On 25-Jul-2006 Tim Bunce wrote:
> Use DBI::Profile to see where the time is being spent.
I am looking into this.
My first attempt at setting DBI_PROFILE=2 and running my script returned with:
DBI::Profile on_destroy failed: Undefined subroutine &DBI::Profile::time_in_dbi
called at /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBI/Profile.pm
line 641 during global destruction.
Looking at line 640-642:
$prologue .= sprintf "%fs ", $time_in_dbi;
my $perl_time = ($DBI::PERL_ENDING) ? time_in_dbi() - $^T : $t2-$t1;
$prologue .= sprintf "%.2f%% ", $time_in_dbi/$perl_time*100 if
$perl_time;
I cannot find a time_in_dbi sub, only a scalar called time_in_dbi. To make it
work I had to change the above to use $time_in_dbi and not time_in_dbi(). Don't
know if this was correct but thought I ought to mention it.
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
>>
