In my experience, sqlite performance becomes problematic quite quickly, even with settings you mentioned (WAL etc).
In my experience, sqlite performance becomes problematic quite quickly, even with settings you mentioned (WAL etc).
I should amend my original post, because I know a lot of developers fall into the trap of thinking that you should always do the open/close connection pattern with these databases. That is a huge trap with SQLite. If you want to add some extra zeroes to your benchmark figures, only use a single connection for accessing SQLite databases. Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId and in cases where transactional scopes are otherwise required. This alone can take you from 100 inserts/second to 10k without changing anything else.
You mean for generating unique primary keys? Why would last insert row id be slow?
> and in cases where transactional scopes are required
Could you elaborate on what you mean by this?
Transactional scopes meaning scenarios like debiting one account and crediting another. This is something you can also manage with locking with application-level primitives.
I’d assume the DB would be most efficient at handling it’s own. At least to the extend that it wouldn’t garner a 100x speedup to do it in app.
We need to remember that opening a connection to SQLite is like opening a file on disk. Creating/destroying file handles requires far more resources and ceremony than taking out a mutex on a file that is never closed.
Here is my SQLite compilation flags: https://github.com/liuliu/dflat/blob/unstable/external/sqlit...
Here is where the flag used: https://github.com/sqlite/sqlite/blob/d46beb06aab941bf165a9d...