Performance when using batch mode of Qt / MySQL

Question

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.

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

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:

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.

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

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!

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?

Answer 1

QMYSQL doesn't support BatchExec.

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

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

bool QMYSQLDriver::hasFeature(DriverFeature f) const

it just returned false.