QSql*Model are very slow

[ABBAPOH]

Hi all.
I have rather big database (20000 records with 17 columns each).
I use QSqlTableModel to display that base. However, when i try to scroll using scroll bar, my treeView lags (for about 1-2 seconds). Seems it lags when first time calling data() method to retrieve data from database. After fields are cached to view, i can scroll without any lags. How can i fix lagging for first call or at least precache data to view?
P.S.: same lags when using QueryModel
P.P.S.: also i have lag when model does fetchMore(), but it can be fixed by fetching all model.

[wysota]

The only viable solution is to create a custom model that will fetch rows manually using a worker thread.

[ABBAPOH]

Well, even after fetching model i have lags when accessing elements for the first time. So thread on fetching will not help i think.

[wysota]

It will help.

[ABBAPOH]

Well, after some tests it appears that loop

while (model->canFetchMore())
model->fetchMore();
takes very little time.
However, showing treeview with model that is already fetched takes about 7-10 seconds. So, threading fetching won't help me, isn't it?

[wysota]

It will help. If you do it wisely, of course.

[javimoya]

why are you using a qtreeview instead of a qtableview?

anyway...
I don't understand your "lag"....

Just right now I'm working with a query with 15000 records... (qsqlquerymodel + qtableview) and it's pretty fast.

I think that you have another problem.

(¿are you using a subclassed view or model?? )

[ABBAPOH]

The problem was in QTreeView. Using QTableView has no problems (lags are very little on sorting, about 0.3 sec, which is acceptable and no lags on scrolling). Thanks all.

[ABBAPOH]

Not to create new thread.
Is it correct that my own table model will always be faster than sql model when showing all data/sorting it?
And what about filtering with ProxyModel - will it be faster or slower than filtering with sql query?

[wysota]

It depends. If the database is not optimized in some special way for sorting in a particular order, doing it locally will probably be faster.

[ABBAPOH]

It is optimised. I choose myself the format of database. I only need to take decicion - to use sql database and classes for it or implement everything myself (storing in memory, sorting, filtering, editing).

[wysota]

It is optimised.
And based on what do you say that? You can't possibly know that upfront unless you implemented the database engine yourself.

[ABBAPOH]

I meant i created indexes in sql table and not try to sort not indexed columns))
however yes, sqlite driver implementation can be rather slow, however i hope it is not.

I tested sorting methods with different model implementations (SqlTableModel, SqlQueryModel + Proxy, custom model with data in memory + Proxy for sorting). Seems true, however i thought sql will be faster:)

Sorting... time elapsed: 150 ms - OwnModel + Proxy model
Sorting... time elapsed: 60 ms - OwnModel + Proxy model reverse
Sorting... time elapsed: 421 ms - SqlTableModel + fetchall
Sorting... time elapsed: 391 ms - SqlTableModel reverse + fetchall
Sorting... time elapsed: 1076 ms - SqlTableModel not indexed column + fetchall
Sorting... time elapsed: 236 ms - SqlQueryModel fetched + Proxy model
Sorting... time elapsed: 99 ms - reverse

[wysota]

Sql might be faster only if you request data in the order that it is stored in the engine. That's what I meant by being optimized for sorting in some particular order.