TableView GUI slow for slow database

Andy314

I use Qt 5.4.1 MinGW on Windows 8.1 and make a ODBC connection via DRIVER={ODBC Driver 11 for SQL Server} over the internet. The connection is slow, but fast enough to retrieve a table in 0.5 sec. Thats ok for me.
The problem is that in this case the tableview is very slow (clicking in field, resizing window or column). I costs 1-2 seconds to resize a column. When I connect to a local database all works fast.
I cannot understand why. Whats the difference?
If I understand the model/view concept correct, the QSqlTableModel retieves the data and the tableview uses this data to show it on the screen. When I make a fetch to all data, the tableview should show this data in an "offline" way and no DB connection is needed for this directly. So the GUI speed for a slow and a fast DB-connection should be the same.
But it isnt ! When I disconnect the network cable from the computer I cannot resize the window !!! The programm sucks and continues when I plug-in the cable again.
Why needs the TableView a permanet DB connection ? What does it do permanetly ?

SGaist

Hi,

How many rows does your table contain ? IIRC QSqlTableView doesn't load the complete table but fetches the data as needed so it might explain part of the slowness

Andy314

Hi @SGaist,
thank you for your answer. My table has 320 rows and 24 columns.
I have made some experiement meanwhile. In the windows Resourcemonitor-Network I can see how much data the programm transfers from the database.
My results:

A column resizing refreshes the data from the database. I conclude that the code has a very simple (stupid) logic: Do something with the tableview -> get all data needless again.
Can this be?
I had expected that the Model has some internal logic to cache the already loaded records. But scrolling forward and backward fetches always new data.
Is this correct that all invisible records out of screen will be lost ?
Window / TableView resizing fetches only again all data. See 1.

Has the SqlModel/TableView really such a simple logic ?

alex_malyu

view requests data every time it needs to display row/column with specific index.
If model does not own the data and gets it remotely it has 2 choices:

create duplicate of the data in the memory
or request it from the database.

Both choices have pluses and minuses.
And I believe 2nd way is used in QSqlTableModel .

I disagree that this is stupid.
Also you can always subclass model and make it actually work with a local copy if you prefer.

Andy314

Hi @alex_malyu
From the standpointconcept of a remote datasource the mechanism is logic. But from the pracical use in is very uncomfortable.

I come from the C++Builder and have used the very powerfull DeveloperExpress Grid. So I expected Qt has ready to use components, too. (I was very surprised that the editor of a boolean value was a spin-widget by default.)

Nevertheless, Qt has a very flexible way to adapt it to custom tasks, thats is good. One can and must do more to reach the flexibility. That is sometimes hard for a Qt-beginner.

Regarding to the problem:

I need a cached SqlModel, by subclassing from the SqlTableModel?
Overwriting of the data, and setData and mybe other recordmanipulation functions.

in very raw pseudocode:

QVariant Data(index)
{
if(!Chached(index))
LoadInCache(index)
return Cache(index)
}
SetData(index, value)
{
WriteInCache(index, value)
WriteInModel(index,value)
}

Ok, I have an other question.
I quess the SqlTableModel does not querys a whole record but makes a query for every cell separately ?
Is it faster to retrieve the whole record at once with the record(int row) function?

QVariant Data(index)
{
if(!RowIsCached(index))
LoadRowInCache(index)
return Cache(index)
}

Chris Kawa

That caching strategy only works if your app is the only one accessing the database.

There's no way for Qt to know if your database is modified frequently from outside or not. That said it just plays it safe by default and fetches the data again whenever it updates the view.

As you pointed out though, it allows you to tweak that behavior to fit your particular needs (e.g. by implementing a cache, for offline access or any other type). Just remember that such cache can become stale if the database is concurrently accessed by other clients, so you might want to think of some way of checking that and discard the cache on some conditions.

As to whether it's faster to fetch a row depends on the data itself and your access pattern. If you have a 1000 columns in a row, but the view covers just say 3 columns at a time it might be more efficient to fetch just part of the row or whole columns instead of whole rows. On the other hand if the table is "narrow" and the data is "light enough" fetching a whole row (or even a few rows at a time) can be better.
There's no single answer that fits all scenarios. You'll have to profile and adjust to your particular case.