Disable autocommit during hundreds of MySQL UPDATE statements in Django program

Question

In a Django program, how to explicitly disable auto transaction management before hundreds of UPDATEs and enable it after the UPDATEs finish?

I looked into http://docs.djangoproject.com/en/dev/topics/db/transactions/ but didn't find any clue.

I tried to put the following code at the beginning

settings.DISABLE_TRANSACTION_MANAGEMENT = True

I also tried

cursor = connection.cursor()
cursor.execute('SET SESSION autocommit = 0;')
...
UPDATE
...
cursor.execute('SET SESSION autocommit = 1;')

Neither methods above improved the updating speed. Is there anything wrong with above codes?

Answer 1

If you're only doing hundreds of updates and not tens of thousands, maybe the speed issue isn't due to writing the data, but due to finding it rather. i.e. perhaps there's a where clause in the update statement and it takes a while to find the correct row to update. If that's the case then turning off autocommit wouldn't help - you'd need an index on the field in your where clause.

How many rows are in the table? What does the update statement look like?

You could also try prepared statements, but for under a thousand updates it shouldn't make much of a difference.