Re: DBI Hash of Column name values

[derby]

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 => {} } );

Now I can do this:

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

[Tux]

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

YMMV

[derby]

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

[Tux]

For the local compare, where bind was twice as fast as slice, I used:

Linux    2.6.31.12-0.2 x86_64
CPU      Core(TM) i5 CPU 660 @ 3.33GHz/1199(2)
Memory   7802 Mb
perl     5.12.1 64bitall/longdouble
DBI      1.613
DBD::Pg  2.17.1
Postgres 8.4.4

[avrono]

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

[avrono]

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

[Anonymous Monk]

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
                }
              ]
            ]
          ]
        ];

[avrono]

Thanks for all the wisdom ! I am not sure how efficient my final solution is, but I wanted an array of hashes. This works

my %rec;

$sth->bind_columns (\(@rec{@{$sth->{NAME_lc}}}));


while ($sth->fetch) {
my %res = %rec;
    push(@result, \%res);
}
    return \@result;