FYI: Access methods - relative speed

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/