# sqlite benchmark

use strict;
use warnings;

use DBI;
use Benchmark qw(cmpthese);

my $N     ||= 1e3;
my $count ||= -1;
my @rows = (1 .. $N);

sub no_index_autocommit_1 {
  my $start = time;
  my $dbh   = DBI->connect("DBI:SQLite:no_index_autocommit_1.sqlite") 
+or die;

  # Get max to avoid duplicate entries (even w/o index to make
  # benchmark equal for all tests)
  my $sth = undef;
  $sth = $dbh->prepare(qq(Select max(col1) from logentries));
  $sth->execute();
  my $max = $sth->fetchrow_arrayref->[0] ||= 0;

  $sth   = $dbh->prepare(
    q{INSERT INTO logentries (col1, col2, col3, col4) values (?,?,?,?)
+ }
  );
  $sth->execute($_+$max, $_, $_, $_) for @rows;
}

sub no_index_autocommit_0  {
  my $start = time;
  my $dbh = DBI->connect("DBI:SQLite:no_index_autocommit_0.sqlite") or
+ die;
  $dbh->do('BEGIN');

  # Get max to avoid duplicate entries (even w/o index to make
  # benchmark equal for all tests)
  my $sth = undef;
  $sth = $dbh->prepare(qq(Select max(col1) from logentries));
  $sth->execute();
  my $max = $sth->fetchrow_arrayref->[0] ||= 0;

  $sth = $dbh->prepare(
    qq(INSERT INTO logentries
    (col1, col2, col3, col4) values (?,?,?,?) )
  );
  $sth->execute($_+$max, $_, $_, $_) for @rows;
  $dbh->do('COMMIT');
}

sub index_autocommit_1 {
  my $start = time;
  my $dbh   = DBI->connect("DBI:SQLite:index_autocommit_1.sqlite") or 
+die;

  # Get max to avoid duplicate entries
  my $sth = undef;
  $sth = $dbh->prepare(qq(Select max(col1) from logentries));
  $sth->execute();
  my $max = $sth->fetchrow_arrayref->[0] ||= 0;

  $sth = $dbh->prepare(qq(Select max(col1) from logentries));
  $sth->execute();
  $sth   = $dbh->prepare(
    q{INSERT INTO logentries (col1, col2, col3, col4) values (?,?,?,?)
+ }
  );
  $sth->execute($_ + $max, $_, $_, $_) for @rows;
}

sub index_autocommit_0  {
  my $start = time;
  my $dbh = DBI->connect("DBI:SQLite:index_autocommit_0.sqlite") or di
+e;

  $dbh->do('BEGIN');

  # Get max to avoid duplicate entries
  my $sth = undef;
  $sth = $dbh->prepare(qq(Select max(col1) from logentries));
  $sth->execute();
  my $max = $sth->fetchrow_arrayref->[0] ||= 0;

  $sth = $dbh->prepare(
    qq(INSERT INTO logentries
    (col1, col2, col3, col4) values (?,?,?,?) )
  );
  $sth->execute($_ + $max, $_, $_, $_) for @rows;
  $dbh->do('COMMIT');
}


cmpthese(
  $count,
  {
    no_index_autocommit_0 => \&no_index_autocommit_0,
    no_index_autocommit_1 => \&no_index_autocommit_1,
    index_autocommit_0    => \&index_autocommit_0,
    index_autocommit_1    => \&index_autocommit_1,
  }
);

__END__

=pod

=head1 DROP AND CREATE

sqlite3 no_index_autocommit_0.sqlite  'drop table logentries'
sqlite3 no_index_autocommit_1.sqlite 'drop table logentries'
sqlite3 index_autocommit_0.sqlite  'drop table logentries'
sqlite3 index_autocommit_1.sqlite 'drop table logentries'

sqlite3 no_index_autocommit_0.sqlite 'create table logentries (col1 in
+teger, col2 integer, col3 integer, col4 integer)'
sqlite3 no_index_autocommit_1.sqlite 'create table logentries (col1 in
+teger, col2 integer, col3 integer, col4 integer)'
sqlite3 index_autocommit_0.sqlite 'create table logentries (col1 integ
+er, col2 integer, col3 integer, col4 integer, primary key (col1))'
sqlite3 index_autocommit_1.sqlite 'create table logentries (col1 integ
+er, col2 integer, col3 integer, col4 integer, primary key (col1))'

=head1 RESULTS

Note: Run the I<sqlite3> commands above before every run of the
benchmark to start off with empty databases every time.

$ perl -s benchmark.pl -N=100 count=1 max=0 no_index_autocommit_0.sqli
+te at benchmark.pl line 41.
                        Rate no_index_autocommit_1 index_autocommit_1 
+no_index_autocommit_0 index_autocommit_0
no_index_autocommit_1 12.5/s                    --               -19% 
+                 -42%               -56%
index_autocommit_1    15.4/s                   23%                 -- 
+                 -29%               -46%
no_index_autocommit_0 21.5/s                   72%                40% 
+                   --               -24%
index_autocommit_0    28.3/s                  127%                84% 
+                  31%                 --

$ perl -s benchmark.pl -N=1000 count=1
                        Rate no_index_autocommit_1 index_autocommit_1 
+no_index_autocommit_0 index_autocommit_0
no_index_autocommit_1 6.67/s                    --               -59% 
+                 -69%               -76%
index_autocommit_1    16.2/s                  143%                 -- 
+                 -24%               -43%
no_index_autocommit_0 21.3/s                  219%                31% 
+                   --               -25%
index_autocommit_0    28.3/s                  325%                75% 
+                  33%                 --

$ perl -s benchmark.pl -N=10000 count=1
                         Rate no_index_autocommit_1 index_autocommit_1
+ no_index_autocommit_0 index_autocommit_0
no_index_autocommit_1 10.3/s                    --               -28% 
+                 -53%               -63%
index_autocommit_1    14.3/s                   39%                 -- 
+                 -35%               -49%
no_index_autocommit_0 22.0/s                  113%                54% 
+                   --               -21%
index_autocommit_0    27.8/s                  170%                95% 
+                  27%                 --

=cut
[download]