Most efficient way to iterate through a QSqlQuery result set?

Question

I'm querying a remote MSSQL Server database using QODBC and I have a result set returned but it appears as though it sends a query to SQL Server for each record one by one. This is really slow for iterating a result set. Running the query from Qt, it takes roughly 15 seconds to complete. I ran the same query in SQL Server Management Studio and it takes 1 second to return results. Here's the code I'm using:

QString connectionTemplate = "DRIVER={SQL SERVER};SERVER=server;DATABASE=db;";
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC3", "db");
db.setDatabaseName(connectionTemplate);
db.setUserName("user");
db.setPassword("password");

if (db.open()) {
    qDebug() << "OK!";
    QSqlQuery * query = new QSqlQuery(db);
    query->exec("SELECT [UserName]"
                "  FROM [dbo].[Users]");


    while(query->next())
    {
        QString userName = query->value(0).toString();
        qDebug() << userName;
    }

    db.close();
}
else {
    qDebug() << db.lastError().text();
}

Is there any way to capture the entire result set into memory and loop through it in memory? I'd rather not have the application take so long to iterate through a result set.

I figured out how to improve the speed of iterating through the results. I had forgotten that I needed to first prepare the sql before executing and setForwardOnly must be set to true. — Cameron Tinker, Jul 04 '12 at 21:19
Any ideas why iterating over 20k records via prepare'd query with setforwardonly might take 4 seconds? This is somewhat related to your question so I decided to ask here :) — Zeks, Oct 21 '12 at 20:28
I'd say that's a pretty good return time for 20k records. It also depends if you're just doing SELECT statements or if you're doing other complex sub queries. By setting setForwardOnly to true, you are drastically improving the the query performance. With the way that QtSql queries databases, it's more efficient to use setForwardOnly to true for SELECT statements. Also, no matter what, you should always prepare your SQL queries as it helps prevent against SQL injection. — Cameron Tinker, Oct 21 '12 at 21:31

score 6 · Accepted Answer · edited Jun 11 '17 at 17:23

6

I figured out how to improve the speed of iterating through the results. I had forgotten that I needed to first prepare the sql before executing and setForwardOnly must be set to true.

QSqlQuery * query = new QSqlQuery(db);
query->setForwardOnly(true);
query->exec("SELECT [UserName]"
            "  FROM [dbo].[Users]");

edited Jun 11 '17 at 17:23

Alexis Wilke

19,179
10
84
156

answered Jul 16 '13 at 12:46

Cameron Tinker

9,634
10
46
85

1

I would recommend writing a REST API to interface with your database from this point on if you can. I'm no longer using direct ODBC access to my database. It's much easier to decouple your data layer from the access layer through an HTTP REST API. This allows the changing of databases without having to change the underlying API too much. – Cameron Tinker Apr 05 '15 at 02:58
1

I'm working on a desktop application with SqLite. I think I got it figured out though. `query.setForwardOnly(true);` – Assimilater Apr 05 '15 at 03:06
1

According to the documentation, the `prepare()` is not required. What is necessary is the `setForwardOnly()`. However, it may vary from database to database, so a prepare may be useful for some of them, somehow. – Alexis Wilke Jun 11 '17 at 17:24

Most efficient way to iterate through a QSqlQuery result set?

1 Answers1