Dinu Option 2, dropping and recreating the index with the transaction, seems to be the way forward - I would suggest that if the author of SQlite (Dr Hipp) has put this forward as a solution, as he did earlier in this thread, then it is probably a safe option and will not lead to an implosion of anything.
Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 19 December 2017 at 16:15, Dinu <dinumar...@gmail.com> wrote: > Hi sub sk79, > I have so far from this thread the following suggestions: > 1) Copy table -> TRUNCATE -> copy back; this doesn't work, what was > supposed > to be TRUNCATE semantics (DELETE FROM without WHERE) has the same > performance as with WHERE. > 2) Structure alterations; either drop table, or drop indexes : I am > reluctant to do this; my evangelical instinct tells me hacking the > semantics > of life might lead to implosion of Earth :) > 3) "Deleted" bit field - presumably the "soft delete" as you call it; I am > analyzing this, but here the question is whether we include the bit in the > indexes. If so, performing a heap of UPDATEs should be even more > inefficient; if we don't include it in the index, the problem of the cost > of > filtering the row needs some analysis which I will probably do; the problem > with this solution is that is has residual effects: we run some pretty > complex queries against this table, with complicated joins and we already > got some surprising execution plans that needed query rewriting. So with > this "deleted" bit out of the index pool, we need to check various other > queries to make sure they are still optimized to what we need. > > All this said and done, 3 hours to delete 15G of data seems atrocious even > if you do it by standards resulted from generations of DOD and NSA > inbreeding... so I'm still hopeful for some DB-related solution. > > > > -- > Sent from: http://sqlite.1065341.n5.nabble.com/ > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
