Perl DBI – committing every nth row

When using the Perl DBI and inserting or updating a large amount of records, it is wise to commit every so ofter as to avoid filling up the database transaction logs. In order to do this, the first thing you need to do is to ensure your db handle has auto-commit turned off. After connecting with, let’s say a db handle of $dbh, add the following line:

$dbh->{AutoCommit}=0;

Then, set a counter before you loop through whatever action you’re performing.

$commit_counter=0;

For example, if you have a while loop while looping through a set of records, or through a file handle, set $commit_counter to 0 before the while loop starts. Then, right before the end of your loop, increment the counter:

$commit_counter++;

Right after incrementing the counter, check to see if it is time to commit:

#Commit every 100 rows
if($commit_counter==100)
{
     $dbh->commit;
     $commit_counter=0;
}

In this example, you would be committing every 100 rows. Notice that $commit_counter is reset to 0 after issuing the commit. It’s that simple.