[ILUG] MySQL DBI utility functions

[Niall O Broin]

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