使用Qt / MySQL的批处理模式时的性能

I am using the SQL module of Qt 5.3.1 (Win 7, VS2013) to insert data into a MySQL 5.6 database. After I noticed some performance issues I execute three test code snippets and measured their runtime to get a better understanding of SQL performance. The result is confusing.

我正在使用Qt 5.3.1(Win 7,VS2013)的SQL模块将数据插入到MySQL 5.6数据库中。在我注意到一些性能问题后,我执行了三个测试代码片段并测量了它们的运行时间,以便更好地理解SQL性能。结果令人困惑。

For testing I used a "test" table containg a VARCHAR column "test" and a uniquely incremented id for each row.

为了测试,我使用了一个“测试”表,其中包含VARCHAR列“test”和每行唯一递增的id。

The first snippet looks essentially like this:

第一个片段看起来基本上是这样的:

const QString uploadQueryString("INSERT INTO test (test) VALUES ('%1')");
for (int i=0; i<1000; i++)
{
   QSqlQuery uploadQuery(uploadQueryString.arg("A: test text"), dataBase);
   if (uploadQuery.lastError().isValid())
   {
      qDebug() << tr("Query execution failed (%1)").arg(uploadQuery.lastError().text());
   }
}

The second like this:

第二个是这样的:

const QString uploadQueryString("INSERT INTO test (test) VALUES %1");

QStringList values;
for (int j=0; j<1000; j++)
{
   values.append("\"B: test text\"");
}

QString valuesString = "("+ContainerToString(values, "), (")+")";

QSqlQuery uploadQuery(uploadQueryString.arg(valuesString), dataBase);
if (uploadQuery.lastError().isValid())
{
   qDebug() << tr("Query execution failed (%1)").arg(uploadQuery.lastError().text());
}

The third like this:

第三个是这样的:

const QString uploadQueryString("INSERT INTO test (test) VALUES (:values)");

QVariantList values;
for (int j=0; j<1000; j++)
{
   values.append("C: test text");
}

QSqlQuery batchQuery(dataBase);
if (batchQuery.prepare(uploadQueryString))
{
   batchQuery.bindValue(":values", values);

   if (!batchQuery.execBatch())
   {
      qDebug() << tr("Batch query execution failed (%1)").arg(batchQuery.lastError().text());
   }
}
else
{
   qDebug() << tr("Unable to prepare batch query");
}

I executed each of these snippets (including open/close code) 10 times:

我执行了10次这些片段(包括打开/关闭代码):

1. 10x1000 basic inserts
Ticks delta: 318617 ms; Kernel delta: 358 ms; User delta: 1201 ms; Process delta 1559 ms

2. 10x1000 by value list insert
Ticks delta: 3011 ms; Kernel delta: 0 ms; User delta: 46 ms; Process delta 46 ms

3. 10x1000 by batch insert
Ticks delta: 631679 ms; Kernel delta: 811 ms; User delta: 998 ms; Process delta 1809 ms

"Ticks delta" is the time needed for each snippet. "Kernel delta" and "User delta" is the actually active user and kernel processing time while "Process delta" is the sum of kernel and user time.

“Ticks delta”是每个片段所需的时间。 “内核增量”和“用户增量”是实际活动的用户和内核处理时间,而“进程增量”是内核和用户时间的总和。

The first result is as expect: It needs a lot of time (especially due to latencies) to execute 10x1000 single queries.

第一个结果是预期的:执行10x1000单个查询需要大量时间(特别是由于延迟)。

The second result also is like expected: It is very fast to execute a single query containing all rows at once.

第二个结果也像预期的那样:执行包含所有行的单个查询非常快。

Alas the third result totally confuses me: I expected the batch mode being much faster! Even in the worst case (if the batch execution is simulated by the driver using single queries like the Qt documentation says) it should be equally slow as the first snippet. In fact it needs twice the time!

唉,第三个结果让我很困惑:我期望批处理模式更快!即使在最坏的情况下(如果驱动程序使用Qt文档所说的单个查询模拟批处理执行),它应该与第一个代码段一样慢。实际上它需要两倍的时间!

What does batch execution actually do in Qt/MySQL? Is there a way to improve batch excution performance? How is it possible that execBatch() performes much worse than executing single queries?

批处理执行在Qt / MySQL中实际做了什么?有没有办法提高批量执行性能? execBatch()执行单个查询的可能性有多大?

1 个解决方案

#1

QMYSQL doesn't support BatchExec.

QMYSQL不支持BatchExec。

You can use QSqlDriver::hasFeature(QSqlDriver::BatchOperations) to check if a driver support Batchexec.

您可以使用QSqlDriver :: hasFeature(QSqlDriver :: BatchOperations)来检查驱动程序是否支持Batchexec。

You can also check the source in QT_SRC/src/sql/drivers/mysql/qsql_mysql.cpp

您还可以在QT_SRC / src / sql / drivers / mysql / qsql_mysql.cpp中检查源代码。

bool QMYSQLDriver::hasFeature(DriverFeature f) const

bool QMYSQLDriver :: hasFeature(DriverFeature f)const

it just returned false.

它刚刚返回假。

#1