See DBI about the Slice option:

my $result = $sth->fetchall_arrayref({});
[download]

The DBI docs also have a written-out example for that, giving you the advantage of speed combined with the ease of hash access, something like:

my %rec;
my $sth = $dbh->prepare ("select region, sales from sales_by_region");
   $sth->execute;
   $sth->bind_columns (\(@rec{@{$sth->{NAME_lc}}}));
while ($sth->fetch) {
    print "$rec{region}: $rec{sales}\n";
    }
[download]

I don't know, bind columns always have a bad smell about them. I prefer the slice alternative where your returned resultset is an array of hashes:

my $rows = $dbh->selectall_arrayref( $sql, { Slice => {} } );
[download]

  foreach my $row ( @$rows ) {
    print $row->{NAME}, "\n";
  }
[download]

Still slower than bind:

{   my $t0 = [ gettimeofday ];
    my $n  = 0;
    my $rows = $dbh->selectall_arrayref ($sql, { Slice => {} });
    foreach my $row (@$rows) {
        $n++;
        }
    printf STDERR "slice: %9.2f recs/sec\n", $n / tv_interval ($t0);
    }

{   my $t0 = [ gettimeofday ];
    my ($n, %rec) = (0);
    my $sth = $dbh->prepare ($sql);
       $sth->execute;
       $sth->bind_columns (\@rec{@{$sth->{NAME_lc}}});
    while ($sth->fetch) {
        $n++;
        }
    printf STDERR "bind:  %9.2f recs/sec\n", $n / tv_interval ($t0);
    }

=>

with postgres on other server:
slice:  23842.81 recs/sec
bind:   27994.01 recs/sec

with postgres on local server:
slice: 214963.65 recs/sec
bind:  505370.38 recs/sec
[download]

YMMV


Enjoy, Have FUN! H.Merijn

Interesting ... YMMV most definitely. Not sure if it's due to a difference in the databases, or the drivers or the data itself ... but using your approach against a 43 col table (mixed data types), I get:

slice:  14021.59 recs/sec
bind:    6750.10 recs/sec
[download]

-derby

Note, my solution is using bind as suggested ...

So $rows is a ref to an Array of Hashes ?

http://search.cpan.org/dist/DBI/DBI.pm#selectall_arrayref

use Data::Dumper;
print Dumper( [ [ { 1, 2 }, [ [ { 6, 7 } ] ] ] ] );
__END__
$VAR1 = [
          [
            {
              '1' => 2
            },
            [
              [
                {
                  '6' => 7
                }
              ]
            ]
          ]
        ];
[download]