Sqlite: Efficient Way To Drop Lots Of Rows

SQlite, Android, true story. I have a table, which I use as a cache: CREATE TABLE cache(key TEXT, ts TIMESTAMP, size INTEGER, data BLOB); CREATE UNIQUE INDEX by_key ON cache(key);

Solution 1:

You are working on a database with "big" data - ie, each blob using multiple pages.

At some point near optimal performance you will reach a limit you can't improve.

Checking all your choices, I see different behaviors, not just different algorithms.

[1] This one shouldn't be terrible slow as long as you use a transaction. You need two operations at once, query (to get blob size) and delete.

[2] This is a good approach. As two queries and a delete, all in a single command, so SQLite engine will optimize.

[3] This is a different behaviour from all before. Same as DELETE FROM cache WHERE ts < (SELECT ts FROM cache ORDER BY ts LIMIT 1 OFFSET count). Query is less expensive then previous, but I bet number of rows deleted are far less then previous one! Expensive part of query/delete will be delete! Query optimization is important, but things will always get slower in delete.

[4] This is a very bad approach!!! Copying all your data to a new table - maybe another database - will be VERY expensive. I only get one advantage from this: you may copy data to a new database and avoid VACUUM, as new database was build from base and it's clean.

About VACUUM... Worst then DELETE is VACUUM. Vacuum is not supposed to be used often in a database. I understand this algorithm is supposed to "clean" your database, but cleaning shouldn't be a frequent operation - databases are optimized for select/insert/delete/update - not to keep all data at a minimal size.

My choice would be using a DELETE ... IN (SELECT ...) single operation, according to predefined criteria. VACUUM wouldn't be used, at least not so often. One good choice would be monitor db size - when this size run over a limit, run a assumed expensive cleaning to trim database.

At last, when using multiple commands, never forget to use transactions!

Solution 2:

Apparently, what is slow is not finding the records to be deleted, but the actual deletion itself.

Check if PRAGMA secure_delete is set by default in your Android's SQLite. You should just disable it, just to be sure.

You do not need to run VACUUM; SQLite automatically resuses freed pages. You need VACUUM only if you actually know that the database will not grow again in size in the future.

Solution 3:

You have two options to improve the performance, especially the first one:

1) Using Transaction like this:

DbTransaction trans = conn.BeginTransaction(); // <-------------------try 
{
   Any code to delete the items
}
catch
{
    trans.Rollback(); // <-------------------throw; // <-------------------
}

2) Otherwise, supposing that the items are continuous, then

• a) Get the ID of the first item;

• b) Get the total number of items to be deleted

• c) Using command like this:

  DELETE FROM blobs WHERE ID > fistId LIMIT count;

Good luck.