QSqlTableModel Network Performance

[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.

[ZNohre]

Full code linked below if it's helpful to anyone else.

https://github.com/ZNohre/qt-cached-sql-tables

[ZNohre]

@ZNohre said in QSqlTableModel Network Performance:

Is this an application design issue? i.e. should the database not be accessed in the cloud? If so, what approaches have others used for database access from multiple locations?

Posting an update here as a breadcrumb trail for others that may find this post. While the above CachedSqlTable methods worked in development, during release the architecture was not scalable or secure and created a host of issues with deployment (as others had previously noted).

I ended up creating a .NET Web App API hosted in Azure as the boundary to the cloud SQL database. This involved refactoring out the CachedSqlTable methods in favor of an ApiClient class that calls HTTP endpoints to handle the CRUD operations. Rather than SQL authentication, the user is logged in via authenticated JWT and the ApiClient class has methods that mirror each endpoint to handle all operations.

I'm self-taught and had never worked in .NET, C#, or Web Apps before. However with some AI prompts and trial and error I was able to get this working without too much headache.

This was a major foundational refactor and in hindsight I wished I had known about this approach which is why I'm making this follow-up post roughly a year later :)

[SGaist]

@ZNohre I just realized now that I somehow missed to tell you about shielding your database behind a REST API so you wouldn't expose your database over internet directly. Sorry for that but glad you went that route.

[ZNohre]

@SGaist No worries, I was attempting to do something that nobody actually does in practice (I just didn't know it at the time) so it probably was assumed a REST API was being used in some fashion already; it wasn't - plain old direct SQL access and authentication over the internet.

Once you see the REST API and implementation, it's obvious that it's the correct approach. My journey was more organic though from local files -> local SQL -> cloud SQL -> REST API. I'm sure if I had taken a course in application development or something I could have jumped to the end, but why walk down one right path when you can walk down three wrong ones first? ;)

During development, the database was only accessible via explicitly whitelisted IP addresses and was only test data at that so nothing would have been exposed.

Thanks for all the help, as always.

[SGaist]

Not three wrong ones, each has its use 😅
That said, you learned stuff on the way which are valuable

You're welcome !