On 27 Mar 2019, at 11:48am, Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
> Imho quite simple: There are operations that take a long time. I observe this > behavior especially with DELETE in combination with ON CASCADE DELETE. Can > take half an hour, and meanwhile the database remains locked. A timing that long suggests that there is no index which allows fast identification of the keyed rows. Or that the index is damaged. Have you checked ? I wonder whether SQLite is treating each DELETE as a single transaction. Could you try wrapping the main delete in BEGIN ... END and see whether that speeds up the cascaded DELETE ? Would you be able to find timings (either in your code or in the command-line tool) and tell us whether it's the DELETE or the END which takes the time ? Locking by rows is a slow operation. You first have to lock the entire database, then lock the row, then release the database. And each of those locks is a test-and-lock operation. Also, because of the way indexes work, locking a row often locks that row's neighbours too in each index. Code which just locks the entire database is simpler and faster. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
