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/
_______________________________________________
PyQt mailing list PyQt@riverbankcomputing.com
http://www.riverbankcomputing.com/mailman/listinfo/pyqt
