If you’re using DBI directly for your database access, not through some ORM, then fetchrow_hashref is probably the handiest way to fetch result rows. However, if you’re working on lots of rows and time is critical, know that it is also be the slowest way to do so.

Here’s a benchmark that shows that binding columns with bind_column takes half the runtime of fetchrow_hashref.


use strict;
use warnings;
use 5.010;

use Benchmark ':hireswallclock';

our $ITERATIONS = 1_000_000;
our $sth;

sub prep_handle {
    my $sql = <<"EOF";
    SELECT title, author, isbn
    FROM title
    WHERE ROWNUM < $ITERATIONS
EOF
    return sqldo_handle( $sql );  # Calls DBI->prepare
}

sub hashref {
    while ( my $row = $sth->fetchrow_hashref ) {
        my $title  = $row->{title};
        my $author = $row->{author};
        my $isbn   = $row->{isbn};
    }
    $sth->finish;
}

sub array {
    while ( my @row = $sth->fetchrow_array ) {
        my ($title,$author,$isbn) = @row;
    }
    $sth->finish;
}

sub arrayref {
    while ( my $row = $sth->fetchrow_arrayref ) {
        my $title  = $row->[0];
        my $author = $row->[1];
        my $isbn   = $row->[2];
    }
    $sth->finish;
}

sub direct_bind {
    $sth->bind_columns( \my $title, \my $author, \my $isbn );
    while ( my $row = $sth->fetch ) {
        # No need to copy.
    }
    $sth->finish;
}

say "Running $ITERATIONS iterations";
for my $func ( qw( hashref array arrayref direct_bind ) ) {
    $sth = prep_handle();
    my $t = timeit( 1, "$func()" );
    printf( "%-11s took %s\n", $func, timestr($t) );
}

Which gives these results

$ ./dbi-bind-bench
Running 1000000 iterations
hashref     took 7.37747 wallclock secs ( 4.98 usr +  0.25 sys =  5.23 CPU) @  0.19/s (n=1)
array       took 4.01768 wallclock secs ( 1.68 usr +  0.19 sys =  1.87 CPU) @  0.53/s (n=1)
arrayref    took 3.86365 wallclock secs ( 1.60 usr +  0.16 sys =  1.76 CPU) @  0.57/s (n=1)
direct_bind took 3.36962 wallclock secs ( 1.13 usr +  0.15 sys =  1.28 CPU) @  0.78/s (n=1)

When speed is key, bind your output variables directly.