Node-sqlite3: Slow performance for run

[mblandfo]

My table looks like this:

create table if not exists sentences (
    text varchar (2048),
    prop1 boolean,
    prop2 boolean,
    primary key (text)
);

I run simple insert or update commands like this. They take ~100ms

var t0 = new Date();
db.run(sql, params, function(err) {
    var t1 = new Date();
    log((t1 - t0) + ' run ms');

If I open up DB Browser for SQLite and run the same insert or update commands, they take ~0ms.

I am on Windows 7.

db.get is very fast, around ~1ms

[mceachen]

1. your primary key shouldn't be 2k of text. Use an md5 or sha of your sentence instead, if you can't use an autogen pk.
2. you aren't using db.serialize and a transaction
3. you aren't using a prepared statement

The first and second issue will impact performance by orders of magnitude. 3. will drop performance by half.

db.serialize(() => {
  db.run("begin transaction")
  const pstmt = db.prepare("insert into sentences values (:text, :prop1, :prop2)")
  pstmt.run("hello", true, false) // replace with a `foreach` around your data
  db.run("commit")
})

[kkaefer]

Note that Date returns timestamps in microseconds, not milliseconds. Your observed time is therefore ~1ms, not ~100ms.

[mblandfo]

@kkaefer that is not true, they use milliseconds.

Subtracting Dates will call Date.valueOf: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object/valueOf

Date.valueOf returns milliseconds: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/valueOf

[mceachen]

@kkaefer Date.now() is millis, not micros. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/now

[mblandfo]

I had gone on vacation shortly after posting and had moved to postgresql anyway, but, I just tried to reproduce this issue now and I could not! even without @mceachen 's improvement suggestions. So, I'll close it.

[kkaefer]

It's documented on https://github.com/mapbox/node-sqlite3/wiki/Control-Flow