-- Alexander Foken <[EMAIL PROTECTED]>
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
[EMAIL PROTECTED] 1 888 359 3508
