SQLiteとトランザクション

SQLiteの追加/更新はトランザクションを使うと高速化に効果があるというのはよく効くので実際試してみました。

use strict;
use warnings;

use DBI;
use Benchmark qw(:all);

my $count = 100;
my $loop  = 100;

cmpthese(
    $count,
    {
        commit_each_insert => \&commit_each_insert,
        commit_bulk_insert => \&commit_bulk_insert,
    }
);

sub commit_each_insert {
    unlink('test1.db');
    my $dbh = DBI->connect('dbi:SQLite:dbname=test1.db');
    $dbh->do(
        "CREATE TABLE test (id int not null, name text not null, primary key(id))"
    );
    for (1 .. $loop) {
        $dbh->do("INSERT INTO test(id, name) VALUES($_, 'name_$_')");
    }
}

sub commit_bulk_insert {
    unlink('test2.db');
    my $dbh = DBI->connect('dbi:SQLite:dbname=test2.db');
    $dbh->do(
        "CREATE TABLE test (id int not null, name text not null, primary key(id))"
    );
    $dbh->do('BEGIN');
    for (1 .. $loop) {
        $dbh->do("INSERT INTO test(id, name) VALUES($_, 'name_$_')");
    }
    $dbh->do('COMMIT');
}

結果は

                     Rate commit_each_insert commit_bulk_insert
commit_each_insert 18.8/s                 --               -71%
commit_bulk_insert 64.5/s               243%                 --

爆速です。

一方でSELECTのトランザクション化は効果あるのかという話があったので試してみました。

use strict;
use warnings;

use DBI;
use Benchmark qw(:all);

my $count = 100;
my $loop  = 100;

unlink('test.db');
my $dbh = DBI->connect('dbi:SQLite:dbname=test.db');
$dbh->do(
    "CREATE TABLE test (id int not null, name text not null, primary key(id))"
);
$dbh->do('BEGIN');
for (1 .. $loop) {
    $dbh->do("INSERT INTO test(id, name) VALUES($_, 'name_$_')");
}
$dbh->do('COMMIT');

cmpthese(
    $count,
    {
        commit_each_select => \&commit_each_select,
        commit_bulk_select => \&commit_bulk_select,
    }
);

sub commit_each_select {
    for (1 .. $loop) {
        $dbh->do("SELECT * FROM test WHERE id = $_");
    }
}

sub commit_bulk_select {
    $dbh->do('BEGIN');
    for (1 .. $loop) {
        $dbh->do("SELECT * FROM test WHERE id = $_");
    }
    $dbh->do('COMMIT');
}

結果は

                     Rate commit_each_select commit_bulk_select
commit_each_select 59.2/s                 --               -16%
commit_bulk_select 70.4/s                19%                 --

多少効果が見られます。

ちなみに、両テストともデータベースとしてon memoryな:memory:を指定するとinsertは20%、selectは2%程度の向上しかみられませんでした。