I have a sqlite database that is, (I think), fairly small.
I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.
I then open a transaction and insert about 100000 rows, the time it takes is about 10 seconds and I think this is acceptable for that number of rows, (and given the fact that my app does some 'other' processing.
My concern is when I do the 'Commit()' itself.
It takes around 50 seconds to write the data to disk... (around 10Mb is actually written).
This is what I do:
-- pseudo code for brevity... Data Source=database.db;Version=3;Pooling=True;Max Pool Size=100; PRAGMA journal_mode=WAL; PRAGMA wal_autocheckpoint = 16; PRAGMA journal_size_limit = 1536; PRAGMA cache_size = 10000; PRAGMA temp_store = MEMORY; -- BEGIN TRANS -- 100K Inserts/ 10K Deletes -- COMMIT
Can anybody think why the commit would take so much longer than the entire transaction?
Any suggestions on how I could speed the commit up?