3 ms·
With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives. Note that WAL an
by bob1029 6y ago
With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives.
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.
- Thaxll 6y ago"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.
- bob1029 6y agoIt'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.
- warmwaffles 6y agoYou won't have to worry about the last insert row id anymore. `RETURNING` was added recently. Enjoy the speeeed.
- edwinyzh 6y agoYes, this is exactly how mORMot (https://github.com/synopse/mORMot https://github.com/synopse/mORMot) implements the DB server with SQLite.
- avinassh 6y ago> I have been able to insert hundreds of thousands of rows per second when using NVMe drives. Thats... amazing! What is your setup like?