How to optimize a block of Qt code that involving

Question

I am working on a Qt(C++) project that involves a huge number of sql queries. Basically it's a function update() that gets called ~1000 times. Each and every call takes around 25 - 30ms in my system resulting in a massive 30seconds total execution time. I believe this routine can be optimized resulting in less time consumption, but don't know how to optimize. Here is the function-

void mediaProp::update(){
static QSqlQuery q1, q2, q3;
static bool firstCall = true;
static QString stable;
QString table = this->type+"s";
if(firstCall){
    stable = table;
    q1.prepare("SELECT id FROM titles WHERE lower(title)= lower(:a) AND type = :b COLLATE NOCASE");
    q2.prepare("INSERT INTO " + table + "(pic_id, score) VALUES (0, 0)");
    q3.prepare("INSERT INTO titles (id, type, title) VALUES (:a, :b, :c)");
    firstCall = false;
}
else if(stable != table){
    stable = table;
    q2.prepare("INSERT INTO " + table + "(pic_id, score) VALUES (0, 0)");
}
q1.bindValue(":a", this->title);
q1.bindValue(":b", dbEnums(this->type));
q1.exec();
q1.last();
int size = q1.at() + 1;

if( size > 0){
    q1.first();
    this->id = q1.value("id").toInt();
}
else if( !this->title.trimmed().isEmpty() ){
    q2.exec();
    this->id = q2.lastInsertId().toUInt();
    q3.bindValue(":a", this->id);
    q3.bindValue(":b", dbEnums(this->type));
    q3.bindValue(":c", this->title);
    q3.exec();
}
else{
    this->id = 0;
}

}

Any suggestion or help would be really amazing! Thanks :)

EDIT- As suggested by Yohan Danvin, I made changes to the function and updated it above.

EDIT2- Yohan Danvin's concept was smart and I was also convinced that using prepared statements as static variables would optimize the routine. But it didn't work the way we expected. Instead of taking less time, the overall routine took more time. It was strange that prepared statements made things worse! But then after a lot of digging, I found why it was so-

THE PROCEDURE TOOK 25 MILLISECONDS IN AVERAGE
AFTER USING Yohan's STATIC PREPARED STATEMENT MAPPING PROCEDURE- IT TOOK 27ms IN AVG

For the record, I was using a file as my database not memory. Every time an INSERT query was performed QSqlQuery would create a temporary dump file and append it to the main database file. Accessing files in contrast to memory is awe-fully time consuming and that resulted in a slow 25ms/insertion rate. I guess when I used Yohan's concept, it took a little more time due to function overhead etc. Do let me know if I'm wrong! Finally i came across http://www.sqlite.org/pragma.html and changed a few pragma parameter-

QSqlQuery("PRAGMA journal_mode = OFF");
QSqlQuery("PRAGMA synchronous = OFF");

And that worked like charm! Rate of execution went from 25ms per routine-call down to 1ms per 3 routine calls. That's a huge gap! Basically setting the pragma journal_mode = OFF tells SQLITE not to create a separate temporary dump file and PRAGMA synchronous = OFF applies the changes to the database after all the queries are performed. May be, by using a temporary in-memory database. Please let me know if I made a point that is wrong.

I'm glad that the routine is now 145X faster!

Answer 1

You want to prepare each query/statement only once, not more. Otherwise the execution plan is recomputed every time by the DBMS and it is time consuming.

Maybe you should implement a way to make sure this is done for all your queries/statements in your application, like so:

QSqlQuery& prepareQuery(const QString& query)
{
    static QMap<QString, QSqlQuery> queries;

    if (!queries.contains(query))
    {
        // not found, insert the query in the map and "prepare" it
        queries[query].prepare(query);
    }

    return queries[query];
}

With this available, now the select in your example would look like this:

QSqlQuery& q = prepareQuery("SELECT id FROM titles WHERE lower(title)= lower(:a) AND type = :b COLLATE NOCASE");
q.bindVariable...

Do this for the 2 inserts as well (even the one with a variable table name, an SqlQuery will automatically be created and prepared for each different table name).

[N.B.: please note that, if working in a multi-thread environment, you would have to make sure the same QSqlQuery object is not used simultaneously by 2 different threads]