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 = 10;
# 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/
