Cameron Tinker Cameron Tinker - 1 month ago 35
C++ Question

Most efficient way to iterate through a QSqlQuery result set?

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.

Answer

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.