FYI: Access methods - relative speed

[H.Merijn Brand]

I thought it might be time to re-check my performance graph(s) on
access methods in DBI now that most DBD's and engines have moved
forward since I did the previous tests:

 http://tux.nl/Talks/DBDc/prfx.html

$ cat test.pl
#!/pro/bin/perl

use 5.16.1;
use warnings;
use Time::HiRes qw( gettimeofday tv_interval );
use DBI;

my $n = 100000;

# This test is about RELATIVE speed differences for access methods
# Not about speed differences between databases

my %db = (
    Pg     => {
        dns     => "dbname=$ENV{LOGNAME}",
        n       => $n,
        },
    mysql  => {
        dsn     => "database=$ENV{LOGNAME}",
        n       => $n / 100,
        },
    Oracle => {
        dsn     => "host=$ENV{ORACLE_HOST};sid=$ENV{ORACLE_SID}",
        user    => $ENV{ORACLE_USER},
        pass    => $ENV{ORACLE_PASS},
        n       => $n / 50,
        },
    SQLite => {
        dsn     => "dbname=sqlite.db",
        n       => $n,
        },
#    Unify  => {
#       },
    CSV    => {
        n       => $n / 100,
        },
    );

my %am = (
    HR  => sub { my $sth = shift;
                while (my $ref = $sth->fetchrow_hashref) { }
                },

    A   => sub { my $sth = shift;
                while (my ($k, $c, $i, $v) = $sth->fetchrow_array) { }
                },

    AR  => sub { my $sth = shift;
                while (my $ref = $sth->fetchrow_arrayref) { }
                },

    DAR => sub { my $sth = shift;
                while (my $ref = DBI::st::fetchrow_arrayref ($sth)) { }
                },

    BC  => sub { my $sth = shift;
                $sth->bind_columns (\my ($k, $c, $i, $v));
                while ($sth->fetch) { }
                },

    DBC => sub { my $sth = shift;
                $sth->bind_columns (\my ($k, $c, $i, $v));
                while (DBI::st::fetchrow_arrayref ($sth)) { }
                },
    );

my %x;
foreach my $db (keys %db) {

    print STDERR " $db ?                  \r";

    my $v;
    eval "require DBD::$db; \$v = DBD::$db->VERSION ()";
    $@ and next;

    $db{$db}{vsn} = $v;
    warn "Testing for DBD::$db-$v\n";

    my $dsn = "dbi:$db:";
    $db{$db}{dsn} and $dsn .= $db{$db}{dsn};
    $db{$db}{$_} //= undef for qw( user pass );
    $ENV{$_} = $db{$db}{env}{$_} for keys %{$db{$db}{env} // {}};

    my $dbh = DBI->connect ($dsn, $db{$db}{user}, $db{$db}{pass}, {
        RaiseError         => 1,
        PrintError         => 1,
        ChopBlanks         => 1,
        ShowErrorStatement => 1,
        FetchHashKeyName   => "NAME_lc",
        }) or next;

    $db eq "CSV" || $db eq "mysql" or $dbh->{AutoCommit} = 0;

    $dbh->do (qq;
        create table test10 (
            k   integer not null primary key,
            c   char (10),
            i   integer,
            v   varchar (10)
            ););
    $dbh->{AutoCommit} or $dbh->commit;

    my $sti = $dbh->prepare ("insert into test10 values (?, ?, ?, ?)");
    $sti->execute ($_, $_, $_, $_) for 1 .. $db{$db}{n};
    $sti->finish;
    $dbh->{AutoCommit} or $dbh->commit;

    foreach my $am (keys %am) {
        my $sth = $dbh->prepare ("select k, c, i, v from test10");
        $sth->execute;
        my $t0 = [gettimeofday];
        $am{$am}->($sth);
        $x{$db}{$am} = tv_interval ($t0);
        }

    $dbh->do ("drop table test10");
    $dbh->{AutoCommit} or $dbh->commit;
    $dbh->disconnect;
    }
warn "Done!\n";

foreach my $db (keys %x) {
    my $norm = 1000 * $x{$db}{A};
    my %c = map { $_ => int ($norm / $x{$db}{$_}) } keys %am;

    print "$db-$db{$db}{vsn}\n";
    printf "%6s %5d\n", $_, $c{$_} for sort { $c{$a} <=> $c{$b} } keys %c;
    }
$ perl speed.pl
Oracle-1.68
    HR   639
   DBC   804
    AR   913
    BC   956
   DAR   967
     A   999
SQLite-1.40
    HR   255
     A  1000
    AR  1041
   DAR  1166
    BC  1333
   DBC  1455
Pg-3.0.0
    HR   198
     A  1000
    AR  1143
   DAR  1186
   DBC  1326
    BC  1332
CSV-0.41
    HR   683
     A  1000
    AR  1164
   DAR  1171
    BC  1181
   DBC  1185
mysql-4.025
    HR   102
     A   999
    AR  1126
   DAR  1271
    BC  1287
   DBC  1328

This clearly shows that having Oracle on a remote host causes the
network to have more influence than the access method

-- 
H.Merijn Brand  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
using perl5.00307 .. 5.19   porting perl5 on HP-UX, AIX, and openSUSE
http://mirrors.develooper.com/hpux/        http://www.test-smoke.org/
http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/