Sqlite updates are slower (15 seconds for 1720 records) on the SSD

Question

Dear developer, for some reason, updates for 1720 records take about 15 seconds when on an SSD drive (especially when the setting is enabled).

I changed sqlite settings using the following document (which works well) http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

I have the following PRAGMA tuned to optimize performance, and I use transactions around a full set of updates.

sqlite3_exec(database, "PRAGMA cache_size=500000;", nil, nil, nil);
sqlite3_exec(database, "PRAGMA synchronous=OFF", nil, nil, nil);
sqlite3_exec(database, "PRAGMA count_changes=OFF", nil, nil, nil);
sqlite3_exec(database, "PRAGMA temp_store=MEMORY", nil, nil, nil);

It seems that the SSD does too much (for example, removes blocks, etc.), which forces it to block for 15 seconds only to update 1720 simple records.

Oddly enough: inserting 2500 entries is almost instant. Can you help me and give me some pointers on how to fix this?

Answer 1

I found the answer by doing some good old testing and testing. It seems that sqlite will freeze on an SSD with TRIM when many separate UPDATE statements are executed one after another in a transaction.

Now I changed the code: a) prepare the command and reuse this command for all updated records. b) COMMIT and START a new transaction every 1500 records

this seems to have fixed the freeze in sqlite. In short: reuse the prepared sql statement and make sure the transaction is not getting too big.