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();
}

Answer 1

1. 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.

2. 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