Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

benchmarking DBI bind columns vs. fetchrow_arrayref

by coolmichael (Deacon)
on Mar 20, 2001 at 13:38 UTC ( #65655=perlquestion: print w/replies, xml ) Need Help??
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();

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:

                 $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

    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

(ar0n) Re: benchmarking DBI bind columns vs. fetchrow_arrayref
by ar0n (Priest) on Mar 20, 2001 at 20:09 UTC
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, has a recent article about various tricks to make it easier to use DBI.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://65655]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2017-05-27 01:00 GMT
Find Nodes?
    Voting Booth?