Slow ODBC driver or programming error?

[TorAn]

I have a query that returns 1000 rows in about 5 secs on a local network.
Exactly the same query takes about 5 mins to complete across the internet(fast one).

I am using odbc driver against MSSQL database.

I suspect that the code I wrote to retrieve records might be the problem.

QAbstractTableModel* DBOperations::executeSelectWithParam (const char* sql, QVariant param)
{
	// db is an instance of QSqlDatabase, correctly initialized.
	try
	{
		QSqlQueryModel* retval = new QSqlQueryModel();
		retval->setQuery(sql, db);
		while (retval->canFetchMore())
			retval->fetchMore();
		return retval;
	}
	catch (...)
	{
		return NULL;
	}
}

QAbstractTableModel* DBOperations::executeSelectWithParam (const char* sql, QVariant param)
{
	// db is an instance of QSqlDatabase, correctly initialized.
	try
	{
		QSqlQueryModel* retval = new QSqlQueryModel();
		retval->setQuery(sql, db);
		while (retval->canFetchMore())
			retval->fetchMore();
		return retval;
	}
	catch (...)
	{
		return NULL;
	}
}

To copy to clipboard, switch view to plain text mode 

Any advice/comment will be appreciated.

[wysota]

Yeah, skip the while() loop

[ChrisW67]

If you need a true row count, which is often the reason for the while loop, then it will be more efficient to run a specific query to get the row count and then let Qt's lazy fetching fetch the actual rows only when/if they are needed in a view.

[marcvanriet]

You can also use setForwardOnly() to make some queries faster.

Regards,
Marc

[TorAn]

Yeah, skip the while() loop

Can you explain why? A single query request returns 256 rows in my case. I know that there are 1000+ results, so I am fetching all results from the database.

[wysota]

I am fetching all results from the database.

That's exactly why. You are fetching a lot of data which blocks your app.

[TorAn]

1017 records is a lot of data?

When this call is executed on the machine where db is located it takes about 2 secs.
When it is executed on the LAN it takes about 4 seconds. When I am on WAN (public), it takes literally 5 minutes. The same sql request through SQL Management studio takes 2 secs on LAN and 2-4 secs on WAN.

[wysota]

Blame your ODBC driver. Qt driver is optimized for what it does, if you want to force it to load all the rows even if it doesn't want to, you probably get additional queries which slow down everything. Use a better driver or skip the unnecessary while loop.

[TorAn]

Blame your ODBC driver

That's what I am doing.

Which driver do you recommend? FreeTDS?

[wysota]

Use the driver most fit for your database. However I would first think whether I really needed this while loop. It seems not necessary.