QSqlQueryModel incrementally remove old rows

[jsulm]

@gozag I still don't understand: do you want to remove "old" rows from the database?

[gozag]

@jsulm I want to remove or "unload" them from the view. Not from the database.
Like when you scroll down far enough it fetches new rows, I want to also unload the rows at the top.

[JonB]

@gozag
It's easy to remove the earlier rows from what the view shows. But I'm guessing you mean remove those "old" rows from the model, so that you don't end up with them all in memory at the model level?

[gozag]

@JonB yes

[JonB]

@gozag
I don't know the answer to that yet. Would I would say is start by ensuring you have setForwardOnly(true). That will stop going back in the SQL result set. Check what happens after fetchMore(), but I'm not convinced old rows will be removed.

A different approach, if you cannot convince QSqlQueryModel to discard old rows, is either:

• Many (most?) SQL implementations allow SELECT to be specified with arguments about where to start from and how many to include (e.g. TOP, LIMIT). Use that to do your own filling/refilling.

• Move back to QSqlQuery. QSqlQueryModel will be using this. If QSqlQueryModel does not allow you to free up old rows, implement what you want on top of QSqlQuery yourself, which hands you the rows to do as you will with.

[gozag]

@JonB Setting setForwardOnly(true) makes the model empty. I guess I need to do it manually or ask myself if it is even necessary to scroll down that far.

[SGaist]

Hi,

In that kind of situation you could implement a moving window which would contain a certain number of rows plus some more that allows for some scrolling. If people start scrolling "too fast", then only load data once they stopped.

[JonB]

@SGaist
The OP says they wish to remove the "old" (a long way back) rows from occupying memory at some point (" My database table has millions of entries (gigabytes of data!)."). This is the problem. QSqlQueryModel does not offer access to removing/reducing the rows previously read: you can fill it incrementally via fetchMore() but you cannot remove rows dynamically. I presume wherever it stores them is private. Please correct me if I am wrong.

QSqlQuery can be used instead by the OP to implement their own which does allow disposing of previously read rows.

@gozag
Incidentally, per the recent https://forum.qt.io/topic/144533/database-performance-5-times-faster-with-access-than-with-ms-sql since QSqlQueryModel does not allow a forward-only query you may be paying quite a performance penalty using it for your case over your own QSqlQuery implementation.

[SGaist]

@JonB that's the goal of the moving window, you only keep in memory a certain amount of data. This means a custom model. In this case, you can use a QSqlQuery that will only retrieve the right amount of data and fill your model with it. Then depending how you move get some more data, drop what was before if moving forward and stop what is after if moving backward.

[JonB]

@SGaist
Which is exactly what I said above

Move back to QSqlQuery. QSqlQueryModel will be using this. If QSqlQueryModel does not allow you to free up old rows, implement what you want on top of QSqlQuery yourself, which hands you the rows to do as you will with.

OP will need to move over from QSqlQueryModel to own calls to QSqlQuery :)

[SGaist]

@JonB my bad, I misunderstood your point. We are on the same line :-)