comment on

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.

Benchmark: timing 30000 iterations of Insert Berkeley, Insert CDB, Ins
+ert SQLite, Insert SQLite 2...
Insert Berkeley: 10 wallclock secs ( 3.26 usr +  5.66 sys =  8.92 CPU)
Insert CDB:  0 wallclock secs ( 0.25 usr +  0.02 sys =  0.27 CPU)
            (warning: too few iterations for a reliable count)
Insert SQLite: 20 wallclock secs ( 9.61 usr +  7.96 sys = 17.57 CPU)
Insert SQLite 2:  6 wallclock secs ( 4.92 usr +  0.08 sys =  5.00 CPU)

Benchmark: timing 30000 iterations of Select Berkeley, Select CDB, Sel
+ect SQLite, Select SQLite 2...
Select Berkeley:  5 wallclock secs ( 2.21 usr +  2.66 sys =  4.87 CPU)
Select CDB:  2 wallclock secs ( 1.10 usr +  0.83 sys =  1.93 CPU)
Select SQLite:  5 wallclock secs ( 4.61 usr +  0.15 sys =  4.76 CPU)
Select SQLite 2:  8 wallclock secs ( 4.70 usr +  0.24 sys =  4.94 CPU)
[download]

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;
[download]

In reply to SQLite vs CDB_File vs BerkeleyDB by Matts

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


No such thing as a small change
	PerlMonks