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? Thanks in advance, -- amicitas _______________________________________________ PyQt mailing list PyQt@riverbankcomputing.com http://www.riverbankcomputing.com/mailman/listinfo/pyqt