QSqlTableModel slow even with manual submit

[pherthyl]

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

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

To copy to clipboard, switch view to plain text mode 

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.OnManualSubmit)
			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()

songsModel =QSqlTableModel()
			songsModel.setTable("songs")
			songsModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
			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()

To copy to clipboard, switch view to plain text mode 

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]

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]

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.