"The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you."
It's not the right approach because it's hard to get right, you want to offload that to the DB.
It's actually not that hard to get right. You can put a simple lock statement around a SQLiteConnection instance to achieve the same effect with 100% reliability, but with dramatically lower latency than what hosted SQL offerings can provide.
Also, the only reason we ever want to lock a SQLiteConnection is to obtain a consistent LastInsertRowId. With the latest changes to SQLite, we don't even have to do this anymore as we can return the value as part of a single invocation.
Re:SQLite is not a toy database
by warmwaffles on Thursday March 25, 2021 @11:59PM
(#26588612)
You won't have to worry about the last insert row id anymore. `RETURNING` was added recently. Enjoy the speeeed.
Yes, this is exactly how mORMot (https://github.com/synopse/mORMot) implements the DB server with SQLite.
Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to <10k inserts per second even on the fastest hardware.
The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you.
The whole point of an embedded database is that the application should have exclusive control over it, so you don't have to worry about the kinds of things that SQL Server needs to worry about.
SQLite is not a direct replacement for SQL Server, but with enough effort it can theoretically handle even more traffic in your traditional one-database-per-app setup, because it's not worrying about multiple users, replication, et. al.