QSqlQuery using hundreds of MB of memory

Question

I'm using a QSqlQuery object to do a linear walk of a 10million row SQL table with a very complex 6 column primary key in sorted order. Because of the key(which I CAN NOT change), breaking upmy query SELECT * FROM table1 with < or > with LIMIT causes a huge number of issues with the algorithm I'm using.

My problem arising is as follows, for whatever reason QSqlQuery seems to be caching the entire result set in memory until it hits a bad alloc and kills the application. So I may read some couple hundred rows, seek() over a couple hundred thousand, and by this point QSqlQuery is using 300mb of memory and my application dies. I read the docs and it seems the only thing that can be done is to use setForwardOnly(), however I often have a need for previous()(which is why breaking up the query with LIMIT is a PITA)

Is there no way to cap the cache for QSqlQuery?

MSalters · Accepted Answer · 2012-08-21 14:44:19Z

1

Why don't you store previous yourself? There's a QContiguousCache which seems ideal for this.

answered Aug 21, 2012 at 14:44

MSalters

182k11 gold badges171 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

John Lotacs Over a year ago

Cause the algorithm could potentially call previous() as many times as there are items in the table.

John Lotacs Over a year ago

To elaborate: these rows are being drawn into a very large graph/diagram depicting events in the table and the application has to draw this.

MSalters Over a year ago

Well, that's where the LIMIT clause comes back then. You cache N results in QContiguousCache, and use LIMIT X*N, (X+1)*N. But if you're essentially doing a random walk through the result set, you should go back to the drawing board.

John Lotacs Over a year ago

The randomness comes from the user, I can't make them scroll in one direction in order. LIMIT is useless because I'm using a sqlite database, LIMIT doesn't speed up queries.... doing LIMIT 9000000, 9000001 takes something like 9 seconds. edit: sorry, it takes 39 seconds

MSalters Over a year ago

@JohnLotacs: back to the drawing board, then. QSqlQuery has no reasonable alternative but to store the first 9 million rows in that example.

|

Collectives™ on Stack Overflow

QSqlQuery using hundreds of MB of memory

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related