Very slow sqlite performance?

[while_e]

System Info:
OS: Debian
Kernel: 4.4
Platform: BeagleBoneBlack
Sqlite3: version (SQLite version 3.8.7.1 2014-10-29 13:59:56).

DB Info:
Schema: CREATE TABLE Table1 (rowId INTEGER PRIMARY KEY AUTOINCREMENT, parm varchar(32), value varchar(32));
Total Rows: 164

If I drop into terminal, manually open DB, and issue a simple update command like (UPDATE Table1 SET value='45' WHERE parm='Parm3') it is very fast. It will occasionally lag for a second or so. I set pragma synchronous = OFF, and same with journal_mode, just to test. This makes it even faster, and I never see the occasional lag.

So I use Qt to open the DB, set those same pragma settings, and use something like this:

QSqlQuery query;
QString strQuery = QString("UPDATE %1 SET value='%2' WHERE parm='%3'").arg(table).arg(value).arg(setting);
 
QTime *time = new QTime();
query.prepare(strQuery);
time->start();
 
if(!query.exec()) {
    qDebug() << "Error with query:: " << strQuery << "|" << query.lastError();
    return false;
}
 
qDebug() << "Duration::" << time->elapsed();

QSqlQuery query;
QString strQuery = QString("UPDATE %1 SET value='%2' WHERE parm='%3'").arg(table).arg(value).arg(setting);

QTime *time = new QTime();
query.prepare(strQuery);
time->start();

if(!query.exec()) {
    qDebug() << "Error with query:: " << strQuery << "|" << query.lastError();
    return false;
}

qDebug() << "Duration::" << time->elapsed();

To copy to clipboard, switch view to plain text mode 

This results in an output of an elapsed time between 2000-3000 with the same simple update command? Not every time, but like 1/5 updates it will hangup for a few seconds. Again, I use the same pragma settings and update command via terminal and it runs like butter, but via Qt it seems to have a ton of overhead on ~1/5 updates. Has anyone run into this issue, or have any insight as to why this might be the case?

[d_stranz]

I've used SQLite (via the QSqlite driver) to create databases that are 300+ GB in size. When I use SSD (solid state disk) drives as the source and output drives, it takes a few hours to read and process 450 GB of input ASCII data and create the 360 GB database. The DB creation uses "INSERT" rather than "UPDATE", I prepare the query once with placeholders for values, then I bind the values just prior to each insert. The DB has one table and 11 indexes plus the auto index for the table's primary key. The final DB has around 69 million rows in the main table.

Key-based searching is almost instantaneous after the database has been created. All of this is done using the QSqlite driver and QSqlDatabase / QSqlQuery.

So, basically SQLite isn't the bottleneck.

I am doing it on Windows, not on a BeagleBone platform. I am wondering if due to the limited memory on the BeagleBone (at least I assume so) that the QSqlite driver is caching data and is being forced to swap the cache in and out of RAM that doesn't happen when you use the command line and SQLite directly.

You might be able to verify this by rewriting your program using the SQLite C library API directly instead of going through the Qt driver. All of the same functionality is available at the API level, it is just a bit more tedious to use.

[Lesiok]

I've used SQLite (via the QSqlite driver) to create databases that are 300+ GB in size. When I use SSD (solid state disk) drives as the source and output drives, it takes a few hours to read and process 450 GB of input ASCII data and create the 360 GB database. The DB creation uses "INSERT" rather than "UPDATE", I prepare the query once with placeholders for values, then I bind the values just prior to each insert. The DB has one table and 11 indexes plus the auto index for the table's primary key. The final DB has around 69 million rows in the main table.

Key-based searching is almost instantaneous after the database has been created. All of this is done using the QSqlite driver and QSqlDatabase / QSqlQuery.

So, basically SQLite isn't the bottleneck.

D_stranz, try to load data in packs, for example 1000 INSERTs in transaction. Something like this :

db.transaction();
1000 x INSERT;
db.commit();

db.transaction();
1000 x INSERT;
db.commit();

To copy to clipboard, switch view to plain text mode 

You can be very positively surprised

[while_e]

d_stranz, I totally didn't think of this as a possibility, but it does make sense. Can anyone suggest a method of testing if the driver is caching data on the BBB? I'd prefer not to rework a bunch of code before proving that's the cause. I'm going to look into it, but just hoping someone here may at least have some breadcrumbs to get me going in the right direction.

[d_stranz]

D_stranz, try to load data in packs, for example 1000 INSERTs in transaction.

Oh, way ahead of you here. I'm using 10000 entry packs. I -was- very happy to see the performance with that change.

[while_e]

So, I went ahead and built a small test that performed 100x update queries. I first did it using the QSQLITE driver, it was routinely about 20-40ms per, with the occasional ~1000ms, and even one ~9000ms. It seems to happen the first few queries, and then it levels out.. I then changed it to use sqlite3 libraries directly, and it was routinely 2-8ms per, except 1/10 would jump to ~15ms, and 1/50 would jump to ~100ms.


The thing that's bugging me though, I've been using the QSQLITE driver for quite a while now with the BBB platform, and never had an issue. I updated to kernel 4.4, and a newer Debian version, which likely brought about newer sqlite3 libraries, and now I have this issue. Rolling back isn't really an option at this point, so I'd like to track down where QSQLITE could be failing me. Guess my best bet is to dig into it and see what I can come up with, but was hoping someone here would give me the eureka moment I so desired.

[d_stranz]

What else is happening on your BBB system when you are running these tests? Are there background processes with higher priority which could cause suspension of your DB process? Are you writing your DB to disk? Could caching in memory followed by committing to disk cause a delay? SQLite has some tuning parameters that can affect these things; the default implementation of the QSQLITE driver may not be setting them appropriately for your application. You can modify these values through SQL PRAGMA statements.

[while_e]

I have tried a plethora of PRAGMA settings (automatic_index, synchonous, journal_mode, page_size, cache_size, and more). They do affect timing, just not in a meaningful way. When I'm running the test, there's no other applications running that would cause issues. Running a 'top' shows nothing using significant resources at all. I've moved to using sqlite3.h directly, and given up on QSQLITE because it's just not worth the time and energy at the moment. When I have this project finished up, I'll have more time to look into what about QSQLITE could be causing the issue.

The thing that bugs me though is that I've used this same code in the past with Qt 5.3, kernel 3.8, and an older flavor of Debian, without any issues at all. So something about newer Qt, kernel, or OS is the first breadcrumb. Just don't have to time to pull at that thread just yet. Was hoping someone else has seen these issues, but apparently not.

Thanks for suggestions. Will report back if when I can track it down to hopefully assist anyone else that runs into the issue.