benchmarking DBI bind columns vs. fetchrow

coolmichael has asked for the wisdom of the Perl Monks concerning the following question:

I was wondering about wether binding the columns was faster than fetchrow_arrayref() with DBI. The problem is, how do I execute the sql statement outside the functions I'm benchmarking? I can't seem to figure it out. As it is, bindcolumns() seems to be winning, but i think the $sth->execute() is the bottle neck. Any suggestions?

#!/usr/bin/perl -w
use strict;

use DBI;
use Benchmark;

my $dbh=DBI->connect("DBI:CSV:f_dir=c:\\\\pdox") 
    || die "opening connection: $DBI::errstr; stopped\n";
my $sql='SELECT * FROM exp';
my $sth=$dbh->prepare($sql)
    || die "preparing $sql: $DBI::errstr stopped\n";

sub by_bind
{
    my @res=(0 .. 5);

    $sth->execute() 
        || die "executing $sql: $DBI::errstr stopped\n";
    $sth->bind_columns(\$res[0], \$res[1], \$res[2], \$res[3], \$res[4
+], \$res[5]); 

    while($sth->fetch()){}    
}

sub by_arrayref
{
    my @result = (0 .. 5);

    $sth->execute() 
        || die "executing $sql: $DBI::errstr stopped\n";
    while($sth->fetchrow_arrayref(\@result)){}
}

timethese(100, {'bind' => \&by_bind, 'arrayref' => \&by_arrayref});

$dbh->disconnect();
[download]

Comment on benchmarking DBI bind columns vs. fetchrow_arrayref Select or Download Code

Replies are listed 'Best First'.
Re: benchmarking DBI bind columns vs. fetchrow_arrayref by arturo (Vicar) on Mar 20, 2001 at 19:33 UTC
`bind_columns` and `fetchrow_arrayref` do different things. You can, in fact, combine them, as it says right there in the DBI documentation: `fetchrow_arrayref' $ary_ref = $sth->fetchrow_arrayref; $ary_ref = $sth->fetch; # alias Fetches the next row of data and returns a reference to an array holding the field values. Null fields are returned as `undef' values in the array. This is the fastest way to fetch data, particularly if used with `$sth-'>`bind_columns'. `fetchall_arrayref` determines how much data is fetched, and `bind_columns` affects how what is fetched ends up, so to speak. I'm not sure what you mean by `execute` being 'the bottleneck', exactly; sure it might be where the lion's share of the processing time gets spent, but notice that you're using `DBD::CSV`, so the engine that's hiding all the complexity of CSV from you is working hard when you call that (it's turning CSV separated data into table rows). You'd see very different results if you used a DBMS like MySQL or Informix (etc.) here. DBI just gives you a consistent interface to different data sources; what's going on under the hood with various data sources is going to be, in general, well, different. Philosophy can be made out of anything. Or less -- Jerry A. Fodor	[reply] [d/l] [select]
(ar0n) Re: benchmarking DBI bind columns vs. fetchrow_arrayref by ar0n (Priest) on Mar 20, 2001 at 20:09 UTC
See 'DBD oddities' talk at Amsterdam.pm by kudra. It shows that `bind_columns()` is indeed the faster. [ar0n]	[reply] [d/l]
Re: benchmarking DBI bind columns vs. fetchrow_arrayref by chromatic (Archbishop) on Mar 21, 2001 at 23:55 UTC
If I were you, I'd modify the arrayref sub and not throw away the return value. It's hard to say what kind of optimizing Perl does with fetchrow_arrayref() in void context: `while (my $arr_ref = $sth->fetchrow_arrayref(\@result)) {}` For what it's worth, www.perl.com has a recent article about various tricks to make it easier to use DBI.	[reply] [d/l]