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();
}

Neo4j and SQL Server 2008 R2

Lately, I've been researching graph databases (NOSQL) as an alternative to traditional relational databases. The performance increase from a graph database compared to a relational database is phenomenal. To get my website rolling, I'm planning on using SQL Server 2008 R2 with the Microsoft Entity Framework. Once the data models are in place, I can then work on writing a SQL to graph database server application for migrating my data model into a graph database. There are several .NET libraries available for interfacing with Neo4j's REST API and the data migration should be trivial. My database migration tool will consist of using the Entity Framework to connect to SQL Server 2008 R2 and one of the .NET frameworks for manipulating Neo4j's REST API. I'll continue to post updates as they become available.

Month List

Tag cloud