Improve UPDATE-per-second performance of SQLite? Improve UPDATE-per-second performance of SQLite? sqlite sqlite

Improve UPDATE-per-second performance of SQLite?


With such small amounts of data, the time for the database operation itself is insignificant; what you're measuring is the transaction overhead (the time needed to force the write to the disk), which depends on the OS, the file system, and the hardware.

If you can live with its restrictions (mostly, no network), you can use asynchronous writes by enabling WAL mode.


You may still be limited by the time it takes to commit a transaction. In your first example each transaction took about 0.10 to complete which is pretty close to the transaction time for inserting 10 records. What kind of results do you get if you batch 100 or 1000 updates in a single transaction?

Also, SQLite expects around 60 transactions per second on an average hard drive, while you're only getting about 10. Could your disk performance be the issue here?

https://sqlite.org/faq.html#q19


Try adding INDEXEs to your database:

CREATE INDEX IDXname ON player (name)