SQLite UPDATE 100ms

Question

I'm using the Qt database abstraction layer to interface with Sqlite3.

int x = GetTickCount();
database.exec("UPDATE controls SET dtype=32 WHERE id=2");
qDebug() << GetTickCount()-x;

The table is:

CREATE TABLE controls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    internal_id TEXT,
    name TEXT COLLATE NOCASE,
    config TEXT,
    dtype INTEGER,
    dconfig TEXT,
    val TEXT,
    device_id INTEGER REFERENCES devices(id) ON DELETE CASCADE
);

Results in an update time of ~100 ms! Even though nothing else is accessing the db and there are a grand total of 3 records in that table.

That seems ridiculously long to me. 10 records would already take a second to complete. Is this the performance I should expect from sqlite or is something messing me up somewhere? SELECT queries are fast enough ~1ms.

Edit 1

So it's not Qt.

sqlite3 *db;
if ( sqlite3_open("example.db",&db ) != SQLITE_OK )
{
    qDebug() << "Could not open";
    return;
}

int x = GetTickCount();
sqlite3_exec(db, "UPDATE controls SET dtype=3 WHERE id=2",0,0,0);
qDebug() << "Took" << GetTickCount() - x;

sqlite3_close(db);

This guy takes just the same amount of time.

Are you using transactions? try to use `database.transaction(), database.exec('update here'), database.commit()`. Sometimes if you have any uncommitted update statements hanging this will slow down inserts/updates — rs., Oct 30 '12 at 16:30

score 5 · Accepted Answer · edited May 23 '17 at 12:15

5

When you access your hard disk it can take long.

Try one of these:

PRAGMA journal_mode = memory;
PRAGMA synchronous = off;

So it will not touch the disk immediately.

SQLite can be very very fast when it is used well. The select statement for your small database is answered from cache.

There are other ways to tweak your database. See other questions like this: Improve INSERT-per-second performance of SQLite?

edited May 23 '17 at 12:15

Community

1
1

answered Oct 30 '12 at 16:36

Vereb

14,388
2
28
30

Fantastic. Just adding synchronous = off drops the time to not measurable by GetTickCount!! – Ron Oct 30 '12 at 16:44
`synchronous = off` means chances are high that you will trash your database in case of a power-loss during an UPDATE/INSERT. – Paul Groke Oct 30 '12 at 17:11
Looks like it but the performance is really needed. The off chance of a catastrophic failure is small. What I'll do is to store a copy of the DB before init. If init succeeds this will be the fallback for next start-up. Since this is not a banking application and data can be recreated from the environment (albeit slow) this should be OK. – Ron Oct 30 '12 at 17:21

SQLite UPDATE 100ms

1 Answers1