Performance issue

--=_alternative 00488DA685257195_=
Content-Type: text/plain; charset="US-ASCII"

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 = >)
{
$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 = >)
{
$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?
--=_alternative 00488DA685257195_=--

Re: Performance issue

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 = >)
> {
> $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 = >)
> {
> $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:alexander@foken.de http://www.foken.de/alexander/

Re: Performance issue

-- Alexander Foken

> 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+',
> );

The original posting doesn't say anything about
whether the statements are used more than once.

If they are all one-time use then preparing them
in advance will be an expensive waste. If they
are re-used then there might be some gain to
keeping the prepared handles around.

Thing is that in situations like this you
frequently do not use all of the statements in
each execution.

If so then you might get some improvement by
caching the statements that actually get used
via "prepare_cached":

my $sth = $dbh->prepare_cached( $statemetnz{ $name } );

This avoids inundating the server with unnecessary
statement handle creation without adding overhead to
re-prepare the statements.

You can use two hashes, one with prepared statements
the other with one-time use if the balance isn't
skewed heavily one way or the other:

my %reuse =
(
foo => 'select ...',
...
);

my %onetime =
(
bar => 'select ...',
...
);



while( my $line = <$infile> )
{
my( $name, @argz ) = split; # whatever...

# assuming the caller does an eval.

my $sth
= $reuse{ $name }
? $dbh->prepare_cached( $reuse{ $name } )
: $dbh->prepare( $onetime{ $name } )
or die "Bogus query: unknown '$name'";

$sth->execute( @argz );
...
}

this'll keep the cached ones around for you while
still allowing the server to quickly give up resources
for one-shot queries.

--
Steven Lembark 85-09 90th Street
Workhorse Computing Woodhaven, NY 11421
lembark@wrkhors.com 1 888 359 3508