Model View Design challenge with larger dataset

[swankster]

@JonB Yes, we do need the ability to view the full day of events. for remote troubleshooting of to confirm random occurrences that can take place on our machines.
I am creating 8 different datasets each with 56k records. and i am doing a QFile to QContiguousCache. which works great at the top of the view, but as I scroll down the view update takes longer and longer. I currently do no have mmap implemented. I am hoping that will resolve my latency as i stroll further into the view.

no, i do not use setIndexWidget() in QTableView.

btw, great tutorials

[ankou29666]

you do need to view your full day events, this is normal. Do you need to have all those 56k events in memory all at once ? when your screen won't be able to display all those items at the same time ... I agree with @JonB on that matter.

I don't believe your lag issues come from the model, but rather from the view :
QML TableView only instanciates the items visible in the view (+3 rows up and 3 more down by default if I'm not mistaken), and has the ability to recycle the delegates that are gone out of the view, such that when you scroll down, the top UI items that have gone out of the view are reused and repositionned at the bottom with the new data that have become visible.
but as far as I understood, QtWidget's QTableView doesn't. I had made a comparison between QtQuick/QML and QtWidget and QTableView was very slow and laggy in comparison to it's QML counterpart which was very fluid. I don't remember for sure the size of my model but I'd say around 100 rows by 6 columns or so. So with such size ...
so my guess it that you are wasting your time trying to optimize the model when your model is not the problem, and I suggest you try with a QML TableView inside a QQuickWidget.

[JonB]

@ankou29666 said in Model View Design challenge with larger dataset:

I don't believe your lag issues come from the model, but rather from the view :

QTableView was very slow and laggy

I try the following:

#include <QTableView>
#include <QApplication>

class MyModel : public QAbstractTableModel
{
    int rowCount(const QModelIndex &parent = QModelIndex()) const override { return 56000; }
    int columnCount(const QModelIndex &parent = QModelIndex()) const override { return 12; }
    QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const override
    {
        if (index.isValid())
            if (role == Qt::DisplayRole)
                return QString("Row %1, Column %2").arg(index.row()).arg(index.column());
        return QVariant();
    }
};

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    QTableView w;
    MyModel myModel;
    w.setModel(&myModel);
    w.show();
    return a.exec();
}

On my "potato" machine/VM, Ubuntu 24.04, Qt 6.4.2, this scrolls as smoothly as a baby's bottom (are we still allowed to say that?!). Unless perhaps you or OP are on Windows and that behaves differently, I conclude that any "lag" comes from the model not the QTableView.

I have not written/tested with a backend database. I may do so....

[JonB]

@swankster said in Model View Design challenge with larger dataset:

Subclassing QAbstractTableModel into my custom myModel then

Before we go any further: Why have you chosen QAbstractTableModel instead of QSqlTableModel/Query? Ah, is that because you query the SQL, perhaps with one of these, and then do your "transformation" to CSV/QFile?

Whenever it is you query the database, do you call void QSqlQuery::setForwardOnly(bool forward)? From my experience forward-only is terribly significant for performance on SQL/MySQL. If you have not done that you should try it. I still do not think you should need to do any of CSV-serialization, use of QFile or QContiguousCache.

[ankou29666]

I didn't try with a DB backend model either, and I'm not sure about it but I just wonder whether my test was with that exact same kind of model, and the difference was huge between qml and qtwidgets. I didn't try on linux either, however I don't remember whether I did it on windows or mac. The 2015 MBP has become a potato (despite it's 16gigs of ram, the core i7-4720 is not up to latest macos versions). The PC is a bit more recent and has much better hardware (core i9-9900K, 64gb ddr4 and GTX1070 even though I'm not certain the graphics card plays a (significant ?) role).

Qml TableView's documentation has a whole chapter about "reusing items" that has no equivalent in QTableView, stating

This approach gives a huge performance boost, depending on the complexity of the delegate.

so it's what lead me to the conclusion that QTableView was slower than Qml TableView. I might be wrong, i'll try that again.

[JonB]

My PC is potato, and VirtualBox running Linux under Windows adds more potato-ness. It seems the Linux VM does use my NVidia card, but barely registers much usage when scrolling. I do not know how your in-memory model could have been written/performed much different than my example.

I am about to give a go with a DB backend. I can barely remember how I have set up MySQL on the Linux box so I may try with SQLite to save brain ache....

[swankster]

@JonB yes I do query the SQL.

@JonB said in Model View Design challenge with larger dataset:

perhaps with one of these, and then do your "transformation" to CSV/QFile?

I dont understand what you are asking here.

no i have not used void QSqlQuery::setForwardOnly(bool forward)
i will try applying this. thanks for the suggestion.

[JonB]

@swankster said in Model View Design challenge with larger dataset:

I dont understand what you are asking here.

Normally you use QSqlQuery or similar when your model is a SQL database, and that is what you set QTableView's model to. However, you say you are binding to a QAbstractTableModel. So I don't know why, or where you do your SQL query. If I understand right, you copy the SQL results to a QFile (perhaps as CSV format). I can only guess that is what you bind the table view to and hence that is your own QAbstractTableModel rather than a QSql... one, is that right?

[JonB]

@swankster , @ankou29666
I smacked together the following to test with a database backend:

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    QTableView w;

    QFile::remove("sqlitedb.db");
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("localhost");
    db.setDatabaseName("sqlitedb.db");
    bool ok = db.open();
    Q_ASSERT(ok);

    QSqlTableModel myModel(nullptr, db);
    QSqlQuery q;

    ok = q.prepare("CREATE TABLE t1(row INT, col0 INT, col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6 INT, col7 INT, col8 INT, col9 INT)");
    if (!ok)
        qDebug() << q.lastError().driverText() << q.lastError().databaseText();
    ok = q.exec();
    if (!ok)
        qDebug() << q.lastError().driverText() << q.lastError().databaseText();

    for (int row = 0; row < 56000; row++)
    {
        ok = q.prepare("INSERT INTO t1 VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        if (!ok)
            qDebug() << q.lastError().driverText() << q.lastError().databaseText();
        q.bindValue(0, row);
        for (int col = 1; col <= 10; col++)
            q.bindValue(col, col);
        ok = q.exec();
        if (!ok)
            qDebug() << q.lastError().driverText() << q.lastError().databaseText();
    }

    myModel.setTable("t1");
    myModel.select();
    qDebug() << myModel.rowCount();

    w.setModel(&myModel);

    w.show();
    return a.exec();
}

I admit I am using SQLite rather than MySQL, because I can't be bothered to figure out my MySQL. It takes quite a while to insert all the rows, but thereafter the scrolling is regular, fast and smooth. (About 1,000 rows every 2 seconds with my finger on Page Down key on the table view, which I suspect is down to the key auto-repeat rate rather than fetching/displaying rows, and certainly fast enough for the user.)

Note that the initial qDebug() << myModel.rowCount(); does indeed print 256 as I suggested would be the case. The Qt SQL code fetches "buffers" of 256 rows at a time. If/when you want more you have to call fetchMore(). The QTableView is doing this internally in response to the scrolling whenever it reaches the end of the latest buffer. There might be a tiny pause each time, though too fast to tell. And once fully populated I can scroll up & down freely.

YMMV. MySQL will certainly require a cross-process access compared against SQLite's direct file access. And if your MySQL database/server is on another machine that will be an overhead.

Why don't you start by seeing how this performs for you?

The only thing I can think of which could cause a "delay/lag" is when the view needs to request the next 256 rows from the db. This would occur during table view scrolling. But it should not become any progressively worse as more records have ben read. You can do all the fetchMore() calls yourself in advance (while (canFetchMore()) fetchMore();) to fully populate the model before displaying the view. From that you should see how much delay they cause on their own and you can see the view's behaviour when there is no database access going on.

Over to you, @swankster ! I really don't know exactly what you are doing or what your situation is. My own finding/belief is that it should work as shown in your situation without excessive lag and without all your pre-processing shenanigans.

[swankster]

@JonB correct