Extracting QSqlQuery results in PyQt4

[amicitas]

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:

# Begin Snippet
        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')

# Begin Snippet
        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')

To copy to clipboard, switch view to plain text mode 

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.00094797466213 seconds

SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
Selected 16668 records.
Unpacking Took: 5.29902267251 seconds

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

I have tried using setForwardOnly() but it made no difference.

Does anyone have any idea on why this is taking so long or how I can improve things?

-- amicitas

Hystrix Audio

[amicitas]

I decided to try just cycling through the code instead of actually extracting the data:

while self.query.next():
        pass

while self.query.next():
        pass

To copy to clipboard, switch view to plain text mode 

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

Query took: 0.171307952858 seconds

SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
Selected 0 records.
Unpacking Took: 1.24349849063 seconds

To copy to clipboard, switch view to plain text mode 

This is certainly faster, but still this is a crazy 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)

while self.query.next():
            record = [self.query.value(index) for index in range(len(header))]
            results.append(record)

To copy to clipboard, switch view to plain text mode 

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

Query took: 0.00130084772843 seconds

SELECT artist,album,title,url,musicbrainz_trackid,hystrix_trackid FROM track
Selected 16667 records.
Unpacking Took: 3.68243214225 seconds

To copy to clipboard, switch view to plain text mode 

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, is there anyway to reduce the rest of it?

-- amicitas

[amicitas]

After getting some suggestions on the PyQt mailing list I have done a few more tests.

I tried using the python packages sqlite3 and also apsw. Using either of these modules the unpacking time is reduced to 1-2 seconds.

Furthermore just cycling through the results takes 1-1.5 seconds, meaning that adding the results to a list is much quicker than using QSqlQuery ( less than 1 second ).

The query time is basically unaffected.

So it looks like the major problem I was having is in how Qt (or PyQT) handles the query extraction (I suspect that part of it has to do with Qt converting everything into QVariants).

With sqlite3 or apsw things are much more tolerable, though if any one has ideas on how to make the extraction even faster I would love to here them. What I am doing for now is to use:

results = [row for row in self.cursor]

results = [row for row in self.cursor]

To copy to clipboard, switch view to plain text mode 

I think if I want my app to feel fast I need to do the query/extraction in the background and return stuff in chunks. I'll have to see how well the gui handles that sort of thing.

I also need to try using the QSqlQueryModel and see how that works since It will bypass the need for me to get the data into python in the first place.

-- amicitas