DBI->connect_cached(...)は早いの? 
Tuesday, January 18, 2011, 06:12 PM - Programing, Programing / Perl
DBIのpodにも書いてあるけど、->*_cachedは遅い
多少内部処理がお粗末になる覚悟でそれっぽいモジュールや後述するplan Aのような単純な実装を選択する方が望ましいだろう

検証用コード
#!/usr/bin/perl
use Benchmark;
use DBI;

sub plan_a
{
$sth1->bind_param(1,$ARGV[1],DBI::SQL_INTEGER);
$sth1->execute();
while($sth1->fetch()){
}
$sth1->finish();

return();
}

sub plan_b
{
my $dbh2 = DBI->connect(qw(dbi:mysql:database=a;host=172.16.2.205;port=3306 a));
my $sth2 = $dbh2->prepare("SELECT * FROM `a` WHERE 1 LIMIT 0,?");
$sth2->bind_param(1,$ARGV[1],DBI::SQL_INTEGER);
$sth2->execute();
while($sth2->fetch()){
}
$sth2->finish();
$dbh2->disconnect();

return();
}

sub plan_c
{
my $dbh3 = DBI->connect_cached(qw(dbi:mysql:database=a;host=172.16.2.205;port=3306 a));
my $sth3 = $dbh3->prepare_cached("SELECT * FROM `a` WHERE 1 LIMIT 0,?");
$sth3->bind_param(1,$ARGV[1],DBI::SQL_INTEGER);
$sth3->execute();
while($sth3->fetch()){
}

return();
}

printf("=== %d records, %d repeats ===\n",reverse(@ARGV));

$dbh1 = DBI->connect(qw(dbi:mysql:database=a;host=172.16.2.205;port=3306 a));
$sth1 = $dbh1->prepare("SELECT * FROM `a` WHERE 1 LIMIT 0,?");

sqrt($_) for(1..10000);
timethese(
$ARGV[0],
{
"A" =>\&plan_a,
"B" =>\&plan_b,
"C" =>\&plan_c,
}
);

__END__

の結果
> === 1 records, 10000 repeats ===
> Benchmark: timing 10000 iterations of A, B, C...
> A: 6 wallclock secs ( 1.23 usr + 0.34 sys = 1.57 CPU) @ 6369.43/s (n=10000)
> B: 41 wallclock secs ( 8.22 usr + 2.34 sys = 10.56 CPU) @ 946.97/s (n=10000)
> C: 17 wallclock secs ( 4.84 usr + 0.74 sys = 5.58 CPU) @ 1792.11/s (n=10000)
これは少々酷い
接続切断を繰り返すplan Bと比べれば2倍だろうが、plan Aから見れば大きな変化とは言えない

大体こういうふざけたモジュールが存在していたとして
package CachedDBI4;
use DBI;
use Data::Dumper;
use vars qw(%db_vars);
use vars qw(%st_vars);

sub connect_cached
{
my($f,@r) = @_;

return $CachedDBI4::db_vars{Dumper(@r)} ||= $f->connect(@r);
}


sub prepare_cached
{
my($f,@r) = @_;

return $CachedDBI4::st_vars{Dumper(@r)} ||= $f->prepare(@r);
}

*DBI::connect_cached = \&connect_cached;
*DBI::db::prepare_cached = \&prepare_cached;

__PACKAGE__

これを使ったスコアが
> === 1 records, 10000 repeats ===
> Benchmark: timing 10000 iterations of D...
> D: 9 wallclock secs ( 3.94 usr + 0.49 sys = 4.43 CPU) @ 2257.34/s (n=10000)
B, C, Dとどんぐりだが、こんなコードが最も早いとは思わなかったわけで

podに書いている時点で知れてる事だけど

Comments

Add Comment

Fill out the form below to add your own comments.









Insert Special: