c++ - 如何优化涉及大量sql查询的Qt代码块？

Question

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

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

任何建议或帮助都会非常棒!谢谢:)

编辑-根据 Yohan Danvin 的建议，我对函数进行了更改并在上面进行了更新。

编辑2-Yohan Danvin 的概念很聪明，我也相信使用准备好的语句作为静态变量可以优化例程。但它并没有像我们预期的那样工作。整个例程没有花费更少的时间，而是花费了更多的时间。奇怪的是，准备好的陈述让事情变得更糟!但是经过大量挖掘，我发现了为什么会这样-

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

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

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

这非常有效!执行速度从每次例程调用 25 毫秒下降到每 3 次例程调用 1 毫秒。这是一个巨大的差距!基本上设置 pragma journal_mode = OFF 告诉 SQLITE 不要创建单独的临时转储文件，并且 PRAGMA synchronous = OFF 在执行所有查询后将更改应用于数据库。可能是，通过使用临时内存数据库。如果我提出的观点有误，请告诉我。

我很高兴例程现在快 145 倍!

Answer 1

您只想准备每个查询/语句一次，而不是更多。否则DBMS每次都要重新计算执行计划，非常耗时。

也许您应该实现一种方法来确保对应用程序中的所有查询/语句都完成此操作，如下所示:

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];
}

有了这个，现在你的例子中的选择看起来像这样:

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

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

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