Displaying progress state during DELETE execution
My Application upgrades archived LogBook dBase-files into a sqlite database. So that the resulting sqlite database will be sometimes >300MB... After this import i have to delete some of the archived data: e.g. DELETE FROM LogBook_LID WHERE DATETIME(TimeStampUtc) < DATETIME('now', '-5 years') This may take a long time (5 min or more - as transaction). Is there any way to be called back, so that i can display a "percentage done" information during this running DELETE statement?
Is there any way to be called back, so that i can display a "percentage done" information during this running DELETE statement?
The only way to do a percentage, AFAIK, is to first collect and count the records, then delete them one at a time, but you can get a notification that "work is being done," with no indication of when it will end, using:
(3) By Gunter Hick (gunter_hick) on 2020-11-05 13:15:30 in reply to 1 [source]
SQLite does not know how many rows will be affected by any given statement. You can use the progress callback to estimate the number of machine instructions per record to delete, but will have to estimate (or count) the rows-to-be-deleted beforehand.
Alternatively you can compile with SQLITE_ENABLE_UPDATE_DELETE_LIMIT and do the deletion in batches. https://sqlite.org/lang_delete.html#optional_limit_and_order_by_clauses
DELETE FROM LogBook_LID WHERE DATETIME(TimeStampUtc) < DATETIME('now', '-5 years') LIMIT 1000;
So that only 1000 rows are deleted per execution and execute over and over again until to more rows are deleted.
e.g. DELETE FROM LogBook_LID WHERE DATETIME(TimeStampUtc) < DATETIME('now', '-5 years')
I'm curious what's taking the time. Is it the search to find out which rows to delete ? Or the actual deletion because many rows are found ?
First, do you have an index on the
TimeStampUtc column ?
Second, have you tried rephrasing the
WHERE clause to allow SQLite to search that column directly ? It should be something like
DELETE FROM LogBook_LID WHERE TimeStampUtc < strftime('%s',DATETIME('now', '-5 years'))
This would allow the search (thought not necessarily the deletion) to be done far faster. So much faster that you might not need a progress display.
The LogBook_LID is used as an "ID-Generator" for language depended LogBook tables (e.g. LogBook_EN, LogBook_DE). Currently an "after delete" trigger of LogBook_LID deletes rows with the same ID in the language depended LogBook_[CountryCode] tables... Hmmm.. You gave me an idea: If i deactivate (delete) this trigger during this delete transaction and call: DELETE FROM LogBook_[CountryCode] WHERE TimeStampUtc < strftime('%s',DATETIME('now', '-5 years')) for each language depended table. This will speed up the whole process.