[pgsql-jp: 25173] Re: INSERT文に関して

[KAWAI,Takanori]

川合孝典です。

私としても杉田さんの書かれている通り、まずは実際にやって試して
見るのが一番だと思います。（人に聞くより確実にレスポンスがあるし）

----- Original Message -----
From: "Tadashi Kanbayashi" <Tadashi.Kanbayashi ＠ toppan.co.jp>
To: <pgsql-jp ＠ ml.postgresql.jp>
Sent: Friday, March 08, 2002 10:48 AM
Subject: [pgsql-jp: 25147] Re: INSERT文に関して


> 神林です。
（中略）
> PostgreSQLの場合も、prepareした後のSQL文の解析結果は、
> キャッシュされているんでしょうか。パフォーマンス的にもそうであれば
> 教えていただいた方法が最良かと思います。
＃解析結果のキャッシュってOracleとかのイメージなんでしょうけれども
DBD::Pgのドキュメント(いくつかあるんですがPg.pmのPOD部分）に
あるよう、SQLの解析結果はキャッシュはされません。

<引用>
PostgreSQL does not have the concept of preparing
a statement. Hence the prepare method just stores
the statement after checking for place-holders.
No information about the statement is available
after preparing it.
</引用>
ただDBD::PgとしてはSQLステートメントそのものやプレースホルダに
関する情報は保持していますから、そのあたりがあとから効いてきます。
で、実際試してみましたが、当然というかCOPYコマンドがぶっちぎりの
最速です。特にデータが大量になればなるほど効いてくるでしょう。

変数を埋め込むと遅くなるのは当たり前（prepareも入りますけど、
それ以前にステートメントを展開するのにも時間が掛かりますしね）

また件数が多くなると複数ステートメントを１度に実行したほうが
プレースホルダを利用するよりも遅くなってしまいました。
CPUタイムからするとPostgreSQL本体のほうで時間が掛かって
いるようにも見えますが、さだかではありません。
＃少なくともあまりいい手段だとも思えないし。

[環境]
TurboLinux6.0 Workstation PostgreSQL 7.1.3
Perl 5.00503 Pg 1.9.0

[実行結果]
1000件
COPY : 0 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)
Embed: 2 wallclock secs ( 0.91 usr +  0.11 sys =  1.02 CPU)
Place: 1 wallclock secs ( 0.29 usr +  0.04 sys =  0.33 CPU)
Multi: 0 wallclock secs ( 0.02 usr +  0.00 sys =  0.02 CPU)

2000件
COPY : 0 wallclock secs ( 0.02 usr +  0.00 sys =  0.02 CPU)
Embed: 4 wallclock secs ( 1.78 usr +  0.33 sys =  2.11 CPU)
Place: 2 wallclock secs ( 0.54 usr +  0.15 sys =  0.69 CPU)
Multi: 2 wallclock secs ( 0.05 usr +  0.01 sys =  0.06 CPU)

10,000件
COPY : 0 wallclock secs ( 0.10 usr +  0.00 sys =  0.10 CPU)
Embed:19 wallclock secs ( 8.80 usr +  0.90 sys =  9.70 CPU)
Place:11 wallclock secs ( 2.45 usr +  0.62 sys =  3.07 CPU)
Multi:18 wallclock secs ( 0.24 usr +  0.07 sys =  0.31 CPU)

[実行したスクリプト]
use strict;
use Benchmark;
use DBI;
my @tb;
my $hDb = DBI->connect('dbi:Pg:dbname=gotdate', 'postgres', '',
      {AutoCommit=>0, RaiseError=>1}) || die $DBI::errstr;
$hDb->do('DROP TABLE emp');
$hDb->do('CREATE TABLE emp (no integer, name varchar(80))');
my @aData = ([10, 'ABC']) x $ARGV[0];
my $sSQL = 'SELECT COUNT(*) FROM emp';
eval {
#1. COPY command
    print "COPY:\n";
    $hDb->do('DELETE FROM emp');
    print "CNT:", $hDb->selectrow_array($sSQL), "\n";
    print scalar(localtime), "\n";
    push(@tb, Benchmark->new);
    {
        my $raData;
        $hDb->do('copy emp from stdin');
        foreach $raData (@aData) {
            $hDb->func(join("\t", @$raData). "\n", 'putline');
        }
        $hDb->func("\\.\n", 'putline');
        $hDb->func('endcopy');
    }
    push(@tb, Benchmark->new);
    print "CNT:", $hDb->selectrow_array($sSQL), "\n";

#2. Embed
    print "Embed:\n";
    $hDb->do('DELETE FROM emp');
    print "CNT:", $hDb->selectrow_array($sSQL), "\n";
    print scalar(localtime), "\n";
    push(@tb, Benchmark->new);
    {
        my $raData;
        foreach $raData (@aData) {
            $hDb->do(
              'INSERT INTO emp VALUES('.
              join(',', map { "'$_'"} @$raData). ')');
        }
        $hDb->commit;
    }
    push(@tb, Benchmark->new);
    print "CNT:", $hDb->selectrow_array($sSQL), "\n";

#3. Placeholder
    print "Placeholder:\n";
    $hDb->do('DELETE FROM emp');
    print "CNT:", $hDb->selectrow_array($sSQL), "\n";
    print scalar(localtime), "\n";
    push(@tb, Benchmark->new);
    {
        my $raData;
        my $hSt = $hDb->prepare('INSERT INTO emp VALUES(?, ?)');
        foreach $raData (@aData) {
            $hSt->execute(@$raData);
        }
        $hDb->commit;
    }
    push(@tb, Benchmark->new);
    print "CNT:", $hDb->selectrow_array($sSQL), "\n";

#4. Multi-Statement
    print "Multi:\n";
    $hDb->do('DELETE FROM emp');
    print "CNT:", $hDb->selectrow_array($sSQL), "\n";
    print scalar(localtime), "\n";
    push(@tb, Benchmark->new);
    {
        my @aSt = map {
         'INSERT INTO emp VALUES('.
          join(',', map { "'$_'"} @$_). ");\n";
        }  @aData;
        $hDb->do("@aSt");
        $hDb->commit;
    }
    push(@tb, Benchmark->new);
    print "CNT:", $hDb->selectrow_array($sSQL), "\n";
};
die $@ if($@);
$hDb->disconnect;
print "COPY :",timestr(timediff($tb[1], $tb[0])),"\n";
print "Embed:",timestr(timediff($tb[3], $tb[2])),"\n";
print "Place:",timestr(timediff($tb[5], $tb[4])),"\n";
print "Multi:",timestr(timediff($tb[7], $tb[6])),"\n";

===================================================
川合 孝典 (Hippo2000)
   DBI日本語メーリングリスト管理人、Kansai.pm所属
   kwitknr ＠ cpan.org GCD00051 ＠ nifty.ne.jp
   http://member.nifty.ne.jp/hippo2000
   http://www.hippo2000.net/
「Perlを256倍使うための本　DBI編」　3/14発売
http://www.ascii.co.jp/books/detail/4-7561/4-7561-4058-0.html
===================================================