[PyQt] Extracting QSqlQuery results in PyQt4

[Aaron Digulla]

mir amicitas schrieb:
> I am trying to use SQLite in a PyQt4 application. I am running into a
> problem where it is taking a really long time to get the data out of
> the QSqlQuery object after a query is completed. Here is a code
> snippet with the relevant parts:
> 
> #################################################################
>         self.selected_columns = self.table_definition['track']
>         select_string = ','.join(self.selected_columns)
> 
>         query_str = 'SELECT %s FROM track %s'%(select_string, where_string)
> 
>         start_time = time.clock()
>         self.query.prepare(query_str)
>         self.query.exec_()
>         util.message('Query took:', time.clock() - start_time, 'seconds')
> 
>         print ''
>         print query_str
> 
>         results = []
>         header = self.selected_columns
> 
>         self.query.first()
> 
>         while self.query.isValid():
>             record = [self.query.value(index).toString() for index in
> range(len(header))]
>             results.append(record)
>             self.query.next()
> 
>         util.message('Selected', len(results), 'records.')
> 
>         util.message('Unpacking Took:', time.clock() - start_time, 'seconds')
> 
> #################################################################
> 
> While the query is quick, extracting the data takes a really long
> time. Way longer than I think it should take.
> 
> Here are the results:
> #################################################################
> Query took: 0.00094797466213 seconds
> 
> SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
> Selected 16668 records.
> Unpacking Took: 5.29902267251 seconds
> #################################################################
> 
> #################################################################
> Query took: 0.0317866355896 seconds
> 
> SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM
> track WHERE (album LIKE '%it%' OR title LIKE '%it%' OR artist LIKE
> '%it%')
> Selected 3262 records.
> Unpacking Took: 1.4495204451 seconds
> #################################################################
> 
> I also to tried just cycling through the code instead of actually
> extracting the data:
> 
> #################################################################
> while self.query.next():
>         pass
> #################################################################
> 
> Results:
> #################################################################
> Query took: 0.171307952858 seconds
> 
> SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
> Selected 0 records.
> Unpacking Took: 1.24349849063 seconds
> #################################################################
> 
> This is certainly faster, but still this is a long time to just to
> cycle though the records.
> 
> If I use a simplified extraction:
> #################################################################
> while self.query.next():
>             record = [self.query.value(index) for index in range(len(header))]
>             results.append(record)
> #################################################################
> 
> Results:
> #################################################################
> Query took: 0.00130084772843 seconds
> 
> SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
> Selected 16667 records.
> Unpacking Took: 3.68243214225 seconds
> #################################################################
> 
> I also tried doing the loop (including string conversion) with out
> appending the results. That took 4.5 seconds.
> 
> What this means is that the time break down is:
> 1.2 seconds just to loop through the records.
> 1.7 seconds to extract the data from the records.
> 0.8 seconds to convert to a string.
> 0.7 seconds to create the python list.
> 
> While I probably can't reduce the time it takes to create the python
> list much, and I can avoid the string conversion, is there anyway to
> reduce the looping and extracting?
> 
> I have tried using setForwardOnly() but I did not notice a difference.
> 
> Does anyone have any idea on why this is taking so long or how I can
> improve things?

It seems that loading 10'000 records out of the sqlite DB just takes a
couple of seconds. sqlite isn't very fast when you have a lot of data
(and yeah, 10'000 isn't that much ...).

You must find a way to split the operation into smaller chunks. Also try
the python sqlite3 module to get a feeling if Python is slow or if it's
Qt or PyQt.

In my app, I'm running sqlite operations in a background thread so they
won't block the user from working. It takes a bit of effort but it was
much more simple than I expected.

Regards,

-- 
Aaron "Optimizer" Digulla a.k.a. Philmann Dark
"It's not the universe that's limited, it's our imagination.
Follow me and I'll show you something beyond the limits."
http://darkviews.blogspot.com/          http://www.pdark.de/