I know nothing about MySQL, but I benchmarked prepare vs prepare_cached
in Oracle many moons ago, and saw no great difference, which is actually
what I expected. In theory, the main savings is avoiding the repeated
cost of the database parsing and prepping the statement, but Oracle
does its own internal statement caching, so its mostly moot. There
might be corner cases where prepare_cached buys you something in
Oracle, but I think by and large it doesn't. For example, it does
save a network round trip presumably, so perhaps over a slow link
or something like that...
Mark
Tim Bunce wrote:
Use DBI::Profile to see where the time is being spent.
Tim.
On Tue, Jul 25, 2006 at 06:35:59PM +0100, Martin J. Evans wrote:
I know this is one of those how long is a piece of string questions but
I cannot see any difference using prepare_cached with a remote mysql or
oracle database. I have code which does inserts/updates/selects - around
20 - 30 different pieces of SQL and most of them are run between a 100
and 1000 times per file I process. When I process around 10 files it
takes around 4 minutes and this drops about 5s when using prepare_cached
(but I thought this was too small so was within the bounds of variance
running the same script multiple times anyway).
Most of the uses are like this:
my $sql = q/select column from table where column2 = ?/;
my $val = selectrow_array($sql, undef, $a_value);
where the select returns one row. I changed this to:
$s = $h->prepare_cached($sql);
$s->execute($a_value);
$val = $s->fetchrow_array;
$s->finish;
There is was specific change to prepare_cached which actually doubles
the time when run to mysql compared with using prepare:
select LAST_INSERT_ID();
Am I doing something wrong or are my expectations wrong. I had thought
using prepare_cached would shave a lot more than 5s in 240s off.
Has anyone got a concrete example of where prepare_cached is
significantly faster? or I am flogging a dead horse?
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
