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
