Why is my sqlite query so slow in Qt5?

Question

In Qt5.4 using QSqlDatabase with sqlite3 on Ubuntu14.04 64bit:

First I open and call transaction() on the db.

Next I make 54 individual insert queries, each prepared, each deleted after execution.

Finally I call commit().

All calls complete without error and still the execution time is horrible (around 500 ms total for 54 trivial inserts).

My computer is reasonably modern and has striped SSD disks for performance. When accessing the sqlite file using Sqliteman it is blazingly fast.

So what is going on?

Here is the insert:

void BottleRigStorage::upsertTag(Tag &tag){
    //ScopedTimer st("query time for tag");
    if(open()){

            QSqlQuery query(db);
            query.prepare("INSERT OR REPLACE INTO tags ("
                          "  id"
                          ", batchID"
                          ", retries"
                          ", good"
                          ", status"
                          ", color"
                          ", firstCheckTimestamp"
                          ", createdTimestamp"
                          ", modifiedTimestamp"
                          ", fulfilledTimestamp"
                          ") VALUES ("
                          "  :id"
                          ", :batchID"
                          ", :retries"
                          ", :good"
                          ", :status"
                          ", :color"
                          ", :firstCheckTimestamp"
                          ", :createdTimestamp"
                          ", :modifiedTimestamp"
                          ", :fulfilledTimestamp"
                          ");");
            query.bindValue(":id", tag.id);//8 chars
            query.bindValue(":batchID", tag.batchID);//8 chars
            query.bindValue(":retries", tag.retries);//int
            query.bindValue(":good",tag.good?1:0);//bool
            query.bindValue(":status", tag.status);//6 chars
            query.bindValue(":color", tag.color);//7 chars
            query.bindValue(":firstCheckTimestamp", tag.firstCheckTimestamp); //long
            query.bindValue(":createdTimestamp", tag.createdTimestamp);//long
            query.bindValue(":modifiedTimestamp", tag.modifiedTimestamp);//long
            query.bindValue(":fulfilledTimestamp", tag.fulfilledTimestamp);//long

            if (query.exec()) {
                //qDebug() << "Successfully updated tag database after "<<st.getIntervalCompleteString();
            }
            else {
                qWarning() << "ERROR: could not upsert tag with id " << tag.id<< ". Reason: "<< query.lastError();
            }
            query.finish();
        }

    else {
        qWarning() << "ERROR: DB not open for upsert tag sqlite3";
    }
}

UPDATE: And here is open() as requested:

bool BottleRigStorage::open(){
    if(!db.isOpen()){
        if(!db.open()){
            qWarning() << "ERROR: could not open database. Reason: "<<db.lastError();
        }
    }
    return db.isOpen();
}

Probably, but it's unlikely to be SQLite, and more likely to be something about the way you're using it. Profiling your application should reveal where that slow down is (or at least give a good hint). — cmannett85, Jul 03 '15 at 05:20
The commented out ScopedTimer st("query time for tag"); part in the code measured the time to be spent in this function but I guess with a profiler I can see sub-calls too. — Mr. Developerdude, Jul 03 '15 at 05:21
Was it faster with Qt 5.3 ? or with 4.8 ? If there is a bug in Qt 5.4 sqlite driver lot's of people would be happy to know — Marco, Jul 03 '15 at 07:00
No I explicitly separated my db into two different files to disallow concurrent access to this data — Mr. Developerdude, Jul 03 '15 at 07:01
What function `open` does? BTW, you don't need `query.finish();` — Miki, Jul 03 '15 at 07:10
What is the slowest call? The `query.exec()`, or the transaction commit? — CL., Jul 03 '15 at 13:38

score 5 · Accepted Answer · edited May 23 '17 at 11:45

Use prepare only once. Your code is preparing query each time after QSqlQuery creation. You need to create QSqlQuery with preparing outside of function, and just use value binding and sql query exec in function:

void BottleRigStorage::upsertTag(Tag &tag){
//ScopedTimer st("query time for tag");
if(open()){
        query.bindValue(":id", tag.id);//8 chars
        query.bindValue(":batchID", tag.batchID);//8 chars
        query.bindValue(":retries", tag.retries);//int
        query.bindValue(":good",tag.good?1:0);//bool
        query.bindValue(":status", tag.status);//6 chars
        query.bindValue(":color", tag.color);//7 chars
        query.bindValue(":firstCheckTimestamp", tag.firstCheckTimestamp); //long
        query.bindValue(":createdTimestamp", tag.createdTimestamp);//long
        query.bindValue(":modifiedTimestamp", tag.modifiedTimestamp);//long
        query.bindValue(":fulfilledTimestamp", tag.fulfilledTimestamp);//long

        if (query.exec()) {
            //qDebug() << "Successfully updated tag database after "<<st.getIntervalCompleteString();
        }
        else {
            qWarning() << "ERROR: could not upsert tag with id " << tag.id<< ". Reason: "<< query.lastError();
        }
        query.finish();
    }

else {
    qWarning() << "ERROR: DB not open for upsert tag sqlite3";
}

}

Query object in this case can be a private member and create, for example, after database initialization.

You can tuning sqlite database via pragmas. For example, next code will increase executing of queries:

m_pDatabase->exec("PRAGMA synchronous = OFF"); m_pDatabase->exec("PRAGMA journal_mode = MEMORY");

More information about this you can reade here

IMPORTANT: I found this to be illuminating http://beets.io/blog/sqlite-nightmare.html. Basically there is a bug in sqlite that makes it sleep for seconds instead of milliseconds in some cases. Worth the read. — Mr. Developerdude, Dec 07 '16 at 10:25

score 2 · Answer 2 · answered May 30 '18 at 11:26

I was Facing the same issue when I had like 99 Programs and each and everyone of that had 99 Steps and I was reading that data from Pendrive from CSV file and inserting them into DB. it was taking more than 5 min but after that, I have made few changes in

main.cpp

   db.open();
   db.exec("PRAGMA synchronous = OFF");
   db.exec("PRAGMA journal_mode = MEMORY");

and added db commit on the class for insert query

model.cpp

qDebug()<<"can start a transaction PrgQuery:"<<QSqlDatabase::database().transaction();
query.prepare("insert query");
query.exec();
qDebug()<<"end transaction Step Query:"<<QSqlDatabase::database().commit();

This solved my problem and minimize the time to like 10 sec. Pretty Fast like Unlimited Power

Why is my sqlite query so slow in Qt5?

2 Answers2