You generate a big number of open statement handles, each costing some
memory. Put the SQL statements (*NOT* the statement handles) into a
hash, like this:
use DBI;
my %statements=(
'find_users' => 'select foo,bar from users where baz=?',
'find_documents' => 'select * from documents where id=? and type=?',
'increment_counter' => 'update counter set n=n+',
);
## some lines later
my $sth=$dbh->prepare($statements{'find_users'});
$sth->execute(42);
while (my $record=$sth->fetchrow_arrayref()) {
# ...
}
$sth->finish();
# somewhere else
$dbh->do($statements{'increment_counter'});
Personally, I don't like this style very much, as I would always have to
lookup somewhere what actually happens.
See also http://www.perl.com/pub/a/2002/10/22/phrasebook.html
Alexander
Richard J McWaters wrote:
I was trying to improve the performance of a Perl DBI query. In the
process I was also trying to improve the look and maintenance of my code.
I am doing a batch job where I am doing multiple queries based on an input
file. The old code had the prepare statement inside the "read file" loop.
A sample would look like:
use DBI qw(:sql_types);
while ($name = <INFILE>>)
{
$sth = $dbh->prepare("
SELECT TAB_NAME FROM TABLE1 WHERE TAB_IN = ?
" );
$sth->bind_param( 1, $name);
$sth->execute();
while ( @out1 = $sth->fetchrow_array )
{
print "$out1[1]\n";
}
}
I can't send a sample of the actual code, but this is a "summary" of what
I am trying to do.
I thought it would be nice to collect all the prepare statments together
and put them in a "documented block" at the start of the program just
after the use DBI line. So the above code would now look like:
use DBI qw(:sql_types);
$sth = $dbh->prepare("
SELECT TAB_NAME FROM TABLE1 WHERE TAB_IN = ?
" );
$sth->bind_param( 1, $name);
while ($name = <INFILE>>)
{
$sth->execute();
while ( @out1 = $sth->fetchrow_array )
{
print "$out1[1]\n";
}
}
Not only would this allow me to have an easier to maintain group of
prepare statements, I also would be doing the prepare only once in the
program instead of for each line of input file. To my surprise, this
slowed down my program instead of speeding it up. Does anyone know what
is going on?
--
Alexander Foken
mailto:[EMAIL PROTECTED] http://www.foken.de/alexander/
