SQLite Forum

Displaying progress state during DELETE execution
Login

Displaying progress state during DELETE execution

(1) By Heribert (derhexer) on 2020-11-05 07:49:03 [link] [source]

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?

(2) By Stephan Beal (stephan) on 2020-11-05 11:34:30 in reply to 1 [source]

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:

https://www.sqlite.org/c3ref/progress_handler.html

(3) By Gunter Hick (gunter_hick) on 2020-11-05 13:15:30 in reply to 1 [link] [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.

(4) By Keith Medcalf (kmedcalf) on 2020-11-05 17:35:04 in reply to 1 [link] [source]

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

eg:
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.

(5) By Simon Slavin (slavin) on 2020-11-06 01:46:11 in reply to 1 [link] [source]

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.

(6) By Heribert (derhexer) on 2020-11-06 08:40:22 in reply to 5 [link] [source]

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.