Node.js sqlite3: very slow bulk inserts – Solved

I had some slowness issues with batch inserts with Node.js and SQLite 3.

This post gave me a hint that the prepared statement had to be created AFTER the transaction. Also, the SQL query to commit the transaction is “END”, not “COMMIT” as in other RDBMS

This one worked well for me, few seconds to add 120k records. I’ve used it in a script to fill a SQLITE db from CSV files in my serverless microservice (soon available on my github repository).

db.serialize(() => {
  db.run("BEGIN TRANSACTION;");
  const insertStatement = db.prepare("INSERT INTO t (a,b) VALUES (?, ?)");
  // many times
  insertStatement.run([a,b]);
  insertStatement.finalize();
  db.run("END;", () => console.log(`done`));
});

Nelson's log

I’m running into a baffling problem where the straightforward way to use prepared statements in the MapBox sqlite3 Node.js code is super slow. Like inserting 100 rows in a table takes 16 seconds!

Update: found the problem, I wasn’t using transactions right. See bottom. Simple solution for faster bulk inserts in sqlite3: do them with a transaction.

The API docs have a strange note for Statement.finalize() which says “if you experience long delays before the next query is executed, explicitly finalizing your statement might be necessary”. I’m seeing exactly that. But after you’ve finalized it you can’t reuse the prepared statement; what’s the point of using statements at all then?

There’s notes online that naive bulk inserts in sqlite3 are slow, nothing to do with Node. See StackOverflow and Blog::Quibb. The quick fix I learned from both of those is to wrap the insert block in a transaction, because…

View original post 245 more words

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s