QSqlTable-/QSqlQueryModel with QTableView is very slow

Andy314

Hello all,

we are one the way to switch from a MSSQL-Database on a local network server to a Remote-Server over the internet. Pure database loading in a dataset works fast (but only with forwardOnly). Thats ok for us.
But linking database models to a QTableView is a nightmare - so slow especialy for writable data: Initial loading, scrolling.
Is there a trick/setting to speed it up. (forwardOnly not possible her.)
A trick I found is to use MS-Access as bridge. Link all tables and views into access and connect QDatabase to Access. Working is about 3 time faster. Initial loading about 10 times.
Why that ? Nevertheless that cannot be the finally solution and its to slow nevertheless.
What the common way to manage slow database connection with Qt ?

SGaist

Hi,

What kind of database are you using now ?
How slow is your connection ?
How big is your database ?

MrShawn

If you run the same query in SSMS how long does it take comparatively?

How many records are you pulling and if it is a lot do you really need them all in one pull?

I have had very similar experience to what you are describing with the SQL models. Nowadays I create my own model with a DAO, not so bad once you get the hang of it. Can pretty well optimize the query/connection that way without a lot of the excess things being done by the sql models. Additionally if you design it right you can also drop these into some other threads pretty easily to provide a better feel on the UI.

Andy314

@SGaist said in QSqlTable-/QSqlQueryModel with QTableView is very slow:

Hi,

What kind of database are you using now ?
How slow is your connection ?
How big is your database ?

Hi,
the DB-file is about 6 GB size. My Internet connection has 100 Mbit down- and 10 Mbit upload speed with 30 ms ping. I have tested it at an 1 Gbit connection and it has no big effect.
My big test is a Dialog with about 20 tableviews for Lookups. Every Table has ca. 50 entries.
With MS-Access it takes 8 second. A direct MSSQL-Server connection take 3:35 minutes -> 26 times slower.

On the other hand I tested the loading of 1 record via QSqlQuery and QSqlRecord. It take at 200 ms. In a wide range this is independent of the amount of loaded records (maybe 100). So I quess ervery simple database action takes at least 200 ms.
Mayby the models perform a lot of simple action and the sum takes very long.
How does the model work. Is there a difference beween QSqlTableModel and QSQLQuery model in performance. ChatGPt says the querymodel has no cache.
I will test more and separet the problems in smaller parts.

Christian Ehrlicher

@Andy314 said in QSqlTable-/QSqlQueryModel with QTableView is very slow:

How does the model work

It's open source - take a look by yourself.

JonB

@Andy314 said in QSqlTable-/QSqlQueryModel with QTableView is very slow:

Is there a trick/setting to speed it up. (forwardOnly not possible her.)

I don't know about how MS Access comes out much faster. I think that vs Qt QSql... may use a different adapter, and that is significant for MS SQL. But when you do your timings at least try with QSqlQuery::setForwardOnly(true), it should make a huge difference (e.g. https://stackoverflow.com/questions/55814351/qt-qsqldatabase-connected-to-mssql-pulls-rows-extremely-slowly). That may mean you cannot use a QSqlTableModel, I'm not sure. But then MS Access may not be using the equivalent of a QSqlTableModel either.

Andy314

Hello @JonB,

I found out some things:
The problem was NOT the first retrieving of the data. Thats fast enough. All other stuff later costs the most time:

At first QTableView::resizeColumnsToContents() is the absolute performance killer. But other simple things are slow, too.

Moving an other window over of the view or scroll one row.

The reason, as I assumed, are a lot of database actions. The models seems connected directly to the database. For every data(...) call it asks the database or moves the cursor. There seems no difference between QSqlQueryModel and QSqlTableModel.
You can test it in windows with the Taskmanager: Performance/Ethernet: A simle action cost abount 0.2 MBit/s.

So it seem the the QSql...Models are not practicable for slow database connection via internet.

Are there any tricks/parameter to speed up that task or must I build my own cached model ?

PS:
I guess the MS-Access makes some internal caching and because its a local file it speed up so extremly.

SGaist

@Andy314 the Qt models are indeed not optimized for very slow database connections so building your own model is a good plan.

That said, accessing a database directly over internet is usually a pretty bad idea security wise even if the connection is in encrypted.