Re: prepare_cached - just how much quicker should I expect?
- From: martin.evans@xxxxxxxxxxxx (Martin J. Evans)
- Date: Wed, 26 Jul 2006 10:43:34 +0100 (BST)
On 25-Jul-2006 Tim Bunce wrote:
Use DBI::Profile to see where the time is being spent.
I got this working and could not see anything taking longer. However, on
further investigation of my logs I have found the reason why doing a
prepare_cached on "select LAST_INSERT_ID()" to mysql is a BAD thing to do.
It always returns the first id ever inserted. i.e.
insert into table1 values (val)
select LAST_INSERT_ID() returns 1
insert into table1 values (val)
select LAST_INSERT_ID() returns 1 (instead of 2)
etc
I see there is a warning about how you can get into trouble with prepare_cached
in the DBI pod. Perhaps it would be worth noting that there is also some SQL
you don't want to prepare_cached as it won't work properly - like this example.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
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
- Follow-Ups:
- Re: prepare_cached - just how much quicker should I expect?
- From: Tim Bunce
- Re: prepare_cached - just how much quicker should I expect?
- References:
- Re: prepare_cached - just how much quicker should I expect?
- From: Tim Bunce
- Re: prepare_cached - just how much quicker should I expect?
- Prev by Date: Re: MySQL BLOB: random access via DBI ?
- Next by Date: Re: prepare_cached - just how much quicker should I expect?
- Previous by thread: Re: prepare_cached - just how much quicker should I expect?
- Next by thread: Re: prepare_cached - just how much quicker should I expect?
- Index(es):
Relevant Pages
|
|
