DBD-DBM too slow
am 23.08.2006 20:23:49 von mankyuhan2001Hi. I installed DBD::DBM through cpan and everything seems fine.
I wanted to benchmark DBD::DBM's performance, so I did simple test.
I populate table that I created with 200000 integer and strings.
In mysql, it was going almost 7000 inserts / sec
but in DBD::DBM, the best I could get was 130/sec
Is there some optimization that I should do?
Is it possible to use something like Mmap to speed up DBD::DBM?
Here is my source code.
Thanks.
#######################################
# This will create Table
#######################################
use DBI;
my $dbh =3D DBI->connect('dbi:DBM:');
$dbh->{RaiseError} =3D 1;
my $sth =3D $dbh->do("CREATE TABLE dbmInt (id INTEGER, input TEXT)");
my $sth =3D $dbh->do("CREATE TABLE dbmChar (id CHAR(4), input TEXT)");
$dbh->disconnect;
#####################################
# This will populate Table
# mkUtil is nothing but collection of simple functions like random number g=
enerator=20
# and printing time and messages..
####################################3
use DBI;
use mkUtil;
my $dbh =3D DBI->connect('dbi:DBM:');
$dbh->{RaiseError} =3D 1;
my (@sql_c, @sql_i);
my $id =3D 0;
mkUtil::printTime( "DBM: Start Generating SQL Command String");
foreach my $cid ('aaaa'...'gzzz'){
push(@sql_c, "INSERT INTO dbmChar (id, input) VALUES ('$cid', 'Dumm=
yTest')");
push(@sql_i, "INSERT INTO dbmInt (id, input) VALUES ($id, 'DummyTes=
t')");
$id++;
}
mkUtil::printTime( "DBM: Finished Generating SQL Command String");
my $count =3D 0;
my $startTime =3D mkUtil::printTime( "DBM: INT Insert Start");
foreach my $sql (@sql_i){
my $sth =3D $dbh->prepare($sql);
$sth->execute;
$count++;
if($count%2000 eq 0){
mkUtil::printTime("$count");
}
}
my $endTime =3D mkUtil::printTime( "DBM: INT Insert END");
mkUtil::printAVG(123032, ($endTime - $startTime), "DBM: INT Insert AVG (sec=
)");
$dbh->disconnect;
##################################3
# This will read data (randomly) from table
####################################
use DBI;
use mkUtil;
my $dbh =3D DBI->connect('dbi:DBM:');
$dbh->{RaiseError} =3D 1;
my $time =3D 10;
if(defined $ARGV[0]){
$time =3D $ARGV[0];
}
my $count =3D 0;
my $startTime =3D mkUtil::printTime("DBD::DBM:: Integer Select Start");
my $endTime =3D mkUtil::getTime();
while($endTime - $startTime < $time){
my %r =3D mkUtil::strRand();
my $rInt =3D $r{'integer'};
my $ary =3D $dbh->selectall_arrayref("SELECT * FROM dbmInt WHERE id=
=3D $rInt");
foreach $item (@{$ary}){
foreach $inside (@{$item}){
# print "$inside\n";
}
}
# sleep(1);
$endTime =3D mkUtil::getTime();
$count++;
}
$endTime =3D mkUtil::printTime("DBD::DBM:: Integer Select End");
print "COUNT: $count\n";
mkUtil::printAVG($count, $time, "DBD::DBM:: Integer Select AVG (sec)");
$count =3D 0;
$startTime =3D mkUtil::printTime("DBD::DBM:: Char Select Start");
$endTime =3D mkUtil::getTime();
while($endTime - $startTime < $time){
my %r =3D mkUtil::strRand();
my $rChar =3D $r{'string'};
my $ary =3D $dbh->selectall_arrayref("SELECT * FROM dbmChar WHERE i=
d =3D '$rChar'");
foreach $item(@{$ary}){
foreach $inside (@{$item}){
# print "$inside\n";
}
}
# sleep(1);
$endTime =3D mkUtil::getTime();
$count++;
}
$endTime =3D mkUtil::printTime("DBD::DBM:: Char Select End");
print "COUNT: $count\n";
mkUtil::printAVG($count, $time, "DBD::DBM:: Char Select AVG (sec)");
$dbh->disconnect;