From: Fergal Daly (fergal at domain esatclear.ie)
Date: Thu 13 Sep 2001 - 11:48:15 IST
If you moved the prepare to outside the first loop it might make a
difference, the whole point of a prepare is that the DB only has to parse
the statement once and you can reuse it without reparsing. I think this may
not work very well for MySQL but even still moving it outside might help.
It's possible that you'd see a bigger difference with Oracle where the
prepares can have much bigger effects, especially with complex queries.
[ at domain $row ] creates an anonymous ref to an array that has the same entries as
at domain $row. Basically it makes a copy of the array. Possibly the array pointed to
by $row is permanently associated with that prepared statement and is used
to pass values from C code to perl code. That would mean it gets altered
every time a new row is read from the DB and fetch always returns the same
ref. You could end up with
push at domain rows, $row while($row = $sth->fetch);
filling at domain rows with n references to the same array
Fergal
On Thu, Sep 13, 2001 at 11:16:48AM +0100, Niall O Broin wrote:
> I know that many of you use Perl with MySQL and I'm wondering if anyone
> would care to comment on this code
>
> #!/usr/bin/perl
> #
> use DBI;
> use Benchmark;
> use strict "vars";
>
> my ($ID, $table, $i, at domain picnums, $dbh, $sth, $start, $stop, $elapsed);
>
> $dbh = DBI->connect( 'DBI:mysql:Database', 'MySQLuser', 'MySQLpassword');
> $start = new Benchmark;
> foreach (0..100) {
> $sth = $dbh->prepare("select ID from MyTable where ID < 1000");
> $sth->execute;
> $sth->bind_columns(undef, \$ID);
> while($sth->fetchrow_arrayref) { push at domain picnums, $ID }
>
> }
> $stop = new Benchmark;
> $sth->finish;
> $dbh->disconnect;
> $elapsed = timediff($stop, $start);
> print "fetchrow_arrayref :",timestr($elapsed),"\n";
>
> undef at domain picnums;
>
> $dbh = DBI->connect( 'ArchiveDB', 'MySQLuser', 'MySQLpassword');
> $start = new Benchmark;
> foreach (0..100) {
> $table = $dbh->selectall_arrayref("select ID from MyTable where ID < 1000");
> }
> $stop = new Benchmark;
> $dbh->disconnect;
> $elapsed = timediff($stop, $start);
> print "fetchall_arrayref :",timestr($elapsed),"\n";
>
> and the result of running it a couple of times
>
> fetchrow_arrayref : 2 wallclock secs ( 1.06 usr + 0.24 sys = 1.30 CPU)
> fetchall_arrayref : 3 wallclock secs ( 1.69 usr + 0.21 sys = 1.90 CPU)
> fetchrow_arrayref : 2 wallclock secs ( 1.12 usr + 0.24 sys = 1.36 CPU)
> fetchall_arrayref : 2 wallclock secs ( 1.63 usr + 0.22 sys = 1.85 CPU)
>
> (I ran it lots more than that, and everything was as cached as it was going
> to be at that point, and all runs gave about the same results.) The above is
> extracted from a slightly larger test program I cobbled together - I made it
> as small as possible for this email, so don't bitch if you see some silly
> error which makes it not work.
>
> What puzzles me is that using what the DBI documentation refers to as a
> "utility function" i.e. selectall_arrayref is much slower than using
> fetchrow_arrayref in a loop i.e. rolling your own "selectall_arrayref".
>
> BTW I know that selectcol_arrayref would be more appropriate for fetching
> one column, but the real program fetched more than one column. Anyway, I
> tested with selectcol_arrayref and there was no difference in runtime.
>
> I've had a look at the DBI code, which says that selectall_arrayref calls
> fetchall_arrayref which calls fetchrow_arrayref which all seems perfectly
> logical, so why is it slower than a D.I.Y. method ? One little thing puzzles
> me in the code - there is a line
>
> push at domain rows, [ at domain $row ] while($row = $sth->fetch);
>
> What's that doing, specifically the [ at domain $row ] part ? Why not just have
>
> push at domain rows, $row while($row = $sth->fetch);
>
> (fetch is a required alias for fetchrowarrayref BTW - that got me for a while)
>
> Versions are MySQL 3.23.32, DBI 1.13, DBD 1.2215, Perl 5.6.0, not that I
> think any of that matters a whole lot.
>
>
> There now - chew on that with your coffee :-)
>
>
>
> Niall
>
> --
> Irish Linux Users' Group: ilug at domain linux.ie
> http://www.linux.ie/mailman/listinfo/ilug for (un)subscription information.
> List maintainer: listmaster at domain linux.ie
--
This archive was generated by hypermail 2.1.6 : Thu 06 Feb 2003 - 13:12:07 GMT