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?
