QSqlQuery poor performance with pySide6

stef.pellegrino

Hi,
With PySide6, Windows 11, Python 3.10

I'm trying to get data from SqlServer, 3 columns of 2000 rows

// env is connecting through "ODBC Driver 13 for SQL Server" + QODBC
//
sql = QSqlQuery("SELECT * from mytable", db=env)
sql.setForwardOnly(True);
rows = 0

// or sql.last() => same behaviour
while sql.next():
    rows += 1

Almost 45 sec. to get them all !!!

3 sec. with another app in pure JDBC
8 sec with "pure" pyodbc loop

What's wrong ??
Thanks

hskoglund

Hi, you could also try using a readonly db env, don't know Python but something like
env.setConnectOptions("SQL_ATTR_ACCESS_MODE=SQL_MODE_READ_ONLY;")
Note: needs to be called before you open the database.

stef.pellegrino

Ok,
Thanks for the tips, but nothing changes...

stef.pellegrino

Ok, found out the solution here
[https://forum.qt.io/topic/45674/extremely-slow-qsqlquery-select-on-a-small-database/4](link url)

Thanks...

Source code of qsql_odbc.cpp :

if (isForwardOnly()) {
       bool ok = true;
       while (ok && i > at())
           ok = fetchNext();
       return ok;
   } else {
       r = SQLFetchScroll(d->hStmt,
                           SQL_FETCH_ABSOLUTE,
                           actualIdx);
   }

JonB

@stef-pellegrino
I was interested in your problem, but I don't understand what you found in that linked post or the code you pasted which made any difference to your timing? I am well aware that setting forward only makes a big difference, and always recommend it to people here. But you already had sql.setForwardOnly(True); in your code, so I didn't point that out. So what changed?

Oh: You don't perchance mean that you needed to set that before executing the query for the first time? I never use the constructor-with-query, so I would always have set it prior to exec().

stef.pellegrino

@JonB Yes, pretty tricky...
The Qt documentation said that setForwardOnly must be called before preparing the query

JonB

@stef-pellegrino
Ah, thank you, just as well I never use that constructor-with-query then!