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 >>
