1

I am running the following Python code (simplified):

 db = mysql.connector.connect(**dbconfig)
 cur = db.cursor()
 for row in dataFrame:
     cur.execute(INSERT [TABLE NAME] column, VALUES ({row}))
     db.commit() # should this be inside or outside the loop?

My question is will I expect a difference in performance based on whether the .commit() statement is inside the loop (runs each iteration) or outside (runs after all .execute statements).

Both ways seem to work fine and I cannot notice any difference in performance for small amounts of data (< 100 rows). Eventually, I will be processing many more rows and so I want to get this right now as I expect its going to make a difference.

thisissparzo
  • 37
  • 1
  • 8
  • check this thread that handles a similar issue https://stackoverflow.com/questions/46543436/speeding-bulk-insert-into-mysql-with-python – nbk Feb 26 '22 at 20:44

1 Answers1

1

It depends.

COMMIT takes some time; more commits = more time taken.

On the other hand, the more things you put into a single transaction, the longer that transaction takes and the more time that it interferes with other processes. And the greater chance of a deadlock.

Mor most situations, group statements together that need to be "atomically" run or fail. That is what transactions are for. If you end up with performance or deadlock problems, then we should look into the details to decide what to do -- on a case by case.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I agree with your sentiment of atomizing the processes but there has to be some point to which it just no longer makes sense to atomize a process. For instance, if commit() carries much overhead then you have to pay for that every time you loop rather than once at the end. Would you agree? – thisissparzo Feb 27 '22 at 05:43
  • @thisissparzo - That's the performance gain. – Rick James Feb 27 '22 at 06:16