Recipe 14.12 Repeating Queries Efficiently

14.12.1 Problem

You have a query that you want to execute repeatedly, and you'd like to do it as efficiently as possible. Sometimes you have several queries that are similar, but not quite identical, that you'd like to execute efficiently (for example, you have a loop through an array of names and want to SELECT ... WHERE name=$name).

14.12.2 Solution

Take advantage of the fact that you can repeatedly execute a query that you need prepare only once.

$sth = $dbh->prepare($SQL);
# execute query ten times
for ($i=0; $i < 10; $i++) {
  $sth->execute( );
  while (@row = $sth->fetchrow_array) {
    # ...
  }
}

If you have changing parameters, use the DBI's binding features:

$sth = $dbh->prepare('SELECT uid,login FROM People WHERE name = ?');
foreach $person (@names) {
  $sth->execute($person);
  while (@row = $sth->fetchrow_array) {
    # ...
  }
}

14.12.3 Discussion

"Prepare once, execute often" is one secret to DBI success. By separating preparation from execution, the database server can parse and optimize queries once and then execute them many times. Most databases can do this even when the queries contain placeholders for values to be filled when the query is executed.

The process of replacing placeholders with actual values is known as binding. The simplest way is to bind when you execute:

$sth = $dbh->prepare('SELECT id,login FROM People WHERE middle_initial = ?');
$sth->execute('J');

If you have multiple parameters to bind, pass more values to execute:

$sth = $dbh->prepare('SELECT * FROM Addresses WHERE House = ?
                      AND Street LIKE ?');
$sth->execute('221b', 'Baker%');

You don't have to do the binding and the execution in one step. The bind_param function binds without executing:

$sth = $dbh->prepare('SELECT id,login FROM People WHERE middle_initial = ?');
$sth->bind_param(1, 'J');
$sth->execute( );

The first argument to bind_param is the placeholder number (starting from 1) in the statement:

$sth = $dbh->prepare('SELECT * FROM Addresses WHERE House = ?
                      AND Street LIKE ?');
$sth->bind_param(1, '221b');
$sth->bind_param(2, 'Baker');

You can give an optional third argument to bind_param that identifies the data type of the value and thus whether to quote it:

$sth->bind_param(1, 'J', SQL_CHAR);

If you want to use this type argument, you must import the types explicitly or import all of them:

use DBI qw(SQL_CHAR SQL_INTEGER);
use DBI qw(:sql_types);

List all types with:

foreach (@{ $dbi::EXPORT_TAGS{sql_types} }) {
  printf "%s=%d\n", $_, &{"DBI::$_"};
}

You do not need to quote the values you pass to bind or to a binding execute. DBI automatically quotes them if they are to be used as strings.

The major limitation to binding is that you often can't use placeholders for table or column names. That is, you can't prepare this query:

SELECT ?,? FROM ? WHERE ? = ?

Remember, the goal of separate prepare and execute is to let the database server optimize the query. There's precious little information to do any optimization on in that query!

A smaller limitation is that each placeholder can represent only a single scalar value. Consider this query:

SELECT id,login FROM People WHERE name IN (?)

You can prepare this query without a problem, but you can't bind more than one value to the placeholder.

14.12.4 See Also

The documentation with the DBI module from CPAN; http://dbi.perl.org; Programming the Perl DBI