On 26-Jul-2006 mark wrote: > 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
Mark, Thanks for that. It is nice to at least know that other people have had the same experience. When running my script to Oracle (over a fast oracle connection) and doing 74 unique inserts/updates/selects and 26187 calls to prepare_cached I get no decernable speed up using prepare_cached, execute, fetch* compared with select*. As you say, with other databases and slower networks it may make a bigger difference. Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com > > 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 >>>
