Perl Cd Bookshelf [Electronic resources] نسخه متنی

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

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) {
# ...
}
}

"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);