如何优化涉及大量SQL查询的Qt代码块? [英] How to optimize a block of Qt code that involving huge number of sql queries?

问题描述

我正在从事一个涉及大量SQL查询的Qt(C ++)项目.基本上，它是一个函数update()，被调用〜1000 次.在我的系统中，每次调用大约需要25到30毫秒，这导致30秒的总执行时间.我相信可以优化此例程，从而减少时间消耗，但不知道如何优化.这是功能-

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 :)

编辑- 根据 Yohan Danvin 的建议，我对该功能进行了更改并在上面进行了更新.

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

EDIT2 - Yohan Danvin的概念很聪明，我也深信使用准备好的语句作为静态变量可以优化例程.但这并没有达到我们预期的效果.而不是花费更少的时间，整个例程花费了更多的时间.准备好的声明会使情况变得更糟，这很奇怪！但是经过大量的挖掘，我发现了为什么会这样-

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

出于记录目的，我使用的是文件而不是内存作为数据库.每次执行INSERT查询时，QSqlQuery都会创建一个临时转储文件并将其附加到主数据库文件中.与内存相比，访问文件非常耗时，并且导致25ms/插入速率的降低.我想当我使用Yohan的概念时，由于函数开销等原因花费了更多时间.如果我错了，请告诉我！最后，我遇到了 http://www.sqlite.org/pragma.html 并做了一些修改编译参数-

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");

那就像魅力一样！执行速度从每个例行调用25ms降低到每个3个例行调用1ms.那是一个巨大的差距！基本上设置pragma journal_mode = OFF告诉SQLITE不要创建单独的临时转储文件，并且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.

我很高兴该例程现在 145X 更快！

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

推荐答案

您只想prepare每个查询/语句一次，而不要更多.否则，DBMS每次都会重新计算执行计划，这很耗时.

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

同样对2个插入执行此操作(即使是具有可变表名的插入，也会自动为每个不同的表名创建并准备一个SqlQuery).

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

[注意:请注意，如果在多线程环境中工作，则必须确保2个不同的线程不会同时使用同一个QSqlQuery对象]

[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]

这篇关于如何优化涉及大量SQL查询的Qt代码块?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！