[ILUG] MySQL DBI utility functions

[Fergal Daly]

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.


[ @$row ] creates an anonymous ref to an array that has the same entries as
@$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 @rows, $row while($row = $sth->fetch);

filling @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, @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 @picnums, $ID }
> 
> }
> $stop = new Benchmark;
> $sth->finish;
> $dbh->disconnect;
> $elapsed = timediff($stop, $start);
> print "fetchrow_arrayref :",timestr($elapsed),"\n";
> 
> undef @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 @rows, [ @$row ] while($row = $sth->fetch);
> 
> What's that doing, specifically the [ @$row ] part ? Why not just have 
> 
> push @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 linux.ie
> http://www.linux.ie/mailman/listinfo/ilug for (un)subscription information.
> List maintainer: listmaster at linux.ie

--