PerlのDBIモジュールを用いてSQLiteデータベースにINSERTを行う場合、prepare関数とcommitを適切に使うことで大幅に実行速度が向上する。
ポイントは次の２点
（１）prepareのプレースホルダを使い、ステートメントを使いまわす
（２）AutoCommitをOFFにして、明示的にcommitを行う
特に、（２）の効果は劇的に大きい。
■ 実験
（１）と（２）の双方を行った場合
約５６０００行の追加にかかった時間 ： 26.9秒～27.1秒
（２）のみを行った場合
約５６０００行の追加にかかった時間 ： 30.9秒 ～31.9秒
（１）も（２）も行わなかった場合
約５６０００行の追加にかかった時間 ： 651.2秒
■ 実証コード
（２）のみ行うコード （行ごとにprepareを構築する）
#!/usr/bin/perl
use strict;
use DBI;
use Time::HiRes;
my $dbh = undef;
my $sth = undef;
my @arr; # CSVファイルの行データを切り分けた配列
my $tmQueryStartTime = undef; # クエリ開始時刻
my $strSqlDsn = 'DBI:SQLite:dbname=./test.sqlite'; # DSN
$tmQueryStartTime = Time::HiRes::time(); # 開始時間を保存
eval{
# データベースに接続
$dbh = DBI->connect($strSqlDsn, "", "", {PrintError => 1, AutoCommit => 0});
if(!$dbh){
print("error : database open error\n");
exit;
}
open(IN, "< TestFile.csv");
while(<IN>) {
$strTmp = $_;
@arr = split(/,/, $_);
# SQL文を構築する
$strQuery = "insert into acctbl values(null,'".$arr[0]."','".$arr[1]."','".$arr[2]."','".$arr[3]."','".$arr[4]."','".$arr[5]."','".$arr[6]."','".$arr[7]."')";
$sth = $dbh->prepare($strQuery);
# SQLを発行する
if($sth){ $sth->execute();}
if($sth){ $sth->finish();}
}
$dbh->commit;
close(IN);
};
if($@){
# evalによるエラートラップ：エラー時の処理
$dbh->rollback;
$dbh->disconnect();
print("Error : ".$@."\n");
exit();
}
print("Lapse time : "(Time::HiRes::time() - $tmQueryStartTime)." sec\n");
（１）と（２）を行うコード （prepareのプレースホルダを利用して、ステートメントは再利用する）
#!/usr/bin/perl
use strict;
use DBI;
use Time::HiRes;
my $dbh = undef;
my $sth = undef;
my @arr; # CSVファイルの行データを切り分けた配列
my $tmQueryStartTime = undef; # クエリ開始時刻
my $strSqlDsn = 'DBI:SQLite:dbname=./test.sqlite'; # DSN
$tmQueryStartTime = Time::HiRes::time(); # 開始時間を保存
eval{
# データベースに接続
$dbh = DBI->connect($strSqlDsn, "", "", {PrintError => 1, AutoCommit => 0});
if(!$dbh){
print("error : database open error\n");
exit;
}
# SQL文を構築する
$strQuery = "insert into acctbl values(null,?,?,?,?,?,?,?,?)";
$sth = $dbh->prepare($strQuery);
open(IN, "< TestFile.csv");
while(<IN>) {
$strTmp = $_;
@arr = split(/,/, $_);
# SQLを発行する
if($sth){ $sth->execute($arr[0],$arr[1],$arr[2],$arr[3],$arr[4],$arr[5],$arr[6],$arr[7]); }
}
$dbh->commit;
close(IN);
};
if($@){
# evalによるエラートラップ：エラー時の処理
$dbh->rollback;
$dbh->disconnect();
print("Error : ".$@."\n");
exit();
}
print("Lapse time : "(Time::HiRes::time() - $tmQueryStartTime)." sec\n");
（２）を行わない場合のコード
上の実証コード（緑で着色している部分）からの変更点のみ。
# データベースに接続
$dbh = DBI->connect($strSqlDsn, "", "", {PrintError => 1, AutoCommit => 1});
# または
$dbh = DBI->connect($strSqlDsn, "", "", {PrintError => 1});
# $dbh->commit; # この行は不要
# $dbh->rollback; # この行は不要
■ 参考資料
・ CPAN DBI
・ SmartSmart Perl講座 第5章 データベースハンドル
