How to prevent QTableView/QTreeView doing query/sorting multiple times?

JonB

I am getting frustrated by the behaviour of views executing data queries "behind the scenes", without being documented, and repeatedly/unnecessarily. (My examples use Qt 5.7 & PyQt 5.7.)

Following the standard example at http://doc.qt.io/qt-5/qtwidgets-itemviews-customsortfiltermodel-example.html (section Window Class Implementation), I have code like:

self.model = QtSql.QSqlTableModel("table", self)
self.view = QtWidgets.QTableView(self)
self.view.setModel(self.model)
self.view.setSortingEnabled(True)
self.view.sortByColumn(col, Qt.DescendingOrder)

So we have setSortingEnabled() & sortByColumn().

What they don't tell you is that each of these statements performs a full table populate query. By tracing calls to MySQL I see that the setSortingEnabled() executes a SELECT * FROM table and then the sortByColumn() executes a SELECT * FROM table ORDER BY col DESC. (Reversing the order of the statements does not help: then the latter query with ORDER BY col DESC is simply executed twice in a row.)

Apart from the fact that this is not documented, performance is obviously compromised, and this is given as a standard example. I cannot find any way to execute these two statements without performing two SELECTs. How are you supposed to do so?

SGaist

Hi,

The only thing that comes to mind to try is to invert the setSortingEnabled and sortByColumn calls.

In any case, there's no way for Qt to know that it should refrain from doing these queries. These methods might be call at any time thus requiring these updates.

JonB

@SGaist said in How to prevent QTableView/QTreeView doing query/sorting multiple times?:

Hi,

The only thing that comes to mind to try is to invert the setSortingEnabled and sortByColumn calls.

I already stated: "Reversing the order of the statements does not help: then the latter query with ORDER BY col DESC is simply executed twice in a row"

In any case, there's no way for Qt to know that it should refrain from doing these queries.
These methods might be call at any time thus requiring these updates.

So you can't enable sorting and do an initial sort without fetching the data twice from the database. I wonder how many people are aware their code is doing that. That's simply wrong.

SGaist

My bad, I misunderstood the "reversing the order" part.

In any case, how would you propose that a generic component be aware of the fact that you are going to show data from a database but it should wait for you to both enable sorting as well as configure it before starting to fetch data ?

JonB

@SGaist
I would simply like to present a grid to the user with columns he can click on to sort. And I'd like the grid to start out sorted by some column. Just what a QTableView is supposed to be for. Does that sound reasonable? It's what hundreds of applications do.

OK, and I expect to achieve the above by having one SQL query like SELECT * FROM table ORDER BY col executed. Not two. That's all. Pretend my query takes 10 seconds to execute. How do I achieve that, please?

The way I see it at present, with Qt that requires the data to be fetched twice? Either that is not the case, or it's wrong.

[BTW, I haven't got the reference, but something like QSqlTableModel specifically has a call to let you set the sort attributes before the SELECT gets executed.]

SGaist

What you want to do is clear. However, your expectations about how QTableView should be working are wrong. That's a generic component that should work with all possible implementations of a QAbstractTableModel. It's not particularly optimised for the SQL use case.

What happens currently is basically:

You set a model on the table view -> the table view loads the data so it can show something
You modify the view to enable sorting -> the model should be sorted based on the default values
You modify again the view by changing the sorting -> the model should again be sorted but with the new values

You are likely referring to QSqlTableModel::setSort which requires a call to select for the update to happen.

So what you would need is a variant of QTableView that would setup itself based on the sorting you did first on your QSqlTableModel when you set the model on that view.

Note that QSortFilterProxyModel might also be of interest.

JonB

@SGaist
Thank you for your reply, it's late tonight here, I shall look into it tomorrow!

VRonin

@SGaist said in How to prevent QTableView/QTreeView doing query/sorting multiple times?:

Note that QSortFilterProxyModel might also be of interest.

Big up for this line. QSortFilterProxyModel is definitely what you need

JonB

@SGaist

Thank you for your clarification on the behaviour of QTableView.

Your description implies there should actually be 3 queries against the data, then. I only see 2. However, I admit I would need more time to be sure.

Let me see if I have understood the behaviour of setSortingEnabled() & sortByColumn():

setSortingEnabled() is what makes the View show sort indicators on column headers and allow clicking. It also executes a data fetch query, passing whatever (if anything) is currently defined as the sort order (from sortByColumn()) into an ORDER BY.
sortByColumn() determines the ORDER BY. It too executes a data fetch.

(Please correct me if I am wrong.) Now, after (just, i.e. no sortByColumn() yet) a setSortingEnabled() I will see the sort indicator in some direction on some column. I am guessing this would be Ascending on column #0 initially(?) However, the query it issues has no ORDER BY at all, so by definition the rows are in indeterminate order. This seems simply inconsistent to me.

What I would have expected/hoped is that setSortingEnabled() would have passed an ORDER BY col0 ASC if that is how the data is supposed to be sorted to correspond to the initial sort indicator state, and/or an optional parameter to allow specification of the initial sort order desired in the View and to be passed in the initial query --- in effect, combining the setSortingEnabled() with an initial sortByColumn(). Or even, as you say, an equivalent of QSqlTableModel.setSort(). As it stands it seems you must fetch the data twice simply to correspond to the View's initial state.

I have inherited 32K lines of code which uses every kind of Qt SQL/model/view architecture all over the place, it's a mess. I will indeed investigate QSortFilterProxyModel. I don't mind which code I use to achieve it, I just don't expect any necessity to fetch the data multiple times for the initial situation.

MrBolton

@JonB said in Default sort indicator order in QHeaderView:

@MrBolton
Thanks for replying.

Your solution is not what I am looking for, unfortunately. I do not even have a QSortFilterProxyModel. Specifically, I do not want sorting to happen at the client. I wish it to happen at the server with a ORDER BY.

Why do you want the sorting to happen on the server? This way, every time you change the sorting, a new query is executed to fetch the date from the server.
Doing the sorting with help of a QSortFilterProxyModel would be much more performant with bigger data sets since the data is only fetched once.

JonB

@MrBolton
Sorry, I really don't understand your comment. Precisely the point is that with "bigger data sets" you would have to fetch all the data, which is incredibly slow and may well exceed available memory. (OTOH, if the dataset is "small" the overhead of re-query compared to holding all the previous rows locally is also "small".) As a rule of thumb, one always wants as much work to be done at the server side as possible, not client side.

Let's say the table has a million rows and the query I want to pose is:

SELECT * FROM table
ORDER BY column
LIMIT 100

Very approximately, pushing the ORDER BY to the server instead of into a QSortFilterProxyModel will run 10 thousand times faster and use one ten-thousandth of the client memory. That's why I would be very careful before advising people to use QSortFilterProxyModel without understanding the ramifications!