Rate I SQLite I Hash (A) I Hash I SQLite 2 I B+Tree (A
+) I B+Tree
I SQLite     18927/s       --       -50%   -51%       -78%         -80
+%     -80%
I Hash (A)   37975/s     101%         --    -3%       -56%         -60
+%     -60%
I Hash       38961/s     106%         3%     --       -55%         -59
+%     -59%
I SQLite 2   86957/s     359%       129%   123%         --          -9
+%      -9%
I B+Tree (A) 95238/s     403%       151%   144%        10%           -
+-       0%
I B+Tree     95238/s     403%       151%   144%        10%           0
+%       --
                Rate S SQLite S Hash(A)   S Hash S B+Tree(A) S SQLite 
+2 S B+Tree
S SQLite     32787/s       --      -44%     -52%        -63%       -64
+%     -68%
S Hash(A)    58252/s      78%        --     -16%        -35%       -37
+%     -44%
S Hash       68966/s     110%       18%       --        -23%       -25
+%     -33%
S B+Tree(A)  89552/s     173%       54%      30%          --        -3
+%     -13%
S SQLite 2   92308/s     182%       58%      34%          3%         -
+-     -11%
S B+Tree    103448/s     216%       78%      50%         16%        12
+%       --
[download]

use DB_File;
use DBI;
use Benchmark qw(cmpthese);
use strict;
use warnings;

# do some setup
my $id_BDB_hash    = 0;
my $id_BDB_hash_a  = 0;
my $id_BDB_btree_a = 0;
my $id_BDB_btree   = 0;
my $id_lit2        = 0;
my $id_lit         = 0;
my $txt            = 'a' x 100;

unlink(
    "bdb.hash.test",    "bdb.btree.test", "bdb.hash.a.test",
    "bdb.btree.a.test", "sqlite.test",    "sqlite2.test"
);

my $dbh =  DBI->connect( 'dbi:SQLite:dbname=sqlite.test', '',  '', { A
+utoCommit => 1, RaiseError => 1 } );
my $dbh2 = DBI->connect( 'dbi:SQLite:dbname=sqlite2.test', '', '', { A
+utoCommit => 0, RaiseError => 1 } );

$dbh->do('PRAGMA page_size = 4096');
$dbh->do('PRAGMA synchronous = off');
$dbh->do('PRAGMA cache_size = 6000');
$dbh->do('PRAGMA journal_mode = MEMORY');
$dbh->do('PRAGMA temp_store = MEMORY');

$dbh->do('CREATE TABLE test (id integer PRIMARY KEY, foo varchar(100))
+');

$dbh2->do('PRAGMA page_size = 4096');
$dbh2->do('PRAGMA cache_size = 6000');
$dbh2->do('PRAGMA journal_mode = MEMORY');
$dbh2->do('PRAGMA temp_store = MEMORY');

$dbh2->do('CREATE TABLE test (id integer PRIMARY KEY, foo varchar(100)
+)');

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=?');

tie my %bdb_btree_a, "DB_File", "bdb.btree.a.test", O_RDWR | O_CREAT, 
+0640, $DB_BTREE
    or die "Cannot open file 'bdb.btree.a.test': $!\n";
tied(%bdb_btree_a)->filter_fetch_key( sub { $_ = unpack( "N", $_ || 0 
+) } );
tied(%bdb_btree_a)->filter_store_key( sub { $_ = pack( "N", $_ || 0 ) 
+} );

tie my %bdb_hash_a, "DB_File", "bdb.hash.a.test", O_RDWR | O_CREAT, 06
+40, $DB_HASH
    or die "Cannot open file 'bdb.hash.a.test': $!\n";
tied(%bdb_hash_a)->filter_fetch_key( sub { $_ = unpack( "N", $_ || 0 )
+ } );
tied(%bdb_hash_a)->filter_store_key( sub { $_ = pack( "N", $_ || 0 ) }
+ );

tie my %bdb_hash, "DB_File", "bdb.hash.test", O_RDWR | O_CREAT, 0640, 
+$DB_HASH
    or die "Cannot open file 'bdb.hash.test': $!\n";
tie my %bdb_btree, "DB_File", "bdb.btree.test", O_RDWR | O_CREAT, 0640
+, $DB_BTREE
    or die "Cannot open file 'bdb.btree.test': $!\n";

sub insertLite {
    $sth_ins->execute( $id_lit++, $txt );
}

sub insertLite2 {
    $sth2_ins->execute( $id_lit2++, $txt );
}

sub insertBDB_btree_a {
    $bdb_btree_a{ $id_BDB_btree_a++ } = $txt;
}

sub insertBDB_hash_a {
    $bdb_hash_a{ $id_BDB_hash_a++ } = $txt;
}

sub insertBDB_btree {
    $bdb_btree{ $id_BDB_btree++ } = $txt;
}

sub insertBDB_hash {
    $bdb_hash{ $id_BDB_hash++ } = $txt;
}

sub selectLite {
    $dbh->selectall_arrayref($sth_sel, undef, $id_lit2++ )
}

sub selectLite2 {
    $dbh2->selectall_arrayref($sth2_sel, undef, $id_lit2++ )
}

sub selectBDB_btree_a {
    $bdb_btree_a{ $id_BDB_btree_a++ };
}

sub selectBDB_hash_a {
    $bdb_hash_a{ $id_BDB_hash_a++ };
}

sub selectBDB_btree {
    $bdb_btree{ $id_BDB_btree++ };
}

sub selectBDB_hash {
    $bdb_hash{ $id_BDB_hash++ };
}

cmpthese(
    60_000,
    {
        'I SQLite'     => \&insertLite,
        'I SQLite 2'   => \&insertLite2,
        'I Hash'       => \&insertBDB_hash,
        'I B+Tree'     => \&insertBDB_btree,
        'I Hash (A)'   => \&insertBDB_hash_a,
        'I B+Tree (A)' => \&insertBDB_btree_a,
    },
);
$dbh2->commit;

$id_BDB_hash    = 0;
$id_BDB_hash_a  = 0;
$id_BDB_btree_a = 0;
$id_BDB_btree   = 0;
$id_lit2        = 0;
$id_lit         = 0;

cmpthese(
    60_000,
    {
        'S SQLite'    => \&selectLite,
        'S SQLite 2'  => \&selectLite2,
        'S Hash'      => \&selectBDB_hash,
        'S B+Tree'    => \&selectBDB_btree,
        'S Hash(A)'   => \&selectBDB_hash_a,
        'S B+Tree(A)' => \&selectBDB_btree_a,
    },
);

foreach my $file (
    "bdb.hash.test", "bdb.btree.test", "bdb.hash.a.test", "bdb.btree.a
+.test",
    "sqlite.test",   "sqlite2.test"
    )
{
    print "$file :" . ( -s $file ) . "\n";
}
[download]