$sth->prepare_cached loop

hmadhi has asked for the wisdom of the Perl Monks concerning the following question:

Will the following loop cause separate queries to the database?


my %kpi_thres = (
                "PDP"=>80,
                "GAS"=>70,
                "RAU"=>75 
      );

$SQL=qq{select * from abc where date=?};

foreach $kpi (keys(%kpi_thres)) 
{

            my $sth_ne = $dbh->prepare_cached($SQL);
            $sth_ne->execute($currDateTime);
}
[download]

Comment on $sth->prepare_cached loop Download Code

Replies are listed 'Best First'.
Re: $sth->prepare_cached loop by tilly (Archbishop) on Mar 08, 2011 at 07:17 UTC
What will happen will vary depending on the database. With Oracle, you will prepare the query once. And then send 3 execute statements to it. Which saves you the effort of compiling the query. Since compilation is expensive in Oracle, this is a win. In MySQL the caching is done client side. So you wind up preparing and executing the query 3 times. Since MySQL makes preparing the query cheap, this doesn't cost very much.	[reply]
Re^2: $sth->prepare_cached loop by hmadhi (Acolyte) on Mar 08, 2011 at 08:02 UTC
Thanks for the reply, I am using Oracle.	[reply]