SQLiteでSQL文の実行速度を上げる方法とは？

こんにちは、WPホームページ研究所の運営サポートチーム（@WP_LABO）です。

SQLiteではトランザクション開始やコミット完了を明記しなければオートコミットがONになるのでループ処理に時間がかかります。オートコミットOFFでSQL文を実行すると処理速度が何倍にも改善するのでおすすめです。オートコミットを使うかどうかは処理で決めるといいかもしれません。

トランザクションは「複数のSQL文を１つの処理としてまとめてデータベースに反映すること」を言います。

１. トランザクションを開始
　　　　　　↓
２. 各処理用のSQL文を読み込む
　　　　　　↓
３. 最後にコミットをする

処理の始まりポイントと最終実行ポイントを明記することでオートコミットがオフになるというわけですね。

SQLiteでSQL文の実行速度を上げる方法とは？

SQLiteでSQL文の実行速度を上げる方法とは？

オートコミットをOFFにするにはトランザクションやコミットを追記すればOKです。

トランザクションの開始

以下がトランザクションの開始です。

$db->exec('begin');

トランザクションをコミットする

コミットは下記です。

$db->exec('commit');

ともにループの内側に書くとSQLのたびに実行されてしまうのでループ外に書きましょう。トランザクションを明記してオートコミットをOFFにするだけで10万件のデータ登録が70〜80秒⇒1秒未満になります。

実際の業務でも複数のSQLを実行するならトランザクションを明記しておくと処理が軽くなるので必要に応じてトランザクションからコミットまでの処理を追記（オートコミットを使わない）しておきましょう。

トランザクションをキャンセル

カンタンに言えばトランザクションは「データベース処理中のエラーに対応する仕組み」。必要に応じて合わせて処理をキャンセル（ロールバック）することもできます。下記がロールバックを行う命令文です。

$db->exec('rollback');

PHPの条件分岐で以下を使い分けるようにしましょう。

・問題が発生しなかったらコミットする
・問題が発生したらロールバックさせる

オートコミットなら随時データベースに値が登録されていくので便利な機能ですね。

実際に明記した例をご紹介

以前に書いた「テストデータを10万件登録するSQL」での記述例は下記です。

// トランザクションを開始する
$db->exec('begin');

$id = 1;
$i = 0;
while ($i < 100000) {
	$name = $id;
	$stmt = $db->prepare("INSERT INTO data_base_name(id,name) VALUES (:id,:name)");
	$stmt->bindValue(':id', $id, PDO::PARAM_INT);
	$stmt->bindValue(':name', $name);
	$stmt->execute();
	$id++;
	$i++;
}

// すべてをコミットする
$db->exec('commit');

もし「コミットかロールバックか？」をエラー処理に分けて書くなら条件分岐を足せばOKです。SQLiteでは同時に書き込む可能性がある場合は「コミットのタイミングが重要」となります。仕組みとしてトランザクションの開始ではデータベースファイルはロックされないので状況に応じて処理を書けば安心ですね。