Bind DBI columns for speed

From xoa

When in a tight loop of many records, using $sth->fetchrow_hashref can be expensive.

For a batch of 500,000 records, the timings look like this:

hashref     took 31.5048 wallclock secs (17.95 usr +  0.48 sys = 18.43 CPU) @  0.05/s (n=1)
array       took 8.83724 wallclock secs ( 6.08 usr +  0.44 sys =  6.52 CPU) @  0.15/s (n=1)
arrayref    took 5.5308 wallclock secs ( 4.44 usr +  0.03 sys =  4.47 CPU) @  0.22/s (n=1)
direct_bind took 4.46956 wallclock secs ( 3.83 usr +  0.02 sys =  3.85 CPU) @  0.26/s (n=1)

Here's the benchmark program I used:

#!/usr/bin/perl

use strict;
use warnings;

use Benchmark ':hireswallclock';
use FLR::DB qw(:sqldo);

FLR::DB::set_instance('TTDEV') || die "Can't set_instance('TTDEV'): $!";

our $sth;

sub prep_handle {
    my $sql = <<"EOF";
    select interestlevel, av_flag, isbn, title
    from testbook
    limit 500000
EOF
    return sqldo_handle( $sql );
}

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

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

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

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

for my $func ( qw( hashref array arrayref direct_bind ) ) {
    $sth = prep_handle();
    my $t = timeit( 1, "$func()" );
    print "$func took ", timestr($t), "\n";
}