Extracting QSqlQuery results in PyQt4

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:

Code:

---

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

---

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:

Code:

---

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

---

Code:

---

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 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

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

Code:

---

while self.query.next():
        pass

---

Results:

Code:

---

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 crazy long time to just to cycle though the records.

If I use a simplified extraction:

Code:

---

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

---

Results:

Code:

---

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

-- 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:

Code:

---

results = [row for row in self.cursor]

---

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