Re: to prepare_cached or not?

On 6/16/07, Joshua N Pritikin <[email protected]> wrote:
> On Sat, Jun 16, 2007 at 02:09:44AM -0400, Rudy Lippan wrote:
> > On Thu, 14 Jun 2007, Greg Sabino Mullane wrote:
> > >No significant memory or computation cost. It's a fairly cheap
> > >optimization.
> > >
> > >It's probably fine to use it all you want.
> >
> > That depends, I can think of some instances were you would not want to use
> > it:
> >
> > for (1..100_000_000) {
> >     eval {
> >         my $sth = $dbh->prepare_cached(q{
> >             INSERT INTO mytable (number) VALUES ($_);
> >         });
> >         $sth->excute();
> >     }; if (my $e = $@) {
> >         die "Hmm there was an error... Did you forget to create mytable?
> >         $e";
> >     }
> > }
>
> That's pretty obvious. At least _I_ wouldn't expect it to be a win in
> such circumstances. I currently have only 51 queries in my application.
>

Some people may not find it obvious though.  The underlying mechanism
is that while $dbh->prepare just returns the $sth,
$dbh->prepare_cached keep an internal hash where the keys are the
statements passed to prepare_cached, and the values are the $sth's,
allowing it to return a previous $sth for a repeated statement.

So long as you have a fixed set of statements that use placeholders
for the variable values, it works great :)

There's also some language in the DBI docs mentioning how to tie the
internal CachedKids hash to things like Tie::Cache::LRU to prevent it
from growing unbounded.

-- Brandon