ODBC vs RESTful API

31. August 2012 00:55 by Cameron in C++, Qt  //  Tags: , , , , , , , , , ,   //   Comments

In the process of writing the IGA desktop application, I've been faced with several design decisions. One of the most challenging decisions I had to make was how I should most effectively interact with a database backend. To help with this decision, I weighed out the pros and cons of using ODBC and a RESTful web API. Each of these methods are very good for certain purposes.

ODBC

Pros

  • Cross platform support through C/C++ libraries
  • Secure using username and password (connection encrypted)

Cons

  • Some ISPs/Schools block port 1433 (used with SQL Server) or other database ports (MySQL, Postgre, etc)
  • Slow response time in some instances (running multiple queries can take a fair amount of time)

RESTful API

Pros

  • Fast response time
  • Abstracts data backend - i.e. allows for an easy switch of database servers or switch of web server languages
  • Easily allows for multiple desktop and mobile frontends by adhering the web API interface (ODBC isn't usually standard in mobile platforms)

Cons

  • Requires tighter security
  • All requests must be encrypted using SSL or  plain text is sent to the server
  • Requires some sort of authentication either by API key or other method to prevent arbitrary access to server

Ultimately, I decided to go with using a RESTful web API for maintaining separation of the database architecture from the IGA desktop application. This will allow me to change the database backend without breaking the application as long as I keep the API interface the same. Another huge factor in choosing a RESTful web API is that my school blocks port 1433 on its campus wireless networks. I want college students to be able to use the IGA desktop application while on campus so this was a necessary choice. Overall, both provide advantages and disadvantages and neither one is "better" than the other. I hope this helps people with the decision between ODBC and a RESTful API.

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