SQLite で「PRIMARY KEY」を《真のプライマリキー》とするには | tech - 氾濫原 の続きです。
以下のような簡単なベンチマークスクリプトを使って差を測ってみました。
Linux の VPS と OS X とでやってみましたが、だいたいこの例では5%ぐらいの差がつくようでした。
use v5.14;
use utf8;
use DBI;
use DBD::SQLite;
use Benchmark qw(:all) ;
use String::Random qw(random_regex random_string);
sub dbh {
my ($db) = @_;
my $dbh = DBI->connect('dbi:SQLite:dbname=', "", "", {
sqlite_allow_multiple_statements => 1,
RaiseError => 1,
sqlite_see_if_its_a_number => 1,
sqlite_unicode => 1,
});
}
say "DBI::VERSION: $DBI::VERSION";
say "DBD::SQLite::VERSION: $DBD::SQLite::VERSION";
say "SQLite version: ". dbh()->{sqlite_version};
my $dbh_rowid = dbh();
$dbh_rowid->do(q{
CREATE TABLE tfidf (
`id` INTEGER PRIMARY KEY,
`term` TEXT NOT NULL,
`entry_id` INTEGER NOT NULL,
`term_count` INTEGER NOT NULL DEFAULT 0,
`tfidf` FLOAT NOT NULL DEFAULT 0,
`tfidf_n` FLOAT NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX index_tf_term ON tfidf (`term`, `entry_id`);
CREATE INDEX index_tf_entry_id_tfidf_n ON tfidf (`entry_id`, `tfidf_n`);
});
my $dbh_without_rowid = dbh();
$dbh_without_rowid->do(q{
CREATE TABLE tfidf (
`term` TEXT NOT NULL,
`entry_id` INTEGER NOT NULL,
`term_count` INTEGER NOT NULL DEFAULT 0,
`tfidf` FLOAT NOT NULL DEFAULT 0,
`tfidf_n` FLOAT NOT NULL DEFAULT 0,
PRIMARY KEY (`term`, `entry_id`)
) WITHOUT ROWID;
CREATE INDEX index_tf_entry_id_tfidf_n ON tfidf (`entry_id`, `tfidf_n`);
});
say "insert";
{
my $i = 0;
cmpthese(-1, {
'with rowid' => sub {
$dbh_rowid->prepare_cached(q{
INSERT INTO tfidf (`term`, `entry_id`, `term_count`) VALUES (?, ?, ?);
})->execute(random_regex('[a-z]{2,10}'), $i++, 1);
},
'without rowid' => sub {
$dbh_without_rowid->prepare_cached(q{
INSERT INTO tfidf (`term`, `entry_id`, `term_count`) VALUES (?, ?, ?);
})->execute(random_regex('[a-z]{2,10}'), $i++, 1);
},
});
};
say "select";
{
my $i = 0;
cmpthese(-1, {
'with rowid' => sub {
$dbh_rowid->selectall_arrayref(q{
SELECT * FROM tfidf WHERE `term` = ? AND `entry_id` = ?
}, { Slice => {} }, random_regex('[a-z]{2,10}'), $i++);
},
'without rowid' => sub {
$dbh_without_rowid->selectall_arrayref(q{
SELECT * FROM tfidf WHERE `term` = ? AND `entry_id` = ?
}, { Slice => {} }, random_regex('[a-z]{2,10}'), $i++);
},
});
}
