Programming the Perl DBI

Performance

Without using placeholders, the insert statement shown previously would have to contain the literal values to be inserted and would have to be re-prepared and re-executed for each row. With placeholders, the insert statement needs to be prepared only once. The bind values for each row can be given to the execute method each time it’s called. By avoiding the need to re-prepare the statement for each row, the application typically runs many times faster.

Here’s an example:

my $sth = $dbh->prepare(q{
  INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
}) || die $dbh->errstr;
while (<>) {
    chop;
    my ($product_code, $qty, $price) = split /,/;
    $sth->execute($product_code, $qty, $price) || die $dbh->errstr;
}
$dbh->commit || die $dbh->errstr;

See execute and bind_ param for more details.

The q{...} style quoting used in this example avoids clashing with quotes that may be used in the SQL statement. Use the double-quote like the qq{...} operator if you want to interpolate variables into the string. See the section on "Quote and Quote-Like Operators” in the perlop manpage for more details.

See also the bind_column method, which is used to associate Perl variables with the output columns of a SELECT statement.