QSqlQuery performance issue

Kemo Hay

Hi.
I am fairly experienced with Qt but never had to work with QSqlDatabase and QSqlQuery classes, sorry if my question has an obvious answer that I am not seeing yet.
I want to load large data into classic arrays of objects. For simplicity, imagine I want to select from a database (irrelevant what driver to use, either ORACLE or Postgres) 10.000.000 integers into a buffer of type int.
My SqlQuery::exec will perform fairly fast, the problem is in reading the data into an array, as it will need to wrap all 10.000.000 of them into QVariant separately, only for me to unwrap them, something like this:

if (!m_query.exec(qsSql))
	return false;

int buffer[10000000];
int i = 0;
while (m_query.next())
	buffer[i++] = m_query.value(0).toInt();

I would like to know if there is a better way of doing this?
Thank you

Bonnie

That's the design.
If you don't want the results to be wrapped in QVariant, then you must use the database driver's native APIs, not by Qt.

Christian Ehrlicher

Don't read 10mio entries. It will be slow no matter what you use.

Kemo Hay

Thank you for replaying. What do you suggest to do instead? I know this code works fine if you use it only to show information on GUI, You select some 1000 items maybe, populate a Widget, but we have a different file format that the data needs to be converted. I could for example ready by pages of 1k rows, or 4k rows per chunk, but then again, each element in the chunk will need to go through wrapping and unwrapping around QVariant. This is what bothers me the most.

Bonnie

That's the design.
If you don't want the results to be wrapped in QVariant, then you must use the database driver's native APIs, not by Qt.

Kemo Hay

Thank you. Yes, I am probably going to do that, just wanted confirmation, to not reinvent the wheel.
Thank you, I will mark this as an answer

JonB

@Kemo-Hay
IMHO, wrapping in QVariant is possibly the least of your problems. As @Christian-Ehrlicher said above, reading 10 million items into memory is crazy/slow/memory hungry. You're supposed to use a database with that many entries as a database, not an array in memory. You're supposed to (a) filter what you need in the SELECT ... WHERE ... and (b) if that's still large use a paging mechanism to fetch just what you need when you need to display to the user.

Kemo Hay

Hi JohB,
Thank you for your comment. I can agree this is not a common use-case for simple standalone applications. I work at Synopsys, our tools sometimes required to load large data in memory for wafer map visualizations and chip design analysis. Chips are getting smaller day by day, 5nm is as small as 10 carbon atoms, transistors in chips are that small. You can imagine how many defects are reported in the diagnosis of the chip. Our customers run huge servers and we do not lack the hardware for loading data in memory, but still waiting for hours for the tool to populate a wafer map is a bad user experience. There is always a place to improve on performance.
Thank you

JonB

@Kemo-Hay
That is reasonable, I spoke generically and you appear to have good reason to adopt your approach for your situation.

Then you might measure the overhead of the QVariant wrapping compared to not, but as @Bonnie said that requires "use the database driver's native APIs, not by Qt". I just don't know how much of an overhead that imposes compared to the actual reading of the data.

BTW, I think you will find it's a bit unfair to say

My SqlQuery::exec will perform fairly fast, the problem is in reading the data into an array

In your code you only know that you have launched the code and it has begun to result the (large) result stream. I think you will find that during your m_query.next() loop the QSqlQuery is fetching as it goes along from the response stream, which itself is "expensive".

BTW, for your SQL database I think you will find there is a way of improving speed (and memory) for your case. Since here you only reading all the data, did you set QSqlQuery::setForwardOnly(true)? In the cases I tested this did make quite some difference.

ollarch

Hi,

You could use Threads for this. If you have multicore CPU (sure you have) you can split the query into multiple queries and the insertion into your buffer.

SGaist

Hi,

In addition to what @JonB said (and since you did not show your query), also ensure that you are not requesting data that you are not using to build you internal structure. That will also help shave off time.