On Wed, 12 Sep 2001 13:59:49 -0400 (EDT), [EMAIL PROTECTED] (Rob Ransbottom)
wrote:
> Has anyone tried caching the prepared statements?
Yes; I do that frequently. That's really why there's a difference between
preparing and executing; I usually prepare all the statements I will need at the
beginning of my script (once) and can then execute them repeatedly, as often as
I need. This also saves the database work (if it supports that sort of thing),
since it only has to analyse and translate the statement once.
It may be useful for selects, but is probably more commonly used for inserts and
updates -- and this is where placeholders really shine, since they allow you to
prepare one statement and execute it dozens of times with different parameters
each time, rather than having to prepare dozens of different statements, each of
which the database has to parse and analyse anew.
If I only have a few statements, I usually use separate variables; otherwise, I
frequently store them in a hash -- something like this:
my(%sql, %handle);
$sql{'insertbla'} = <<EOSQL;
insert into bla (foo, bar)
values (?, ?)
EOSQL
# etc, for as many statements as I need
# then, prepare them all by looping through (keys %sql) and doing a
# $dbh->prepare for each string and storing the resulting statement handle
# in $handle{$key}
# Then use the statement:
$handle{'insertbla'}->execute(12, 'hamburger')
or mydie "Error executing insertbla: $DBI::errstr";
> Is this a good idea?
I think so.
> I could easily have 150 prep'd statement handles lying around.
Might that number be reduced if you use placeholders more to make the statements
more generic?
Cheers,
Philip
