Better way to store query results from db in Qt Lists

mardzo

I have a select query from which i want to store results in QT lists after query execution. Some of my code is:

QSqlQuery querySelect;

QStringList rfid;
QList<QDateTime> datetimeIN;
QList<QDateTime> datetimeOUT;

... Some other code + querySelect prepare.....

if(querySelect.exec())  {


          while( querySelect.next() ) {

                rfid.append(querySelect.value( 0 ).toString());
                datetimeIN.append(querySelect.value( 1 ).toDateTime());
                datetimeOUT.append(querySelect.value( 2 ).toDateTime());

          }

     }
     else  {
         
       qDebug()  << querySelect.lastError();
   
  }

I want to know is there another, more optimized faster approach to store the results in the lists, other than with the query.next while cycle because i think this is rather slow?`

JonB

@mardzo
No, you do have to fetch each row out of the query result set one at a time and do whatever you want with the data. In terms of "slow", be aware that this is only doing the loop client-side, it isn't actually doing it one-at-a-time server-side (e.g. with a SQL CURSOR), so it's not that bad.

As for your "style" of handling the rows returned by storing the column values into 3 separate list variables. This is a bit "messy", and relies on you keeping each list in sync with the others and using the same index for each one. Do you not think a "nicer" way to represent your rows fetched from the data source might be to create a class which has member variables for a single rfid, datatimeIN & datetimeOUT --- i.e. it represents a single row --- and then return a QList<RowClass>?

mardzo

Yes for that you are right, but as this was only a test program for presentation, i will fix the above asap. Just wanted to tel that when I put querySelect.setForwardOnly(true); the data filling is much faster

JonB

@mardzo said in Better way to store query results from db in Qt Lists:

querySelect.setForwardOnly(true); the data filling is much faster

http://doc.qt.io/qt-5/qsqlquery.html#setForwardOnly :

Forward only mode can be (depending on the driver) more memory efficient since results do not need to be cached. It will also improve performance on some databases.

I'm (really quite) surprised this makes much difference, and intrigued. Are you using MySQL, on what platform, or what? How big is your resultset from your query?

mardzo

Im fetching results from a remote Microsoft SQL Server 2012 on a local network installed on a windows 10 machine. As this software is still in development i am currently fetching about 500 rows from 6 columns in a request

Taz742

@mardzo
We can not change the Qt base and its aspirations.
The only thing we can do is keep this information on the Qt side so that further operations are quick.

Which @JNBarchan said well:
@JNBarchan said in Better way to store query results from db in Qt Lists:

i.e. it represents a single row --- and then return a QList<RowClass>?

QHash <QString, RowClass> will be faster then QList<RowClass> .

For example:

struct RowClass {
    QDateTime dtIN;
    QDateTime dtOUT;
    QString rfid;
}

QHash<QString, RowClass> hash;

while( querySelect.next() ) {
    QString rfid = querySelect.value( 0 ).toString();
    QDateTime dtIN = querySelect.value( 1 ).toDateTime();
    QDateTime dtOUT = querySelect.value( 2 ).toDateTime();
    
    RowClass cls;
    cls.rfid = rfid;
    cls.dtIN = dtIN;
    cls.dtOUT = dtOUT;

    hash[rfid] = cls;
}

JonB

@Taz742

QHash <QString, RowClass> will be faster then QList<RowClass> .
hash[rfid] = cls;

You are assuming that OP's rfid is a primary key (or at least unique). While that may be the case, the OP does not state that anywhere in his question.
QHash will only be faster than QList if the OP does multiple lookups by key field in his results. If he does not --- e.g. he just uses the values once to populate the UI, say --- it will actually be (marginally) slower & more memory.

mardzo

Just to clarify, rfid is not a primary key, and once i fetch the data, i am using it to fill a QStandardItemmodel and display it in a QTableView in the ui

JonB

@mardzo
Than as I said, stick with QList, not QHash, in your example.

You still have to fill the QStandardItemModel one row at a time, there is nothing more efficient than that offered from Qt. In this case, of course, there is no need to involve a QList, you do it straight from querySelect.next() into QStandardItemModel.setData().

JonB

@mardzo said in Better way to store query results from db in Qt Lists:

Im fetching results from a remote Microsoft SQL Server 2012 on a local network installed on a windows 10 machine. As this software is still in development i am currently fetching about 500 rows from 6 columns in a request

I'm still surprised/intrigued that you say setForwardOnly() makes any (noticeable) difference to speed. Are you sure it does (i.e. you've run it a few times with and without)? (e..g It would be more likely that it's the "random" delay in response from remote SQL Server than the forward-only.) I'd be grateful if you confirmed this, as it might make a difference to me in code I have inherited --- thanks.

Taz742

@mardzo said in Better way to store query results from db in Qt Lists:

Just to clarify, rfid is not a primary key, and once i fetch the data, i am using it to fill a QStandardItemmodel and display it in a QTableView in the ui

When I use QTableWidget and fill it with my class. I use a 0-bit column for UID-like. When I need to edit or delete this record in the database, I have this record UID in the QTableWidget's last row and in the 0-van column and this class from QHash is quicker.
If you're the same, I recommend you use QHash.

@JNBarchan I wanted to say this from the beginning.

mardzo

@JNBarchan yes i have run it multiple times with and without and in I confirm in my case it makes a difference.

Cheers

VRonin

Did you try running your program in Release mode? Debug mode for QtSql is quite heavy and might be the real bottleneck here (apart from network but that's not something you can control)

@mardzo said in Better way to store query results from db in Qt Lists:

Just to clarify, rfid is not a primary key, and once i fetch the data, i am using it to fill a QStandardItemmodel

Then why are you putting a further step inbetween? just fill the model in the query loop.

JonB

@Taz742 said in Better way to store query results from db in Qt Lists:

@mardzo said in Better way to store query results from db in Qt Lists:

Just to clarify, rfid is not a primary key, and once i fetch the data, i am using it to fill a QStandardItemmodel and display it in a QTableView in the ui

When I use QTableWidget and fill it with my class. I use a 0-bit column for UID-like. When I need to edit or delete this record in the database, I have this record UID in the QTableWidget's last row and in the 0-van column and this class from QHash is quicker.
If you're the same, I recommend you use QHash.

@JNBarchan I wanted to say this from the beginning.

Before we confuse the OP, he wants to use QStandardItemModel, and as @VRonin & I have said, he should not go via either QList or QHash, he should just fill the model in the query loop.

Next bit for @Taz742 only:

Assuming you only show, say, at most 100 rows to the user (you don't want to show thousands, do you), and do the occasional one-time lookup of a column I can't see it will be faster with a QHash than a QList (assuming you remember to count the time for populating the QHash as well as the single lookup). But whatever.

What I don't get, however, is why you're doing any lookup at all? From a QTableWidget/QTableView, isn't the whole point to use the QModelIndex functions to map directly from the view to the row in the model, and use that, without any searching?