Loading data into QTableView from QAbstractTableModel

Jo Jo

I think like this: if the user has selected sorting, then I need to re-sort the data on database level and store result in cache and force the view to pick up the new, but already sorted data from the cache, but only those that are currently visible on the screen, not all at once.

I'll note that there is a lot of data in the table, hundreds of thousands. Sorting by columns and filtering are also needed.

Do you have any other suggestions?

SGaist

Why not use the combo QSqlTableModel / QSortFilterProxyModel ?
If you really need to do querying then use QSqlQueryModel.

Jo Jo

@SGaist I think it will be slow. As far as I know QSortFilterProxyModel filters data linearly. I need filtering/sorting at the DB level using indexes.

JonB

@Jo-Jo
I don't know what your word "linearly" means here. However, I agree that (so far as I know) even with a QSqlTableModel a QSortFilterProxyModel imposed on top does not pass the sort (or filter) to the underlying SQL database. However, Googling I am unable to verify this, you should check the SQL SELECT statement generated before proceeding.

Assuming that is the case, it also means that all of the rows must be read from database to memory for sorting to work, and rows which fail the filter are still read into the client (neither of which is the case if you sort/filter at the database) Which is slow/memory consuming, if your data is big.

You can use QSqlQuery, QSqlQueryModel or QSqlTableModel with your own ORDER BY and/or WHERE as appropriate if they do not offer to pass that to the SQL backend.

Going back to your original question. You write "data can be continuously coming into the model". What do you mean by that, given that the data is in a database, not e.g. arriving from a real time device?

If you say that each time all the data rows are different, you can either delete all current rows and continue with the model as-is or you can call beginResetModel(). The latter will require it to get column definitions again on next fill call. My hunch is that these two will perform at about the same speed. QSqlQueryModel /QSqlTableModel have a clear() method to delete all rows. Might be preferable to resetting the model.

Jo Jo

@JonB said in Loading data into QTableView from QAbstractTableModel:

I don't know what your word "linearly" means here.

"Linearly" means O(n) complexity. It's too expensive especially if we have a many rows.

@JonB said in Loading data into QTableView from QAbstractTableModel:

What do you mean by that, given that the data is in a database, not e.g. arriving from a real time device?

The database is frequently updated. All changes should be displayed in QTableView. In addition to updating the database, the user can also select their filters and sorting, table should work in real time.

JonB

A SQL WHERE clause will also likely filter "linearly", it may well use primary key look up to avoid that depending on column, but don't assume that even if you have an index on a different column which is in the WHERE it will select that to avoid linear filter.

Anyway, I do not dispute that if you have a "large" number of rows and you want to be "efficient" (time and space) you may want to do your sorting & filtering at SQL side. Upon re-reading, QSqlTableModel at least has a setSort() which does get passed as ORDER BY to the SQL query. And equally it has a setFilter() for the WHERE. So instead of using those from QSortFilterProxyModel --- which I don't think will use these from QSqlTableModel --- use them explicitly yourself.

You also mention:

The database is frequently updated. All changes should be displayed in QTableView.

Be aware, if you are not already, that Qt SQL stuff knows nothing about the data being changed outside at the server. That is entirely up to you to code, you get no notifications that anything has changed, you will have to refresh on a timer.

In addition to updating the database

If other processes are updating between when you read records and when you update them, you might like to look at the SQL UPDATE statement generated. I am not sure it fully uses pessimistic locking --- UPDATE ... WHERE col1 = value_read_previously AND col2 = value_read_previously AND ... --- as MS ADO.NET SQL driver I used to use did, so you should be careful to see what it will update.

Jo Jo

@JonB

As far as I understand, setSort will sort and return all columns. This is not optimal.
I need to sort the list and get only row IDs as a result. Then use lazy loading to load rows by their ID.
Same for filtering.

JonB

@Jo-Jo
I don't know what you mean. setSort() does not return any columns, it just sets what column to sort by which will be put into the SQL query. That is quite separate from which columns you want.

It's just an option in QSqlTableModel. You probably don't want that if all you want is "row IDs". Are you going to build some enormous WHERE id IN ( ... ) clause to load rows by a subset of IDs?? And why would you do that, why get IDs first and then use them to get rows when you could get the rows in the first place in sorted order?

Maybe you know what you are doing, I'm afraid I do not.

SamiV123

resetting the model is going to cause performance issues + the user is going to lose their selection and current cell/row/item

What you want to do is to use the beginInsertRows and endInsertRows instead.

fazanbaba43

This post is deleted!