Improving speed of iterating a result set from a QSqlQuery

4. July 2012 16:22 by Cameron in C++, Qt  //  Tags: , , , , , , , ,   //   Comments

Today, I had to figure out why it was taking so long to iterate a result set using a QSqlQuery. A query that I ran in about 1 second from SQL Server Management Studio was taking 15 seconds to complete from my C++ application. I found that if you don't set ForwardOnly to true using setForwardOnly(true), it will drastically decrease performance of iterating through the query's results. You should also prepare the query before executing. After applying those changes, I got my query to run in under a second! Huge difference! You can see an example of how to connect to a SQL Server database and query a table using the QODBC3 driver. The speed improvements should also apply to SQLite databases.

QString connectionTemplate = "DRIVER={SQL SERVER};SERVER=server;DATABASE=Users;";
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC3", "db");
db.setDatabaseName(connectionTemplate);
db.setUserName("sa");
db.setPassword("password");
if (db.open()) {
	qDebug() << "OK!";
	QSqlQuery * query = new QSqlQuery(db);
	query->setForwardOnly(true);
	query->prepare("SELECT [UserName]"
				   "  FROM [dbo].[Users]");
	QTime begin = QTime::currentTime();
	if(query->exec())      {
		while(query->next())
		{
			QString userName = query->value(0).toString();
			qDebug() << userName;
		}
		QTime end = QTime::currentTime();
		qDebug() << "finished in " + QVariant(end.second() - now.second()).toString() + " seconds";
	}
	db.close();
}
else {
	qDebug() << db.lastError().text();
}

Month List

Tag cloud