PDA

View Full Version : QSqlTableModel slow even with manual submit



pherthyl
15th October 2008, 19:40
I'm trying to wrap my head around model/view classes especially with respect to SQL. So I've got a simple function that scans through a directory, reads ID3 tags, and inserts one row into a table for each song.

Using just QSqlQuery and doing a db.transaction(), db.commit() around the entire scanning (so one giant transaction) works just fine and processes about 500 files/s. It looks something like this:



db.transaction()
it = QDirIterator(self.rootFolder, QDirIterator.Subdirectories);
while it.hasNext():
try:
path = it.next()
if path.endsWith('.mp3', Qt.CaseInsensitive):
items += 1
try:
audio = EasyID3(path)
artist = audio['artist'][0]
title = audio['title'][0]
album = audio['album'][0]

query.prepare('INSERT INTO songs VALUES(:guid, :title, :artist, :album)')
query.bindValue(':guid', QVariant(QUuid.createUuid().toString()))
query.bindValue(':title', QVariant(title))
query.bindValue(':artist', QVariant(artist))
query.bindValue(':album', QVariant(album))
query.exec_()
except ID3NoHeaderError:
noHeaders += 1
except UnicodeDecodeError:
decodeError += 1
except IOError:
notFound += 1
except KeyError:
noTags += 1
except UnicodeEncodeError:
encodeError += 1
db.commit()


Now I want to use QSqlTableModel to do the same thing (I know it doesn't make much sense, just trying to learn this stuff). Then my code looks like this:



songsModel =QSqlTableModel()
songsModel.setTable("songs")
songsModel.setEditStrategy(QSqlTableModel.OnManual Submit)
it = QDirIterator(self.rootFolder, QDirIterator.Subdirectories);
while it.hasNext():
try:
path = it.next()
if path.endsWith('.mp3', Qt.CaseInsensitive):
items += 1
try:
audio = EasyID3(path)
artist = audio['artist'][0]
title = audio['title'][0]
album = audio['album'][0]

row = songsModel.rowCount()
songsModel.insertRow(row)
songsModel.setData(songsModel.index(row, 0), QVariant(QUuid.createUuid().toString()))
songsModel.setData(songsModel.index(row, 1), QVariant(title))
songsModel.setData(songsModel.index(row, 2), QVariant(artist))
songsModel.setData(songsModel.index(row, 3), QVariant(album))
except ID3NoHeaderError:
noHeaders += 1
except UnicodeDecodeError:
decodeError += 1
except IOError:
notFound += 1
except KeyError:
noTags += 1
except UnicodeEncodeError:
encodeError += 1
songsModel.submitAll()


Since I set it to manual submit changes, it should do one giant transaction just as before. However now the scanning performance degrades to about 100 items/s, which is about the speed I get when I don't use transactions in the first case..

So what am I not understanding? Is QSqlTableModel just not meant for mass inserts? Any help would be greatly appreciated.

janus
15th October 2008, 20:44
Hi,

not sure, but I think if you call db.transaction() and commit() around it it might be faster, since submitAll() and commit() afaik is not the same.

pherthyl
15th October 2008, 21:40
Putting db.transaction()/db.commit() around the model stuff does indeed boost the performance to the levels of the QSqlQuery implementation.

However I don't really like that solution, since the model should handle that transaction business, so I removed that again. It's weird that when I leave the default edit strategy, the performance is pretty much the same as when I use ManualSubmit.. Seems there is a bug somewhere here. Obviously even with manualsubmit there are some hard drive writes going on which is destroying performance.