PDA

View Full Version : QSqlQuery poor performance retrieving data



croscato
29th October 2014, 16:29
Hi. I'm exporting some data from a Firebird database to a text file.

The code is as follow:



QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE");

db.setHostName("127.0.0.1");
db.setDatabaseName("DATABASE");
db.setPort(3052);
db.setUserName("USER");
db.setPassword("PASS");

if (db.open()) {
QSqlQuery query(db);

query.prepare(SQL);
query.setForwardOnly(true);

if (query.exec()) {
QFile file("C:/Users/NAME/Desktop/Sync.txt");

if (file.open(QIODevice::ReadWrite | QIODevice::Truncate)) {
while (query.next()) {
QString rec;

for (int i = 0; i < query.record().count(); ++i) {
rec = rec + query.value(i).toString().append("|");
}

file.write(rec.append("\n").toStdString().c_str());
}

file.close();
} else {
qDebug() << "Error: " << file.errorString();
}
} else {
qDebug() << "Error: " << query.lastError().text();
}
} else {
qDebug() << "Error: " << db.lastError().databaseText();
}


The table referenced in the SQL has 10k records. And the exported file is around 1MB.

Using QIBase it takes 05:41 approximately.

The same table in a MySQL database with the same data takes around 01:13.

However, this same firebird database accessed through Delphi. Generate this file under 10 seconds.

Could this poor performance be related to the Qt SQL drivers?

Is there any way I could improve the overall performance of this type of code?

Thanks!

wysota
29th October 2014, 16:59
First of all you can optimize your while loop which does a lot of unnecessary memory allocations and string conversions (you can even generate the string in the database engine directly). To test raw query speed you can remove the whole body of the while loop to see how long it takes to iterate the result.

croscato
30th October 2014, 19:50
First of all you can optimize your while loop which does a lot of unnecessary memory allocations and string conversions (you can even generate the string in the database engine directly). To test raw query speed you can remove the whole body of the while loop to see how long it takes to iterate the result.

Hi Wysota, thanks for the fast answer.

I already have tried to minimize memory allocation and string conversions. All with the same result.

Running the application without the body of the loop is extremely fast as it should be.

However I solved the problem with a cost, replacing the QIBase plugin by IBPP (http://www.ibpp.org/) library.

The same code now runs fast enough. QElapsedTimer which I was using to measure the time taken to perform the operation
is reporting the time as 00:00:00.

I hope this could help other persons with the same problem using QIBase.

wysota
31st October 2014, 07:53
I already have tried to minimize memory allocation and string conversions.

What did you do to achieve the goal? If you still have append() or toStdString() calls then you should try harder, these calls shouldn't be there.

croscato
31st October 2014, 11:22
Dear Wysota the solution I used is the code bellow.



IBPP::Driver driver = IBPP::DriverFactory();

IBPP::Database db = driver->DatabaseFactory("127.0.0.1/3052", "DATABASE_PATH", "USER", "PASSWORD");

db->Connect();

IBPP::Transaction transaction = driver->TransactionFactory(db);

transaction->Start();

IBPP::Statement statement = driver->StatementFactory(db, transaction);

statement->Prepare(FBSQL.data());
statement->Execute();

QFile file("C:/Users/NAME/Desktop/sync.txt");

int total = 0;

if (file.open(QIODevice::ReadWrite | QIODevice::Truncate)) {
QElapsedTimer timer;
timer.start();

std::string str_data = "";
int64_t int_data = 0;
double dbl_data = 0.0f;

QByteArray header;

while (statement->Fetch()) {
++total;

if (header.isEmpty()) {
for (int i = 1; i <= statement->Columns(); ++i) {
header.append(statement->ColumnName(i)).append("|");
}

header.chop(1);

file.write(header.append("\n"));
}

QByteArray line;

for (int i = 1; i <= statement->Columns(); ++i) {
switch (statement->ColumnType(i)) {
case IBPP::sdArray:
break;
case IBPP::sdBlob:
break;
case IBPP::sdDate:
break;
case IBPP::sdTime:
break;
case IBPP::sdTimestamp:
break;

case IBPP::sdString:
statement->Get(i, str_data);

line.append(str_data.c_str()).append("|");

break;

case IBPP::sdSmallint:
case IBPP::sdInteger:
case IBPP::sdLargeint:
statement->Get(i, int_data);

line.append(QString::number(int_data)).append("|");

break;

case IBPP::sdFloat:
case IBPP::sdDouble:
statement->Get(i, dbl_data);

line.append(QString::number(dbl_data)).append("|");

break;
}
}

line.chop(1);

file.write(line.append("\n"));
}

file.close();

qDebug() << "Records: " << total;
qDebug() << "Time: " << msecsToTime(timer.elapsed());
} else {
qDebug() << "Error: " << file.errorString();
}

statement->Close();

transaction->CommitRetain();

db->Disconnect();

wysota
31st October 2014, 11:49
So where exactly did you try to minimize memory allocations? :)

Your original while loop can be rewritten as:


QTextStream stream(&file);
while (query.next()) {
QStringList record;
for (int i = 0; i < query.record().count(); ++i) {
record << query.value(i).toString();
}
stream << record.join('|') << endl;
}

Or even:

while(query.next()) {
const int cnt = query.record().count();
for (int i = 0; i < cnt; ++i) {
file.write(query.value(i).toByteArray());
file.write(i == cnt-1 ? '\n' : '|');
}
}

croscato
31st October 2014, 19:18
Hi wysota.

I run some tests with the suggestions you made and get some interesting results.

Every suggest loop were run four times and the measurements are as follow:



Code snippet 1
#1: 05:44
#2: 06:00
#3: 05:41
#4: 05:48

Code snippet 2
#1: 00:38
#2: 00:28
#3: 00:29
#4: 00:38



As you can see. The second code snippet runs reasonably faster.

Seeing this results I got intrigued about what could have caused this large difference in execution time.

Then a tested this modified version of the code snippet 1



QTextStream stream(&file);

const int cnt = query.record().count();

while (query.next()) {
QStringList record;
for (int i = 0; i < cnt; ++i) {
record << query.value(i).toString();
}
stream << record.join('|') << endl;
}

and the results were:



Code snippet 3
#1: 00:35
#2: 00:30
#3: 00:40
#4: 00:45



Close enough from the performance achieved with code snippet 2.

It seems that querying the record count in all iterations of the loop was the real bootleneck and the code snippet 2 is the answer for this problem, although using IBPP I can obtain a little more performance.

croscato
18th November 2014, 18:22
After finishing my project I decide to investigate what could have been causing the slow response in Qt SQL classes.

Looking at QSqlQuery code
https://qt.gitorious.org/qt/qtbase/source/73a1e8c60d894701f34806cc4b847aa2814bf389:src/sql/kernel/qsqlquery.cpp line #905, the method record() seems to be the problem.



QSqlRecord QSqlQuery::record() const
{
QSqlRecord rec = d->sqlResult->record();

if (isValid()) {
for (int i = 0; i < rec.count(); ++i)
rec.setValue(i, value(i));
}
return rec;
}

As you can see the code loops through an internal record structure and populates a QSqlRecord which is returned by the method. Putting a call to record() inside any loop, now doesn't look like a good idea.

I made a project to test calling record() in diferent ways. And exporting data with 300K records I get results varying from 03:00 to 00:30 using three different implementations.

Project: 10752
Database DDL: 10753

I coundn't attach the data file because it's size exceeds the Qt Centre limitation. But this database it's based in MySQL open database sample.
All data and table definition could be downloaded from https://launchpad.net/test-db/

Anyway, before anything I should have read Qt documentation as it says from the record method:



Returns a QSqlRecord containing the field information for the current query. If the query points to a valid row (isValid() returns true), the record is populated with the row's values. An empty record is returned when there is no active query (isActive() returns false).

To retrieve values from a query, value() should be used since its index-based lookup is faster.

In the following example, a SELECT * FROM query is executed. Since the order of the columns is not defined, QSqlRecord::indexOf() is used to obtain the index of a column.


and yet provides an example of how it should be used



QSqlQuery q("select * from employees");
QSqlRecord rec = q.record();

qDebug() << "Number of columns: " << rec.count();

int nameCol = rec.indexOf("name"); // index of the field "name"

while (q.next())
qDebug() << q.value(nameCol).toString(); // output all names

note that the call to record is done only once and outside to while loop.