30 December 2009

(SQLite + Perl) prepare関数とcommitによる劇的速度向上

PerlのDBIモジュールを用いてSQLiteデータベースにINSERTを行う場合、prepare関数とcommitを適切に使うことで大幅に実行速度が向上する。

ポイントは次の2点

(1)prepareのプレースホルダを使い、ステートメントを使いまわす

(2)AutoCommitをOFFにして、明示的にcommitを行う

特に、(2)の効果は劇的に大きい

■ 実験


(1)と(2)の双方を行った場合
約56000行の追加にかかった時間 : 26.9秒~27.1秒

(2)のみを行った場合
約56000行の追加にかかった時間 : 30.9秒 ~31.9秒

(1)も(2)も行わなかった場合
約56000行の追加にかかった時間 : 651.2秒


■ 実証コード

(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");

(1)と(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; } # 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");

(2)を行わない場合のコード
上の実証コード(緑で着色している部分)からの変更点のみ。

# データベースに接続 $dbh = DBI->connect($strSqlDsn, "", "", {PrintError => 1, AutoCommit => 1}); # または $dbh = DBI->connect($strSqlDsn, "", "", {PrintError => 1}); # $dbh->commit; # この行は不要 # $dbh->rollback; # この行は不要

■ 参考資料
CPAN DBI
SmartSmart Perl講座 第5章 データベースハンドル