Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Model for a list of large data in QML



  • I asked this in another post but I think is worthy to have it in a separate one so.

    I need advise to choose the best model paradigm to display a list/table of data in QML.

    The requiremest are:

    • Data will be stored in a SQLite database and will probably be a complex query including joins of different tables.
    • The number of elements in the query can be very high (up to 40k right now but will probably be higher so I am assuming at least 100k).
    • Data can change in background without "direct" user intervention (new rows can be inserted or existing ones can be modified).
    • A part from the data coming from the database I would like to keep some information (in memory) for every item. For instance I am thinking in show some information at first level and then allow the user to expand the item to get detailed information. I would like to keep track about which rows were expanded during the execution of the application (no need to keep once the app is shutdown).
    • The list need to be refresh real-time (or almost). I don't want a reresh button or something like that. Also would like the UI not to seem slow.
    • It will be a desktop application running in a regular PC with Ubuntu.
    • Of course the model will be implemented in C++.

    I don't expect a solution or even demo code....just some guidence about which QML control and which model should I inherit from to be able to have the best performance.

    Thanks in advance.


  • Qt Champions 2017

    QAbstractListModel at the back with different role names to used. Best thing is not to load the entire model with 40K or 100K rows. You load number of elements which can be seen in the UI. May be you can implement some kind sliding window. Number of elements in View and Number elements in Model should be around 200 or 300. If any change in those values, you emit dataChanged(..). Otherwise don't worry. If the user scrolls or swipes, you refill the model by fetching the data from DB.
    In summary - Avoid loading the entire DB into model. It will be very slow. Load the model with required set of data.



  • @dheerendra
    OOI, why do you recommend QAbstractListModel and not one of the dedicated SQL ones, like say QSqlQueryModel, with an attached QTableView, or similar?



  • Hi there, again.
    My suggestions:

    • You can implement a custom model inheriting fromQAbstractItemModeland implementing by yourself how the model should storage and recover the information.
    • You can implement a class working in anotherQthreadto check if any database row has been updated and case some update has needed, you can change (in ui main thread) the model data and it will change the view.
    • About expansive item, you can implement this funcionality on delegate of your table using Shape-Shifting Delegates.

    I gonna to dispose a good eBook about Qt and QML below this post, i believe it can agregate your knowledge.
    I wish a good coding.

    Read More:
    QAbstractItemModel
    Qt5 Cadaques Book


  • Moderators

    @JonB said in Model for a list of large data in QML:

    @dheerendra
    OOI, why do you recommend QAbstractListModel and not one of the dedicated SQL ones, like say QSqlQueryModel, with an attached QTableView, or similar?

    QTableView is a widget, and you are rather interested in QML components. And you mentioned you want to display a list, not a table.

    As for the model, I think QSqlQueryModel should also work OK.



  • @sierdzio

    And you mentioned you want to display a list, not a table.

    I am not the OP! And the OP actually wrote:

    to display a list/table of data in QML.

    Yes, sorry about the QTableView question, I forget I am not a QML-er!

    I really only meant about why not specifically QSqlQueryModel for all the SQL support, rather than a plain QAbstractListModel derivation, where the OP would have to write all the SQL support code?


  • Moderators

    @JonB said in Model for a list of large data in QML:

    @sierdzio

    And you mentioned you want to display a list, not a table.

    I am not the OP!

    Whoops, sorry :-)



  • Thank you all for your comments.

    I have not strict limitation about what kind of list or table to use. But I think a TableView will not let me to implement things like the expandable/collapsable details or adding "fancy" things. So I think I should go for a ListView instead of a TableView.

    I have actually implemented kind of a test app using QSqlQueryModel but when the data changes I call setQuery again...this I think is very slow and the control is re-filled so I lost the location where the user was in the list. Also with these approach I don't know how to keep data that is not coming from the DB...for instance the status of a row (expanded/collapsed).



  • @XDePedro said in Model for a list of large data in QML:

    I have actually implemented kind of a test app using QSqlQueryModel but when the data changes I call setQuery again...this I think is very slow and the control is re-filled

    Yes, this is a big problem for you to think through. With your data:

    a. Do you only want to re-fetch incrementally this values which have been added?
    b. When you do re-fetch, do you wish all the old, existing rows to still be in the model/view, or do you want those to be removed and only the new stuff now shown?

    so I lost the location where the user was in the list

    This one is a minor point: if necessary, note the position in the list (via something unique in the data, e.g. a primary key) and restore after re-populate.


  • Moderators

    My - perhaps crazy - thought: use the QSql model to read your DB (side note: perhaps some other db would work better, like psql or mariadb. SQLite tends to be rather slower than others), preferably in a separate thread. When DB data changes, emit a signal to notify the upper layers. Then set up another model, like QAbstractListModel or model proxy which will:

    • remember which list elements were expanded/ collapsed (btw. to display these list items, I'd recommend implementing some custom QML delegate). You can simply extend the data object you get from SQL model and add a boolean variable to mark which items were expanded
    • present the "last read" info from your DB. So, while your SQL model will be busy reading new data (after some background data change), your proxy model will still hold and display old data. Once all is read, it will get the signal from SQL model and update itself


  • @sierdzio
    This sounds reasonable to me.
    The only thing is: given the OP's "crazy" ( ;-) ) requirement to have "at least 100k" items in his lists, it sounds like the approach will require yet another copy of all the data rows! :(


  • Moderators

    @JonB said in Model for a list of large data in QML:

    @sierdzio
    This sounds reasonable to me.
    The only thing is: given the OP's "crazy" ( ;-) ) requirement to have "at least 100k" items in his lists, it sounds like the approach will require yet another copy of all the data rows! :(

    That's why it will be crucial to implement @dheerendra's idea of windowed view. Or at the very least use lazy initialization (canFetchMore() and fetchMore()).



  • @sierdzio
    Yes, but independent of whether lazy reading is involved, my comment was that OP will have one ("large") list of rows in his QSqlQueryModel and another ("large", potentially very similar) list in his new QAbstractListModel. It may be inevitable, but it was just an observation.


  • Moderators

    Oh, right, that's true.



  • @JonB
    It might be a minor problem but I am still trying to solve it. I have to signals: beginDBChange and endDBChange. I use the first one to store the index of the selected item and then the second one to set it as a current item index. It works but I still have some annoying scrollings to set the item at the beginning or the end of the table. I would like this to be completely transparent to the user.



  • @JonB

    Sounds crazy :P but I think I'll give it a try.

    Does someone know if the QSqlQuery use any kind of lazy initialization? or when you execute a query the data is being loaded regardless nobody ask for it??



  • @XDePedro
    Not an area I know about; can only make possible suggestion:

    If you really cannot solve that by (somehow) pre-scrolling before new list is shown --- because list is being completely repopulated from new data --- ISTM you will have to come up with whatever solution to only append new rows to visual list without complete reset/redraw, whatever that might involve.


  • Moderators

    @XDePedro said in Model for a list of large data in QML:

    Does someone know if the QSqlQuery use any kind of lazy initialization?

    Like all Q*Model classes, there is canFetchMore() and fetchMore().

    Whether any further laziness is implemented in the actual loading - I don't know.



  • @sierdzio
    I have been wondering about just this for QSqlQueryModel etc. for a while now!

    Do we know how the "fetchMore" is actually implemented? I presume this is a driver implementation (QMYSQL)? For example, does it actually use a SQL CURSOR on a still on-going query (I hope not! but maybe it does), does it simply mean it fetches another packet from some complete SQL resultset, or what??

    This makes a huge difference to how I might implement efficiency in my SQL code....


  • Moderators

    Feel free to analyze the code here.



  • @sierdzio
    Thanks for that!

    Unfortunately for me, I do all my work in PyQt. That means I can't step through the code in debugger to follow everything, as I would if I used C++ and had the sources.

    Although the page's hyperlinks are good, they're not nearly nearly the same as following through stepping.

    A brief look doesn't tell me much. Though:

    QSqlQueryModel::fetchMore()
    Fetches more rows from a database.
    This only affects databases that don't report back the size of a query
    (see QSqlDriver::hasFeature()).

    At a guess MySQL will return "size of a query" (perhaps SQLite does not), so it may be all irrelevant in my situation?

    In any case, as you'll see I don't think the implementation will be in QSql.... That will rely on the abstraction level of the driver it's using, QMYSQL, and I'm thinking that is where what I would like to know is buried...

    But thanks anyway.


Log in to reply