How to insert multiple rows efficiently using Perl DBI?

Inserting multiple rows efficiently in Perl DBI requires using prepared statements with placeholders and executing them in a loop or transaction. The key is to prepare once and execute multiple times, which avoids re-parsing SQL and significantly improves performance.

Basic Efficient Insert Pattern

The most efficient approach uses prepare() once, then calls execute() for each row within a transaction. Wrapping inserts in a transaction is crucial—it can make batch inserts 10-100x faster by reducing disk I/O operations.

#!/usr/bin/env perl
use strict;
use warnings;
use DBI;

# Create in-memory SQLite database for demonstration
my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:', '', '', {
    RaiseError => 1,
    AutoCommit => 1
});

# Create table
$dbh->do('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');

# Sample data to insert
my @users = (
    ['Alice', 30],
    ['Bob', 25],
    ['Charlie', 35],
    ['Diana', 28],
    ['Eve', 32]
);

# Efficient batch insert using prepared statement
my $start = time;
$dbh->begin_work;  # Start transaction

my $sth = $dbh->prepare('INSERT INTO users (name, age) VALUES (?, ?)');

foreach my $user (@users) {
    $sth->execute(@$user);
}

$dbh->commit;  # Commit all inserts at once
my $elapsed = time - $start;

print "Inserted " . scalar(@users) . " rows in $elapsed seconds\n";

# Verify inserts
my $count = $dbh->selectrow_array('SELECT COUNT(*) FROM users');
print "Total rows in database: $count\n";

# Show inserted data
my $all = $dbh->selectall_arrayref('SELECT name, age FROM users ORDER BY name');
print "\nInserted users:\n";
foreach my $row (@$all) {
    print "  $row->[0], age $row->[1]\n";
}

$sth->finish;
$dbh->disconnect;

Key Performance Techniques

• Prepared Statements: Use prepare() with placeholders (?) to avoid SQL re-parsing for each row
• Transactions: Wrap inserts in begin_work() and commit()—this is the single biggest performance boost
• AutoCommit Off: For large batches, consider setting AutoCommit => 0 in connection attributes
• Batch Commits: For very large datasets (10,000+ rows), commit every 1,000-5,000 rows to balance performance and memory

Common Pitfalls

Without transactions, each insert commits immediately, causing excessive disk writes. Always call $sth->finish() when done to free resources. For error handling, wrap the transaction in an eval block and call $dbh->rollback() on failure to maintain data integrity.