If I understand correctly, you are running a single statement equivalent to
DELETE FROM <table> WHERE <mostly_true>; Since SQLite can drop a whole table very much faster than deleting ist rows one by one, try: BEGIN; CREATE TABLE temp_data AS SELECT * FROM old_data WHERE <want_to_keep>; DROP TABLE old_data; ALTER TABLE temp_data RENAME TO old_data; CREATE INDEX ...; for all indices of your table COMMIT; Finding the rows you want to keep should be fast enough, since any helpful index is still present. Dropping the whole table plus ist associated indices is a very fast operation (just a few changes to sqlite3_master and the free page list). Recreating the index afterwards will be much faster than seeking out and destroying the majority of rows and index entries. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dinu Gesendet: Sonntag, 17. Dezember 2017 23:02 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Atomic DELETE index optimisation? Ok, so to address the size of the problem properly: We have ~32M records out of which we are deleting about ~24M. The raw size of the DB is about 20G out of which 15G goes away. Under these circumstances any page cache becomes irrelevant. The delete operation, which is a single delete run from the sqlite3 tool, is running for 3 hours now. The WAL size is in excess of 9G atm. The WAL size is growing ever slower (was about 10M/s, now it's 1M every 2s, slowing down). This indicates to me a non-linear process that I can link only to the B-trees, it's the only non-linear component I can think of that could cause this slowing-down-to-a-drag. The CPU is capped up badly, the HDD is at idle level so this also hints to the same issue. In reply to your remarks: - I understand the B-trees need to be modified. However, if you prune, (maybe rebalance), write-to-disk every node at a time, that is hugely stressful and inefficient when you are pruning half of a 32M nodes tree. Since the operation is atomic, the indexes could be updated one time and one time only. So, what to do? I think this delete may never end... by the time it took to write this reply, the WAL grow has succombed to 1M every 4s. -- 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
