Optimal approach - read-only parsing of QSqlQuery from multiple threads
-
Hi all,
I'm looking for recommendations as to how to best approach the below situation. I connect to a database and execute a SELECT transaction from within a thread. The amount of data returned by the QSqlQuery is substantial. I then have to execute a large number of operations on the returned data, where each operation iterates through the data looking for specific patterns, conditions, etc. At the moment these operations are run one after another on the same thread as the QSqlDatabase connection (and QSqlQuery). This works exactly as expected.
I'm now looking to parallelise this functionality. I'd like to execute the SELECT transaction once, and then execute the large number of operations on the resulting data across a number of threads (via QtConcurrent and QThreadPool). I understand that the QSqlQuery results cannot be parsed concurrently from different threads, so I'm looking at an alternative strategy. At the moment, I copy each row of the QSqlQuery result into a QVector and then clear the QSqlQuery. As QVector is thread-safe for read-only operations, this should be an appropriate solution.
Is this the optimal approach? Is there a better way to go about this?
-
Hi all,
I'm looking for recommendations as to how to best approach the below situation. I connect to a database and execute a SELECT transaction from within a thread. The amount of data returned by the QSqlQuery is substantial. I then have to execute a large number of operations on the returned data, where each operation iterates through the data looking for specific patterns, conditions, etc. At the moment these operations are run one after another on the same thread as the QSqlDatabase connection (and QSqlQuery). This works exactly as expected.
I'm now looking to parallelise this functionality. I'd like to execute the SELECT transaction once, and then execute the large number of operations on the resulting data across a number of threads (via QtConcurrent and QThreadPool). I understand that the QSqlQuery results cannot be parsed concurrently from different threads, so I'm looking at an alternative strategy. At the moment, I copy each row of the QSqlQuery result into a QVector and then clear the QSqlQuery. As QVector is thread-safe for read-only operations, this should be an appropriate solution.
Is this the optimal approach? Is there a better way to go about this?
@jars121
So far as I am aware, your copying out fromQSqlQueryresult set to some other thread-safe container is the only way.A couple of points, just in case you are not aware (you probably are already):
-
My initial thought is that by the time you have read all the data, copied it into a container and set off
QtConcurrents (just how many CPUs do you have available?) you may be no better off than doing it all in one thread (might be its own thread for theQSqlQueryso that UI thread is not blocked). I take it you have actually timed it and found improvement? It probably only makes sense if you have to run through all the rows multiple, multiple times. Which maybe is your case. -
Biggest saving you can make is to do as much as possible up at the database side, writing a complex SQL query if necessary. The more work you do there and the less data you have to send to client the better.
-
If you have not done so already, make sure your
QSqlQueryis set to be forward-only. This can make a huge difference!
-
-
@jars121
So far as I am aware, your copying out fromQSqlQueryresult set to some other thread-safe container is the only way.A couple of points, just in case you are not aware (you probably are already):
-
My initial thought is that by the time you have read all the data, copied it into a container and set off
QtConcurrents (just how many CPUs do you have available?) you may be no better off than doing it all in one thread (might be its own thread for theQSqlQueryso that UI thread is not blocked). I take it you have actually timed it and found improvement? It probably only makes sense if you have to run through all the rows multiple, multiple times. Which maybe is your case. -
Biggest saving you can make is to do as much as possible up at the database side, writing a complex SQL query if necessary. The more work you do there and the less data you have to send to client the better.
-
If you have not done so already, make sure your
QSqlQueryis set to be forward-only. This can make a huge difference!
@JonB Awesome, thank you very much.
In response to your points:
-
I have timed the current single threaded approach, and I'm looking at implementing the multi-threaded approach as a means of benchmarking the two approaches. As it stands, the worst case scenario would require parsing the SQL results hundreds of times, so I expect there will be a crossover point whereby moving from single- to multi-threaded execution makes sense.
-
This is an aspect I haven't really spent much time on as of yet, but I'll certainly look into it.
-
I haven't set the QSqlQuery to forward-only, thank you for the recommendation!
-
