On 17 Dec 2017, at 8:53pm, Dinu Marina <dinumar...@gmail.com> wrote: > It became apparent to me from performance measurements that the DELETE > operation is very slow, when operating on large recordsets with indexes > involved. My deduction is that SQLite updates the indexes for every deleted > row, which in painstakingly slow... > > Since the DELETE should be atomic, the index update could also be atomic, and > a mass pruning of the index tree is hugely faster than individual removals. > > My question is: is my deduction correct? Is there any way to fix/improve this > in userland? Or are there prospects for this to be improved in SQLite in the > foreseeable future?
The data for the table is held in one tree, occupying one set of pages. The data for each index is held in a separate tree, occupying its own set of pages. So if you have N indexes on the table, N+1 sets of pages need to be updated. If a DELETE deletes more than one row, then SQLite does the caching you’d expect when working out which pages to update. SQLite does this efficiently and work went into optimizing for speed. If the timing of DELETE statements is important to you, use journal mode of PERSIST, MEMORY or WAL. You might want to compare PERSIST and WAL and find out which performs better for you. Remember that journal mode is saved with the database: you only need to set it once for the file, and SQLite will retrieve the setting when it reopens the file. If you are doing more than one DELETE at a time, enclose them all in a transaction: BEGIN DELETE … DELETE … COMM|T This can be far faster than individual commands, since the 'dirty' pages are written back once at the end of the transaction rather than the end of each command. On 17 Dec 2017, at 9:06pm, Dinu Marina <dinumar...@gmail.com> wrote: > Update: we are also using WAL; I have noticed during a huge delete, the WAL > also grows huge. Could this also be a reason for slowness, that SQLite > duplicates every block that hosts a delete? Is there any way to work around > this? Correct. While a transaction with DELETE is being carried out, SQLite must temporarily store both the old and the new versions of all pages affected. This is to allow recover if your computer crashes or loses power before the transaction is complete. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
