6 ms·
One thing missing from this: There are massive performance gains to be had by using transactions. In other RDBMSs transactions are about atomicity/consistency
by mrkeen 3y ago
One thing missing from this:
There are massive performance gains to be had by using transactions. In other RDBMSs transactions are about atomicity/consistency. But in SQLite, transactions are about batching inserts for awesome speed gains. Use them!
- joseferben 3y agoCool! This example from the better-sqlite3 docs makes more sense now: const insert = db.prepare('INSERT INTO cats (name, age) VALUES (@name, @age)'); const insertMany = db.transaction((cats) => { for (const cat of cats) insert.run(cat); }); insertMany([ { name: 'Joey', age: 2 }, { name: 'Sally', age: 4 }, { name: 'Junior', age: 1 }, ]);
- keyle 3y agoThat's a fantastic tip, thanks! I've been on the fence for using sqlite, I love the simplicity but always concerned about write performance.
- bawolff 3y agoI'm curious about how true that is under WAL mode, since a big part of that is implicit batching if i understand correctly.
- rcxdude 3y agostill true, in my experience
- bob1029 3y agoAlong the same lines, SQLite is unlike other RDBMS in that you typically do NOT want to create a new connection per logical unit of work, because that means opening an actual file on disk each time. The more you can re-use a SQLite connection instance the better. SQLite is internally handling the locking for you under most providers[0]. Any sort of external attempts at the same will just make things go slower. The only thing that could really beat the internal SQLite mutex is to put a very high performance MPSC queue in front of your single SQLite connection and take resulting micro batches of logical requests into transactions as noted above, or some more consolidated form of representation. [0]: https://www.sqlite.org/compile.html#threadsafe https://www.sqlite.org/compile.html#threadsafe
- chii 3y ago> SQLite is unlike other RDBMS in that you typically do NOT want to create a new connection per logical unit of work i would've imagined that most RDBMS would have a pool of connections that get reused, rather than creating a new connection every time.
- mrkeen 3y agoIn Java-land it's usually up to the application/libraries to manage this. I see Hikari used a lot.
- porkbrain 3y agoIf you're staring a Rust project then make sure to check that your SQLite dependency supports txs. Without txs, inserts can take a ~15ms. The `rusqlite` crate supports txs, with `sqlite` I didn't find an ergonomic solution.
- globalreset 3y agoInserting batches of items in one db transaction is faster in Posgres as well, in my experience. I expect that to be the case for all rdbms. Each transaction has a cost of "flushing", and every non-transaction query is implicitly a transaction. By doing multiple operations in one explicit transaction, the overhead of a transaction is amortized over more operations.
- cogman10 3y agoIt's the same for mssql. A bunch of inserts in a single transaction is generally fast and unproblematic. Though if you have weird clustered indexes and a lot of concurrency you might run the risk of frequent deadlocks.
- stefanos82 3y agoAnother useful trick you can do is to take advantage of memoization combined with BEGIN TRANSACTION / COMMIT to push at once a massive set of data. What I have done so far was to collect safe data, that is not sensitive info like passwords and such, and save them in an array (in my case I used PHP language), and at a specific number of elements you know it's enough to commit in your database, you do so only once via transaction; in my case was 25K rows and worked flawlessly without users realizing anything at all. I have even done the same thing with 100K rows and didn't even break a sweat!