jeudi 12 février 2015

Improve sqlite query speed

I have a list of numbers (actually, percentages) to update in a database. The query is very simple, I obtain the id of the items somewhere in my code, and then, I update these items in the database, with the list of numbers. See my code:



start_time = datetime.datetime.now()

query = QtSql.QSqlQuery("files.sqlite")
for id_bdd, percentage in zip(list_id, list_percentages):
request = "UPDATE papers SET percentage_match = ? WHERE id = ?"
params = (percentage, id_bdd)

query.prepare(request)

for value in params:
query.addBindValue(value)

query.exec_()

elsapsed_time = datetime.datetime.now() - start_time
print(elsapsed_time.total_seconds())


It takes 1 second to generate list_percentages, and more than 2 minutes to write all the percentages in the database. I use sqlite for the database, and there are about 7000 items in the database. Is it normal that the query takes so much time ? If not, is there a way to optimize it ?


Aucun commentaire:

Enregistrer un commentaire