QSqlTableModel Network Performance

[JonB]

@ZNohre
To diagnose what is going on when, when I did my database work I always found it invaluable to be able to see (debug out) each query executed every time. I cannot remember whether/how I did this with Qt QSQL... calls. Maybe there were protected methods I could override, maybe there is some "trace" flag you can set. If an expert can recall what is available that would be helpful?

UPDATE
Being unable to spot it myself in docs or by Googling, I tried ChatGPT. It said it was having a long think about this :) and then

Short answer: Qt does not provide a single built-in global switch that prints every SQL statement issued by all QSql* classes.

Hmph! Why in the world not? They really ought to offer some such from their code, wherever they send something the SQL db, it really wouldn't be that difficult for them....

[SGaist]

@JonB Two steps (more or less):

• Build the SQL module with QT_DEBUG_SQL defined
• Ensure the qt.sql.qsqlquery logging category is enabled

Go to the source ;-)

[JonB]

@SGaist said in QSqlTableModel Network Performance:

Ensure the qt.sql.qsqlquery logging category is enabled

That sounds like it would be a good idea! So how come ChatGPT doesn't know about it?

Edit: Oh, you mean these are not alternatives, both are required? So that requires building from source code, some of us do not do that. May be relevant to OP though.

[SGaist]

@JonB Because ChatGPT does not know everything. These kind of information are just so statically insignificant that it won't mathematically appear when it tries to find the next token to answer your question.

Unless you feed it the Qt sources and actively tell it to find such things in there, there's a very low chance that it will find it as you have experienced.

[JonB]

@SGaist That is sad to hear :)

[ZNohre]

@JonB I checked this out on the server side during the initial posting and found that one of my widgets had called its SELECT query 5,777 times(!) in maybe 2 hours of testing. I'll have to look into @SGaist's solution below re: the QT_DEBUG_SQL defined.

My cloud hosting bill may be a bit higher this month :)

[ZNohre]

@IgKh Thank you, I still have a lot to learn about networking and databases so I wasn't aware of this. Is there a preferred tool/method to check these metrics (RTT and network hops)?

[IgKh]

@ZNohre

For RTT you can use ping, but it sometimes blocked or not accurate. A good way is to run a query that doesn't need any data (such as SELECT 1) several times and measure the average execution times. Anything over 1-5 milliseconds is to be considered high latency.

For network hops use the traceroute command, or its equivalent on your platform.

[Kent-Dorfman]

Full tableview models over an internet are bad ju-ju. You should switch to a client/server transaction model. It's ok to load managable chunks into a local table presentation, but you MUST limit the number of returned rows and use indexes properly. Never blindly load a table. Doing that means you are not using the relational database properly. Only time you should ever need full table access is during maintenance as a DBA, but even then it's not strictly necessary.

trying to predict and manage latency won't solve your problems. When you switch to a networked database model your access mechanisms have to allow for unpredictable latency.

Some hints

1. make sure your SQL is being executed as stored procedures on the database server where the data is hosted, not the local machine..."cloud" should make you nervous if the table data isn't stored on the same machine as the DB server.
2. switch to a rigid transaction model rather than a table view model
3. make effective use of proper indexing and limiting of SQL result sets

Sorry to be the voice of gloom, but local DB and network are two different worlds.

[ZNohre]

Thanks again for all the replies here; circling back to mark this as solved.

I ended up implementing a custom QAbstractTableModel based on the ModifiedRow class in the QSqlTableModel source code.

The local data is populated via a QSqlQuery with setForwardOnly set to true.

void CachedSqlTableModel::select()
{
    if(m_select.isEmpty() || m_tableName.isEmpty()){
        qDebug() << "Invalid select statement";
        return;
    }

    //Initialize query
    QSqlQuery query;
    query.setForwardOnly(true);
    query.prepare(selectStatement());
    query.exec();

    if(query.isActive()){

        beginResetModel();

        //Reset data structure
        m_cache.clear();

        //Populate header data
        m_record = query.record();

        //Populate table data
        while(query.next()){
            m_cache.push_back(CachedRow(CachedRow::Update, query.record()));
        }

        endResetModel();

    } else {
        m_error = query.lastError();
    }

All of the related cached database operations are executed in the exact same way as the code in the QSqlTableModel database handlers.

    virtual bool updateRowInTable(int row, const QSqlRecord &values);
    virtual bool insertRowIntoTable(const QSqlRecord &values);
    virtual bool deleteRowFromTable(int row);

A big thank you to @Kent-Dorfman for the additional insights here. I implemented all of the suggestions above (stored procedures, transactions, indexing, limiting datasets, etc.) and there is a noticeable speed difference on all fronts.