Skip to content

Instantly share code, notes, and snippets.

@NelsonMinar
Last active October 1, 2021 02:14
  • Star 6 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save NelsonMinar/2db6986d5b3cda8ad167 to your computer and use it in GitHub Desktop.
A demonstration of slow sqlite3 bulk inserts in node.js
// Demonstration that bulk insertsin Node.js sqlite3 using prepared statements is very slow.
// Usage: run with one command line argument, one of "db", "reuse", "finalize"
// Details: http://nelsonslog.wordpress.com/2014/11/16/node-js-sqlite3-very-slow-bulk-inserts/
var sqlite3 = require('sqlite3').verbose();
var start = Date.now();
var db = new sqlite3.Database('inserttest.sqlite');
var mode = process.argv[2], runs = "100";
db.serialize(function() {
db.run("begin transaction");
db.run("drop table if exists data");
db.run("create table data (value integer)");
var stmt = db.prepare("insert into data values (?)");
// Three different methods of doing a bulk insert
for (var i = 0; i < runs; i++) {
if (mode == "db") {
db.run("insert into data values (?)", i);
} else if (mode == "reuse") {
stmt.run(i);
} else if (mode == "finalize") {
stmt = db.prepare("insert into data values (?)");
stmt.run(i);
stmt.finalize();
} else {
console.log('Command line args must be one of "db", "reuse", "finalize"');
process.exit(1);
}
}
db.run("commit");
});
db.close(function() {
// sqlite3 has now fully committed the changes
console.log((Date.now() - start) + "ms");
});
@NelsonMinar
Copy link
Author

Revision 2 is no longer slow, I had a bug with the prepared statement vs. the transaction. Details at http://nelsonslog.wordpress.com/2014/11/16/node-js-sqlite3-very-slow-bulk-inserts/

@lukakoczorowski
Copy link

I'd add one more option:
else if ( mode == 'bulk') {

const max = 999; 
const range = Math.min(max, runs-i);
i += Math.min(max-1, runs-i-1);
const values = Array(range).fill().map( (_,i) => i);
const placeholders = values.map(() => "(?)").join(",");
let sql = 'INSERT INTO data VALUES ' + placeholders;
db.run(sql, values);

}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment