PDA

View Full Version : Slow ODBC driver or programming error?



TorAn
5th October 2011, 02:45
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;
}
}

Any advice/comment will be appreciated.

wysota
7th October 2011, 22:47
Yeah, skip the while() loop :)

ChrisW67
8th October 2011, 00:34
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
12th October 2011, 20:45
You can also use setForwardOnly() to make some queries faster.

Regards,
Marc

TorAn
11th November 2011, 17:26
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
11th November 2011, 19:17
I am fetching all results from the database.

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

TorAn
12th November 2011, 13:24
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
12th November 2011, 14:03
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
12th November 2011, 14:49
Blame your ODBC driver
That's what I am doing.

Which driver do you recommend? FreeTDS?

wysota
13th November 2011, 23:31
Use the driver most fit for your database. However I would first think whether I really needed this while loop. It seems not necessary.