I was using SQLite only because it is easy to use, and I use it just about for everything and everywhere I can. My intent was not to make SQLite queries run faster.
That said, I actually posted my query on the memcache mailing list and discovered several things. The entire thread is at http://bit.ly/lnfiuv. The major takeaway was that Cache::Memcached was way to slow. Per "Dormando's" suggestion, I installed Cache::Memcached::libmemcached and memcached code actually became about 30% faster than pure SQLite code. I also modified my test to get_multi IDs, as I learned that network travel is just a lot slower than disk travel. Here is the result --
Benchmark: timing 10000 iterations of query_dbh, query_mem...
query_dbh: 6 wallclock secs ( 3.87 usr + 0.92 sys = 4.79 CPU) @ 20
+87.68/s (n=10000)
query_mem: 6 wallclock secs ( 2.51 usr + 1.23 sys = 3.74 CPU) @ 26
+73.80/s (n=10000)
For the record, here is my complete test script, including the code for creating the test db.
#!/usr/local/bin/perl
use strict;
use Cache::Memcached::libmemcached;
use DBI qw(:sql_types);
use Benchmark qw(:all);
my $dbh = DBI->connect(
"dbi:SQLite:dbname=mem.sqlite","","",
{RaiseError => 1, AutoCommit => 0}
);
my $memd = new Cache::Memcached::libmemcached ({
'servers' => [ "localhost:11212" ]
});
my $sql = "SELECT Ifnull(str, 'none') FROM t WHERE id = ?";
my @ids = map { int(rand(19_999)) || 1 } (0 .. 20);
timethese(10_000, {
'query_mem' => sub {
my $hashref = $memd->get_multi(@ids);
my @res = ();
while (my ($id, $str) = each %$hashref) {
unless ($str) {
my $sth = $dbh->prepare($sql);
$sth->execute($id);
($str) = $sth->fetchrow_array;
$memd->set($id, $str);
}
push @res, [$id, $str];
}
out(@res);
},
'query_dbh' => sub {
my $sth = $dbh->prepare($sql);
my @res = ();
for (@ids) {
$sth->execute($_);
my ($str) = $sth->fetchrow_array;
push @res, [$_, $str];
}
out(@res);
},
});
sub out {
my @res = @_;
open F, ">", "foo.txt" or die $!;
say F "id: " . $_->[0] . ", str: " . $_->[1] for (@res);
close F;
}
sub create_db {
my $sth = $dbh->prepare(
"CREATE TABLE t (id INTEGER PRIMARY KEY, str TEXT"
);
$sth->execute;
$dbh->commit;
}
sub pop_db {
my $sth = $dbh->prepare("INSERT INTO t (str) VALUES (?)");
$sth->execute(randstr(rand(100))) for (0 .. 20_000);
$dbh->commit;
}
sub randstr {
my $len = shift;
my @chr = ('a'..'z','A'..'Z','0'..'9','_', ' ');
return join "", map { $chr[rand @chr] } 1 .. $len;
}
when small people start casting long shadows, it is time to go to bed
|