Here's the results of some simple performance testing I did on CDB vs BDB (v3) vs SQLite.
The lines marked "SQLite 2" are with large-grained transactions (a commit at the end of the inserts), whereas the other SQLite is with a commit on every row inserted.
Here's the benchmarking code (it's messy - code comments not welcome ;-)
use DBI;
use CDB_File;
use BerkeleyDB;
# use Benchmark qw(timethese cmpthese);
use Benchmark qw(timethese);
# do some setup
my $id_lit = 0;
my $id_lit2 = 0;
my $id_bdb = 0;
my $id_cdb = 0;
my $txt = 'a' x 100;
unlink("sqlite.test", "sqlite2.test", "bdb.test", "cdb.test");
my $dbh = DBI->connect('dbi:SQLite:dbname=sqlite.test','','',
{ AutoCommit => 1, RaiseError => 1 });
my $dbh2 = DBI->connect('dbi:SQLite:dbname=sqlite2.test','','',
{ AutoCommit => 0, RaiseError => 1 });
$dbh->do('CREATE TABLE test (id integer PRIMARY KEY, foo varchar(100))
+');
$dbh2->do('CREATE TABLE test (id integer PRIMARY KEY, foo varchar(100)
+)');
$dbh->do('PRAGMA default_synchronous = off');
my $sth_ins = $dbh->prepare('INSERT INTO test (id, foo) values (?, ?)'
+);
my $sth_sel = $dbh->prepare('SELECT foo FROM test where id=?');
my $sth2_ins = $dbh2->prepare('INSERT INTO test (id, foo) values (?, ?
+)');
my $sth2_sel = $dbh2->prepare('SELECT foo FROM test where id=?');
my %ch;
my $cdb = new CDB_File ('cdb.test', "cdb.test.$$") or die "Can't creat
+e";
my %bh;
my $bdb = tie %bh, 'BerkeleyDB::Hash',
-Filename => 'bdb.test',
-Flags => DB_CREATE,
;
sub insertLite {
$id_lit++;
$sth_ins->execute($id_lit, $txt);
}
sub insertLite2 {
$id_lit2++;
$sth2_ins->execute($id_lit2, $txt);
}
sub insertCDB {
$id_cdb++;
$cdb->insert($id_cdb, $txt);
}
sub insertBDB {
$id_bdb++;
$bh{$id_bdb} = $txt;
}
sub selectLite {
$id_lit++;
$sth_sel->execute($id_lit);
1 while $sth_sel->fetch;
}
sub selectLite2 {
$id_lit2++;
$sth2_sel->execute($id_lit2);
1 while $sth2_sel->fetch;
}
sub selectCDB {
$id_cdb++;
$ch{$id_cdb};
}
my $t1 = timethese(30_000,
{
'Insert SQLite' => \&insertLite,
'Insert SQLite 2' => \&insertLite2,
'Insert Berkeley' => \&insertBDB,
'Insert CDB' => \&insertCDB,
},
);
$dbh2->commit;
$cdb->finish;
$dbh->{AutoCommit} = 0;
$cdb = tie %ch, 'CDB_File', 'cdb.test' or die "tie failed: $!\n";
# cmpthese $t1;
$id_lit = 0;
$id_lit2 = 0;
$id_bdb = 0;
$id_cdb = 0;
my $t2 = timethese(30_000,
{
'Select SQLite' => \&selectLite,
'Select SQLite 2' => \&selectLite2,
'Select Berkeley' => \&selectBDB,
'Select CDB' => \&selectCDB,
},
);
# cmpthese $t2;