Why is my sqlite query so slow in Qt5?
Use prepare only once. Your code is preparing query eachtime after QSqlQuery creation. You need to createQSqlQuery with preparing outside of function, and just use valuebinding 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
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