#!/perl
use strict;
use warnings FATAL => qw(all);
use DBI;
use File::Spec;
use Time::HiRes qw (time);
my $MEMORY = 1;
my $start = time;
my $PATH = "C:/TMP/___TMP/_TRASH";
my $dbfile = File::Spec->catdir($PATH, 'Big_DB.db');
my $dbh;
if (1 == $MEMORY)
{
$dbh = DBI->connect('dbi:SQLite:dbname=:memory:',"","",{RaiseError => 1}) or die "Couldn't connect to database: " . DBI->errstr;
}
else
{
$dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError => 1}) or die "Couldn't connect to database: " . DBI->errstr;
}
print "Time till connect: ", time() - $start, $/;
$start = time;
if (1 == $MEMORY)
{
$dbh->sqlite_backup_from_file($dbfile);
}
print "Time till load: ", time() - $start, $/;
$start = time;
# Marker.
my $sth = $dbh->prepare("select Name, Writeups from employees e, groups g where e.Level = g.Level and g.Level = 'Pope (28)' order by e.Writeups desc ");
print "Time till prepare: ", time() - $start, $/;
$start = time;
$sth->execute();
print "Time till execute: ", time() - $start, $/;
$start = time;
my $answer = 0;
while ( my ($name, $writeups) = $sth->fetchrow_array )
{
$answer += 1; # print join(';', ($name, $writeups)), $/;
}
print "Answer: $answer\n";
print "Time after loop: ", time() - $start, $/;
$start = time;
$dbh->disconnect;
####
Time till connect: 0.0150408744812012
Time till load: 4.3910698890686
Time till prepare: 0.000697851181030273
Time till execute: 0.110021829605103
Answer: 40000
Time after loop: 0.16477108001709
##
##
my $sql = "select Name, Writeups from employees e, groups g where e.Level = g.Level and g.Level = 'Pope (28)' order by e.Writeups desc ";
my $answer = 0;
for my $emp (@{$dbh->selectall_arrayref($sql, {Slice => {}})})
{
$answer += 1;
}
print "Answer: $answer\n";
print "Time after loop: ", time() - $start, $/;
$start = time;
$dbh->disconnect;
##
##
Time till connect: 0.0147781372070313
Time till load: 4.42248010635376
Answer: 40000
Time after loop: 0.415067911148071
##
##
#!/perl
use strict;
use warnings FATAL => qw(all);
use DBI;
use File::Spec;
use Benchmark qw(:all) ;
my $PATH = "C:/TMP/___TMP/_TRASH";
my $dbfile = File::Spec->catdir($PATH, 'Big_DB.db');
my $MEMORY = 1;
my $dbh;
if (1 == $MEMORY)
{
$dbh = DBI->connect('dbi:SQLite:dbname=:memory:',"","",{RaiseError => 1}) or die "Couldn't connect to database: " . DBI->errstr;
}
else
{
$dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError => 1}) or die "Couldn't connect to database: " . DBI->errstr;
}
if (1 == $MEMORY)
{
$dbh->sqlite_backup_from_file($dbfile);
}
cmpthese(-3, {
'PrepExec' => sub
{
my $sth = $dbh->prepare("select Name, Writeups from employees e, groups g where e.Level = g.Level and g.Level = 'Pope (28)' order by e.Writeups desc ");
$sth->execute();
my $answer = 0;
while ( my ($name, $writeups) = $sth->fetchrow_array )
{
$answer += 1; # print join(';', ($name, $writeups)), $/;
}
},
'selectall' => sub
{
my $sql = "select Name, Writeups from employees e, groups g where e.Level = g.Level and g.Level = 'Pope (28)' order by e.Writeups desc ";
my $answer = 0;
for my $emp (@{$dbh->selectall_arrayref($sql, {Slice => {}})})
{
$answer += 1;
}
},
});
$dbh->disconnect;
##
##
Rate selectall PrepExec
selectall 2.88/s -- -26%
PrepExec 3.90/s 35% --
##
##
Rate selectall PrepExec
selectall 2.69/s -- -25%
PrepExec 3.57/s 33% --