Computer Science & Perl Programming

Chapter 60. DBI Caveats

Thomas Akin

Perl’s DBI module allows you to use Perl with database engines. It’s one of the most popular Perl utilities, and I have dozens of clients who make extensive use of DBI for their daily production needs. Some clients use DBI much more effectively than others, and in this article you’ll see how.

DBI and Loops

DBI statements are often used within loops that store or fetch data from a database. You might insert the contents of a text file into a database line by line, or read and parse multiple lines of output from a database query. I often see code like the following from new DBI programmers:

	foreach $line (<FILE>) {

	    chomp $line;

	    ($alpha, $beta, $charlie, $delta) = split(/,/,$line);

	    $sql = qq{ insert into $table (col_a, col_b, col_c, col_d)

	               values($alpha, $beta, $charlie, $delta) };

	    $dbh = DBI->connect($dsn, 'login', 'password');

	    $sth = $dbh->prepare($sql);

	    $sth->execute;

	    $dbh->disconnect;

	}

This code works, but not very efficiently. With the above code it took an hour to insert 200,000 rows into a MySQL database—and MySQL is pretty fast! It might take half a day with Oracle.

There are lots of improvements we can make. The first is just common sense: never put anything in a loop that doesn’t absolutely have to be there. For each of our 200,000 insertions, we connect to the database, prepare our SQL statement, execute it, and disconnect from the database. However, we only need to connect and disconnect once. We simply have to move our connect and disconnect ...