FetchMore with ODBC driver extremely slow

Zerby

Populating a QSqlQueryModel with a large database table consisting over 100k rows is extremely slow. If I use:

@
while (this->canFetchMore())
{
this->fetchMore();
}
@

A database table consisting over 100k rows is pretty much never loaded fully, because the it get's slower and slower to get the next 256 rows. The first 20k or so is fetched reasonably fast, but after that it get slower and slower and slower, eventually slowing so much it pretty much halts.

Does anyone have any experience on this? I'm using the ODBC driver that I built from Qt sources. I found a post with Google which says:

"So the problem comes from the QODBCResult behaviour. I downloaded Qt's Sources, and edit odbc driver. For those interrested in it, you need to change the behaviour of QODBCResult::data(int) and at least QODBCResult::fetch(int) (and maybe QODBCResult::fetchprevious/next/last/first if you really want something clean) functions.

I personnally add a QList buffer, where I store my rows when they are requested. It's almost the same behaviour as Qt (since it don't cache everything but only the requested rows). My app take up to 38-40 Mbs in memory for 22k rows / 55 columns of text and floats in cache."

I looked at the sources, but I must say I can't really see how to optimize it, not enough skills as of yet... :) Can someone help me, maybe someone has come across the same issue?

John Doe1

I have the same problem. I've Query result for 100k, and process this data per 3-4 min, that very slow. In QT you can't set FETCH SIZE for QODBC driver in standart function to set other options, as setConnectOptions(const QString &options = QString()). For QOCI driver you can change FETCH SIZE, OCI_ATTR_PREFETCH_ROWS, but for QODBC you can't. I'm not find solution of this problem. Some peoples tell, that they use other libs and drivers for work with DB, not QODBC driver. For example IDBB dirver work very fast.

Please can you post your changes. Are you change qsql_odbc.cpp file, right?

hskoglund

Hi, don't know if it helps you, but have you tried setting your queries to forward only, e.g.:
QSQLQuery q;
q.setForwardOnly(true);
(made my queries about 50% faster).

JonB

@hskoglund
This is a good tip. It also saves on memory. It may be that the speed/space saving is even greater with the large number of rows in question here.

A separate issue is why/whether the OP needs to read all 100k rows into memory --- surely not to display them?

John Doe1

@hskoglund, Hi, thanks for answer. Of course, every query i set query.setForwardOnly(true);
before call query.exec(); in qsql_odbc.cpp i see "static const int COLNAMESIZE = 256;"
i think that COLNAMESIZE look like OCI_ATTR_PREFETCH_ROWS for QOCI. I try to change COLNAMESIZE in qsql_odbc.cpp from 256 to 1024, and rebuild dll from source code. I rebuild dll, copy this new dll qsqlodbc.dll in my program directory, but now i can't connect to DB, with default version program connect to DB normally. I use mingw32-bit for make dll.

JonB

@John-Doe1 said in FetchMore with ODBC driver extremely slow:

Of course, every query i set query.setForwardOnly(true);

You would be surprised at how many people do not.

i think that COLNAMESIZE look like OCI_ATTR_PREFETCH_ROWS for QOCI

I think it is for the maximum number of characters allowed in a column name!

John Doe1

@JonB Thanks. I think that i need to use other driver, not QODBC, or explore qsql_odbc.cpp more and fix this.