QSqlTableModel record performance with QString concat

JahJerMar

Hi all. I currently have a 5 qsqltablemodels & 5 qtableviews in my program with hundreds of thousands of records.
I ran into an issue building a long string for setfilter based off of one of the main table's primary key.
Iterating over about 1,236 rows creating this filter takes roughly 2.2 seconds.

I'd like to be able to do this for at least 20,000 rows in a fraction of the time < 1s.
Is there something I may be missing?

I appreciate the help.

    filtertwo.append("'" + film->record(0).value("FilmID").toString() + "'");
    for (int i=1; i < film->returnView()->model()->rowCount(); i++) {
        filtertwo.append(",'" + film->record(i).value("FilmID").toString() + "'");
        if(i%100==0)
            QApplication::processEvents();
    }   filtertwo.append(")");

JonB

@JahJerMar
For the purposes of timing, please remove the QApplication::processEvents(); and confirm what the time is then?

JahJerMar

@JonB
1.8s
Better but not in the realm I would like.

I'm thinking the problem is because the .toString() is called every single time but... I'm not
sure if there is a clever way of being able to just convert it all to a QString at the end.

JonB

@JahJerMar
I don't have your answer. But there's a lot you can do to find where the time is/is not. Check how long it takes to append 1,236 times with just a fixed string, no record iteration, to eliminate this being a QString concat issue, Take the lookup of column "FilmID" by name outside the loop. Also the film->returnView()->model()->rowCount(). See how long it takes to just access film->record(i), etc. I do think there's something "fishy" if it's taking 2 seconds to do something 1,200 times....

JahJerMar

@JonB
Yeah I realized I accidentally wrote that and fixed it.
I took your advice and found out that...
The real issue is accessing the records from the model actually takes the time... that record.value method is killer.
It's not the QString append or even the + operators concat of the commas and apostrophes.
I'm gonna dig but I have no idea.

JonB

@JahJerMar
Sounds more like it. Is it .record(i) or .value(j) (you should have replaced "FilmID" with a number by now) that you're saying costs?

jsulm

@JahJerMar Take a look at https://wiki.qt.io/Using_QString_Effectively

JonB

@jsulm
Hi @jsulm. By now the OP has discovered:

The real issue is accessing the records from the model actually takes the time... that record.value method is killer.
It's not the QString append or even the + operators concat of the commas and apostrophes.

His stuff is just too slow to be a strings issue!

JahJerMar

@JonB
Yeah it's truly a performance issue with the database. I'm about to test to see if accessing the values from a query might be quicker. I'll post with results when I have the time. Thanks for the help JonB :)

JonB

@JahJerMar
Although I believe it should not be the case that with a QSqlTableModel, which I presumed would actually do all its data record reading from beginning to end when it executes its query, I'm just wondering whether it's possible that what's going on here is that it's still reading the records during your loop, the only obvious thing which would make it so slow.

Do you/me a quick favour, each of these separately:

Paste your whole loop a second time immediately after the first loop. Does it take twice as long or just the same time as one loop? The first loop will have ensured (presumably) that all reading which might have been delayed is done, if that's an issue the second loop should be much faster.
Do the loop rowCount() times, but make it only access some single record, like record(10) instead of record(i). Is that any faster?

mchinand

I'm not sure if this would improve the performance in your case, but you could try setting QSqlQuery::setForwardOnly to true on your query (before executing the query and if you only need iterating forward on your query results).

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

JonB

@mchinand
I am aware of that one. But when you use a QSqlTableModel you do not iterate your results, do you? You don't even access/visit them. There is no next()ing etc. in your usage. Behind the scenes it has just put them all into its model's rows, has it not?

This is exactly what we are trying to establish, and I for one would like to know if you know better than that about how it works?

JonB

@JahJerMar
I'm getting interested/worried now that this is possibly/getting more likely turning into a "QSqlTableModel only fetches the records as it goes along" issue :)

This guy https://stackoverflow.com/questions/45193416/qsqltablemodel-fetchmore-wildly-inefficient is reporting your kind of behaviour, though might be a red herring. BTW, you never said which actual database/driver you've got here, and it could be relevant if it's bad?

I'm dying to hear the results if you quickly try the two code changes I suggested earlier... :)

Meanwhile I shall dig into this myself probably Tuesday and report back. It could affect my own performance if QSqlTableModel doesn't auto-read all its rows from the query when it issues it.

EDIT Putting that stackoverflow link together with another one I found, https://stackoverflow.com/questions/10760455/disable-qsqlrelationaltablemodels-prefetch-caching-behaviour [July 2017]

For some (well, performance) reason, Qt's "model" classes only fetch 256 rows from the database ...

(Might depend on driver, e.g. SQLite??) If that's how it works then basically while you're doing your loop it's actually still going to fetch from the database resultset as it goes along, no wonder it's slow. That might be good or bad news for you: it wouldn't be the time building the string that's slow, it would be that you're still timing fetching results. You could change behaviour by using QSqlQuery, but it might take just as long if that's just how long it takes to fetch the records.

Now doubtless I'm actually going to have to play with this tomorrow... :(

mchinand

Something else that I think would accomplish what you are trying to do is to perform another query that returns your primary key column as a single row/record. I think that's possible with minimal SQL magic (and hopefully fast). This is a hack, it would be preferred to get better performance from your current approach.

Christian Ehrlicher

@JahJerMar said in QSqlTableModel record performance with QString concat:

film->record(i)

What does this function do? Can you please show the code for this function?

JonB

@Christian-Ehrlicher
That's not his code, it's http://doc.qt.io/qt-5/qsqltablemodel.html#record-1.

JahJerMar

@JonB
First, the computer I was experimenting on was a work computer. Windows environment. Just a 2nd gen i5 with 8 gigs of ram. I'm
here I'm actually at my computer now. Unfortunately I can't get the same speed results... I have my solid states and an overclocked 6th gen i5 with my GTX1070.
Both are running ms sql backend with QODBC driver.

I marked the problem as solved because I figured out a much better way to solve my problem.
Accessing the records takes way too much time. I tried sibling...record.. any function and they all can't performance the way I'd like it to.
I measured the speed of the fetching of 256 rows and honestly, it's EXTREMELY quick ( < 5ms ) even on the slow comp...

It's interesting to note that the last fetch 5/5 takes significantly longer than the rest. The pic is my home computer which goes from ~ 1ms to 15ms
and at work it goes from 5ms tops to about 130ms with those specs... that is not the problem. The performance problem was the calls to sibling/record.

Anyways, all I wanted to do was populate my view based on the model. I basically just put another foreign key in one of my tables and ran a simple setfilter and
it basically solved my initial problem by a speed of 100x. Loading things with setfilter is extremely quick to populate the model...

I'm really new to all this... I don't know how to properly use qt creator debugging tools.
I'm also sorry if I'm not more technical!!
I'm extremely new to C++ & Qt... I don't know much. I haven't been programming for very long ( < 6 months )
and this is my biggest project to date.

As far as your tests @JonB ..I did the first test you mentioned before you even asked to see if that was it. The result was it produced the same speed both times if I remember correctly. The 2nd test I'll have to do when I get back to work Monday on the proper hardware just for the fun of it.

Anyways any thoughts or concerns let me know!

Thanks all.

JahJerMar

@mchinand
That's the route I may end up going for other problems. I found out that putting a foreign key in the child table and calling setfilter fits my needs without having to build a string seems so much easier.