#!/usr/bin/perl -w
use strict;
use DBI;
use DBI::Profile;
use Benchmark qw(cmpthese);

my $dbh;
my $db = 'test';
my $driver = shift || 'mysql';

if ($driver eq 'mysql') {
    $dbh = DBI->connect("dbi:mysql:$db",
        "user", "password", {RaiseError => 1})
        or die "can't connect\n";
}
elsif ($driver eq 'SQLite') {
    $dbh = DBI->connect("dbi:SQLite:$db", "","",
        {RaiseError => 1})
   or die "can't connect\n";
}
else {
    # insert your favorite driver connection code here
    die "Driver $driver not supported\n"
}

#
# testdbi is a table with 100,000 records
# with 20 columns 9 chars each
#

my $query1 = qq{SELECT id1 from testdbi };                 # 1 column

my $query10 = qq{
    SELECT
        id1,id2,id3,id4,id5,id6,id7,id8,id9,id10
    FROM testdbi};                                         # 10 columns

my $query20 = qq{
    SELECT
        id1,id2,id3,id4,id5,id6,id7,id8,id9,id10,
        id11,id12,id13,id14,id15,id16,id17,id18,id19,id20
    FROM testdbi };                                        # 20 columns

sub profile_calls {
    my ($sth, $columns, $max_rows) = @_;
    my (@array, $rowcache, $aref, $href);
    printf "PROFILE - FETCHING %d COLUMN%s\n", $columns, $columns > 1 ? "s" :"";
    $sth->{Profile} = DBI::Profile->new;
    $sth->{Profile} = 4;
    $sth->execute; while (@array = $sth->fetchrow_array()) {};
    $sth->execute; while ($aref = $sth->fetchrow_arrayref()) {};
    $sth->execute; while ($href = $sth->fetchrow_hashref()) {};
    $sth->execute; while ($aref = shift(@$rowcache)
        || shift (@{$rowcache= $sth->fetchall_arrayref(undef, $max_rows)})) {};
    print $sth->{Profile}->format;
    $sth->{Profile} =0;
}

sub compare_calls {
    my ($sth, $columns, $max_rows) = @_;
    my (@array, $rowcache, $aref, $href);
    printf "COMPARE - FETCHING %d COLUMN%s\n", $columns, $columns > 1 ? "s" :"";
    cmpthese (5,
    {
     '---fetchrow_array' =>
        sub {$sth->execute; while (@array = $sth->fetchrow_array()) {} },
     'fetchrow_arrayref' =>
         sub {$sth->execute; while ($aref = $sth->fetchrow_arrayref()) {} },
      '-fetchrow_hashref' =>
        sub {$sth->execute; while ($href = $sth->fetchrow_hashref()) {} },
     'fetchall_arrayref' =>
        sub {$sth->execute; while ($aref = $sth->fetchall_arrayref(undef, $max_rows)) {} },
     'fetchall_arrayref(buffered)' =>
        sub {$sth->execute; while ($aref = shift(@$rowcache)
            || shift (@{$rowcache =
               $sth->fetchall_arrayref(undef, $max_rows)})) {} },
    }
    );
}

my $max_rows = 1000;
my $sth1  = $dbh->prepare($query1);
my $sth10 = $dbh->prepare($query10);
my $sth20 = $dbh->prepare($query20);

compare_calls($sth1,   1, 10_000);
compare_calls($sth10, 10, $max_rows);
compare_calls($sth20, 20, $max_rows);

profile_calls($sth1,   1, $max_rows);
profile_calls($sth10, 10, $max_rows);
profile_calls($sth20, 20, $max_rows);

$dbh->disconnect;