Perl + DBD::Pg で COPY を使う

以前、ベンチマーク取ったことがあるので書いておこう。

PostgreSQLに大量のデータを登録する際はINSERTを使って一件づつ処理するより、COPYを使って一気に登録した方が実行速度が（かなり）速いです。

http://www.1x1.jp/blog/2007/03/php_pgsql_copy.html

やり方は DBD::Pg の POD 読めば書いてありますが、以下のような感じで。

$dbh->do("COPY mytable FROM STDIN");
$dbh->pg_putline("123\tPepperoni\t3\n");
$dbh->pg_putline("314\tMushroom\t8\n");
$dbh->pg_putline("6\tAnchovies\t100\n");
$dbh->pg_endcopy;

で、実際どれぐらい速くなるのか。100行分まとめて投入する処理でベンチマークを取りました。コードは最後に載せておきます。PostgrSQL のバージョンは 8.1 (Linux)。

比較した方法は以下。

• DBIx::Class で create
• prepare & execute
• 生の SQL 発行 ( $dbh->do )
• COPY

結果。

               Rate         dbic prepared_sql      raw_sql         copy
dbic         10.3/s           --         -98%         -98%         -99%
prepared_sql  417/s        3954%           --          -4%         -75%
raw_sql       435/s        4130%           4%           --         -74%
copy         1667/s       16117%         300%         283%           --

なぜか prepare & execute の方が生 SQL よりも遅くなってるのが謎ですが。

DBIC が遅いのはまあ当然なのですが、それにしても 160倍違うので要注意。

#!/usr/bin/perl

package MyDB::Foo;
use base qw/DBIx::Class/;
__PACKAGE__->load_components(qw/ PK::Auto Core /);
__PACKAGE__->table('foo');
__PACKAGE__->add_columns(
    qw/ id bar baz /,
);
__PACKAGE__->set_primary_key('id');

package MyDB;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_classes('Foo');

package main;
use strict;
use Benchmark qw/:all/;

my $schema = MyDB->connect(
    'dbi:Pg:dbname=fujiwara', 'fujiwara', '',
    { RaiseError => 1, AutoCommit => 0 }
);
my $dbh = $schema->storage->dbh;
$dbh->do('TRUNCATE foo');

my $count = shift || 100;

sub by_dbic {
    for (1..$count) {
        $schema->resultset('Foo')->create({
            bar => time,
            baz => 'DBIC',
        });
    }
}

sub by_prepared_sql {
    my $sth = $dbh->prepare('INSERT INTO foo (bar, baz) VALUES (?, ?)');
    for (1..$count) {
        $sth->execute( time, 'PREPARED' );
    }
}

sub by_raw_sql {
    for (1..$count) {
        $dbh->do(sprintf 'INSERT INTO foo (bar, baz) VALUES (%d, %s)', time, q{'RAW'});
    }
}

sub by_copy {
    $dbh->do("COPY foo (bar, baz) FROM STDIN");
    for (1..$count) {
        $dbh->pg_putline(time. "\tCOPY\n");
    }
    $dbh->pg_endcopy;
}

cmpthese( 100, {
   dbic         => \&by_dbic,
   prepared_sql => \&by_prepared_sql,
   raw_sql      => \&by_raw_sql,
   copy         => \&by_copy,
});

$dbh->commit;