Solved PyQt5: Very slow QSortFilterProxyModel(not customized)

[Rhdr]

How do you optimize the performance of a QSortFilterProxyModel based on the example code below?

Example: In the example below I extract 200 records from the database, load them in a QSortFilterProxyModel & finally display them via a sort-able QTableView. Once the user click on a column header it takes about 20 seconds for 200 records to sort(?!) (If I sort the same amount of records in excel it takes less than a second)

from PyQt5 import QtCore, QtWidgets, QtSql
from models import _databaseConnection
        
class FormController(QtWidgets.QMainWindow):
    def __init__(self):
        super(FormController, self).__init__()
        #connect to db
        _databaseConnection.connect()
        
        #setup model
        modelSource = QtSql.QSqlQueryModel()
        modelSource.setQuery("""SELECT TOP 200 Entity.Name, Entity.Surname
                                FROM Entity""")
        
        #setup proxy
        modelProxy = QtCore.QSortFilterProxyModel()
        modelProxy.setSourceModel(modelSource)
        
        #setup ui
        self.resize(900, 700)
        tableView = QtWidgets.QTableView()
        tableView.setSortingEnabled(True)
        tableView.setModel(modelProxy)
        self.setCentralWidget(tableView)
        
if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    f = FormController()
    f.showNormal()
    sys.exit(app.exec_())

Edit1:
One work around to speed up the sorting is to simply re-query the database and request a sorted result set. The drawback however would be increased data usage for mobile users...

[Denni 0]

Okay why are you sorting them in code rather than via the Database engine? As it is designed (or can be designed) to do that extremely efficiently

Conversely if you do not know what sort order you will be using then instead of sorting them build a sortable index based on the the things you might need to resort the list on this reduces the amount of data the program has to manipulate and/or you can create these indexes (in the background) while presenting the current list to the user.

[Rhdr]

You right, I can do the initial/default sorting when loading the query as you suggested, but I still want the users to customize the sorting by clicking on the column headers should he/she wish to do so. Also I thought this is what QSortFilterProxyModel was designed for...

How do you go about building a simple sortable index in qt? Are you referring to caching the data in something like a 2d-list and sorting that instead then afterwards updating the view? (Sorry, I did some searching on the net and could not find any reference to sortable indexes in qt)

[Denni 0]

Well yeah a sort of 2'D list but de-referenced perhaps it all greatly depends on the data you are working with. Just keep in mind that computers love numbers and tolerate everything else by viewing them as special numbers. So anytime you can reduce what you are doing to just numbers (including sorting) you are going to see improvements in speed -- as there is no overhead necessary that would come along with any "special" number.

Still even if you did not de-reference it you could build an Ordered Dictionary with the sorted column as Key and the Key to the Dictionary the table data is stored in being the value. Aka {SortedKey : IndexKey} then all you have to do is iterate through your SortedDict.keys() and reference the FullData[IndexKey] to get the data you need to display.

Further you could, as I stated pass this off to say a worker thread to implement, give it the full list and have it create the various sorted dictionaries, while you are displaying the current one - then while waiting on the User to do something -- the worker thread takes that initial data and creates the various sorted dictionaries that you might need to implement for what the user might want to sort on (or what you allow them to sort on) implemented in most likely to least likely order perhaps.

Of course had the Sort Filter Proxy been quick enough this would not have been necessary but when it comes to speed sometimes simple things can make a world of difference. Lastly I am not saying this is the best answer to this situation it is just an answer. You might find a better one but if not at least you have something to move forward with.

Heck I just learned today that the documentation on how to use a QThread is all wrong and you should not sub-class the dang thing -- can you say back to the design board.

[Rhdr]

Seems like a balancing act either you increase the ram requirements or the app's data usage(front end vs back end processing). Will have to play with it and see which method is preferable.

Either way it may go it seems back end processing would be a lot easier/faster to implement. I think for now I will run with it, get the app up and running and revisit the data usage issue in a future iteration if need be.

Thank you

[Denni 0]

Yes that is generally the trade off -- if you want more speed on the client you have to use more memory (which btw is fairly cheap these days or so they tell me) -- however if you want to use less memory it generally means the program will run slower -- and this trade off has been around for ages ... where it used to be memory was in short supply so speed suffered but now we can speed things up dramatically by just using more memory -- note there were programming tricks and back-flips you could perform in order to speed up code when memory was tight but most of these rather complex methodologies have given way to simply throwing more memory at it. So good luck and always K.I.S.S. it (Keep It Simple and Smart)