ロジック

【ビジネス奮戦記】

その他の部屋へ戻る

<<< プリペアード・ステートメントのススメ >>>

皆さんはプリペアード・ステートメントという言葉を知っているでしょうか？
並以上の基幹業務アプリ開発者ならば大抵は知っていることだと思います。

知らなかったという人は、
開発業務的にジャンルが違う人か、
経験値が低い新人の人か、
ずっと一つの現場で作業していて色んな現場を渡り歩いてこなかった人か、
技術に関してあまり関心がなく情報収集をおこなっていないかだと思います。

まぁせっかくなので、この機会にワンランクアップさせましょう。
しかし、最初に書いておくと、
どんな物事であれ、全ての状況において絶対の万能薬というものはないということです。
適所に用いてこそ絶大な効果を発揮するということを心のどこかに留めて置いてください。

って、なんだかいつもの自分のキャラからすると、
ずいぶんエラそうで先生のような文体になってしまいました。
実際、キクメンという開発者は世に多くの優秀な開発者がいるなかで、
全然大した事はない、平凡開発者のはしくれの一人であります。

なので、ここでことさらエラそうに世の多くの開発者に啓蒙するなんてのは、
もってのほかのほかなのであります。
なので、自分にとっての備忘録として、書いておくことにします。
聞き手はすぐに仕事の事は忘れてしまうアホメンな自分自身という設定でいきます。

いいかねキクメン君？
ハイ！分かりました！・・ってエラそうにいうんじゃねぇテメェ！
これでも喰らえ！（ボグン！：すぐにぼうりょくで解決するのは良い子じゃないですよ）

いやー客先で「うるせぇ！この分からんちんども！これでも喰らえ！」ってやってみてぇー！

さておき、プリペアード・ステートメントである。
これは一体なんなのかというと、
誤解されるのを恐れず敢えて一言で大袈裟に表現すると、
SQLの実行が速ぇぜ！あぁそうさ、光速の一撃だ！これでも喰らえ！ということです。
（これでも喰らえと光速は余計です）

さて、プリペアー（ prepare ）という英語は「準備します」という意味です。
ステートメント（ statement ）は「声明」という意味です。
（面倒なのでエキサイト翻訳が出典先ね）

で、キクメン的な意訳としては、
「これから何度も同じような SQL を繰り返すから、そのつもりで準備しておいてくれや。」
って感じです。

例えば insert into kikumen_tbl ( col1, col2, col3 ) values ( "キクメン" , "男の中の男" , "1969");
という insert 文を流すとき、たったの１行とか、数百行程度の繰り返しならばいいのですが、
これが、数千、数万繰り返すんだというときはさすがに一行一行処理していたのでは遅いのです。

どうして遅いのかというと、
毎回毎回、「処理して欲しいSQLはかくかくしかじかでござる、おのおのがたこころしてかかれよ。」
と、長ったらしい前文句をたれて、一行ごとに毎回準備させ、毎回処理させるからなのです。

（うーんかなり大袈裟な表現だけどまぁいいや、相手はキクメンだし・・なんだとう！）

で、これを、プリペアード・ステートメントを用いると、
「 SQLの構造自体は変わらないけど、セットする値だけは変わるよん、よろちく～。」
と、前もってコンピューター君に宣言して、準備しておいてもらうわけです。

そうすると、SQL 自体の構造解析（パージング）は最初の１回だけ行って、
以降は値だけをサクサク変えていくのです。

かっこよく言うと、SQL をプーリングしておくなんて言います。
（これとは別の言葉にコネクション・プーリングなんていうのもあります）

もうちょっと難しく書くと、プリペアード・ステートメントは、
以前に最適化され実行された SQL をメモリ上にキャッシュし、
それにより SQL を実行する時に、処理の最適化のための前処理を再び実行する必要がないということです。

＊前処理とは、文法のチェックとか、メモリ上のアドレスの正規化とか、アクセスパスや実行計画の最適化などです。

全宇宙知的生命体にも分かりやすい例え話で例えるならば・・・

マスターグレードの嬉しそうな人を１００個買ってきたぞ！
（嬉しそうな人はココを押して参照）

そうだ、嬉しそうな人を１００個も買ったんだ！
ようーし、これから１００個作るぞォ～！

今日はキクメングレイの色で塗るぞ！
よーし出来た！相変わらず素晴らしい出来だ！さすがキクメングレイ！いい色だ！
（キクメングレイのサンプルはココをクリック！）

よし、明日はキクメンダークグリーンで塗るぞ！
よーし出来た！相変わらず素晴らしい出来だ！さすがキクメンダークグリーン！いい色だ！
（キクメンダークグリーンのサンプルはココをクリック！）

よし、明日はキクメンダークブルーで塗るぞ！
よーし出来た！相変わらず素晴らしい出来だ！さすがキクメンダークブルー！いい色だ！
（キクメンダークブルーのサンプルはココをクリック！）

・・と繰り返しているうちに、

総パーツ点数1000個以上の、上級者向けの製作が大変なプラモデル、
「マスターグレード・嬉しそうな人」も、（そんなもん販売されてません）
日々を追うごとにだんだん作るのにも慣れてきて、
２個目、３個目、４個目・・・と作るに従って、完成まではメッチャ早くなっていくというものです。

これだ！これは分かりやすい！実に分かりやすい例え話だ！
さすがオレ！、常にオレ！、最後はオレ！

これなら恐らく地球から４５億光年のはるかかなたの知的生命体、
「スッバラシィ星人」も 0.5 秒の速さで理解してくれたことだろう。
（分からねぇよ！ってか、嬉しそうな人もスッバラなんとか星人とかも分からねぇよ！）

おかしいなぁ・・・

そういうわけで、（どういうわけよ？）プリペアード・ステートメントは、
同じ SQL をグルグル回す時に便利なんだなっていうことで、
ガッテンしてくれたでしょうか？（ by NHK ためしてガッテン）

ガッテン！ガッテン！ガッテン！（何回押してもガッテンは一回！）

大体においては、インサート処理、アップデート処理、
また、WHERE 句の値だけが入れ替わる SELECT 、 DELETE 文などでも効果的であります。

つまりあるグラス（コップ）があって、そのグラスは変わらないんだけど、
中身が、ワインだったり、オレンジジュースだったり、コーラだったり、
お味噌汁だったり（味噌汁はグラスには入れねェだろ！）するけど、
そのつど、グラスを新しいものをもってきて、その新しいグラスに入れるということはしないのであります。

うわ、でも味噌汁入れた後にカルピスを入れるのだけは勘弁だなぁ・・・
（だから、味噌汁はグラスには入れねェだろ！っていうか、まだ例え話してたんかい！）

まぁそんな感じ。

ガッテンしてくれたでしょうか？（ by NHK ためしてガッテン）

（いや、ガッテンはもういいんだって。ループ処理かお前は。）

で、このプリペアード・ステートメントは、
JAVA にもあるし、VB にもあるし、Perl にもあります。
（というより正確にはその言語用のデータベース・ドライバが持っているということですな。）

ではちこっと書いてみましょう。

まずは普通の SQL 実行部分
（言語は Perl ね）

これからインサートするデータはCSVファイルに収められていると仮定したプログラム

  while (<FH>) { # ファイルの最後までループするよ～
    @data = split(/,/, $_); # カンマ区切りの１レコードをバラすよ～
    # ↓上でバラしたデータを使ってSQLを作るよ～
    $sql = "insert into TEST_TABLE ( NAME, SEX , AGE ) values( '$data[0]', '$data[1]', '$data[2]') ";
    eval{
      $sth = $dbh->prepare( $sql );
      $rv = $sth->execute;
    };
    if( $@ ){ # SQL を実行したらエラーが発生しちゃったよ～
      # なんらかのエラー処理をするよ～
    }
  }

さて、ここでのミソはループの中で $sql という文字列変数を使って、
SQL を毎回作っちゃってる点であります。

より具体的にいうと、
毎回「 $sth = $dbh->prepare( $sql ); 」を実行している点がミソなのです。
これがバカイヤン！なのであります。

で、これをループの外に出しちゃうわけです。

プログラムはこうなります。

  # 先にあらかじめ SQL を準備させるよ～
  # その際、? （ ハテナ、クエスチョンマーク ） のところに値が入るからね～
  $sth = $dbh->prepare( "insert into TEST_TABLE ( NAME, SEX , AGE ) values( ?, ?, ?) " );

  while (<FH>) { # ファイルの最後までループするよ～
    @data = split(/,/, $_); # カンマ区切りの１レコードをバラすよ～
    # 上でバラしたデータだけ（しかも配列を直接）渡して実行しちゃうよ～
    eval{
      $rv = $sth->execute(@data);
    };
    if( $@ ){ # SQL を実行したらエラーが発生しちゃったよ～
      # なんらかのエラー処理をするよ～
    }
  }

どうでしょう。
ループの中はかなりスッキリカンコンミカコンコンしたと思います。

（ミカコンコンというのは酔っ払った私の父親が長女を呼ぶときのセリフで、
姉はこれがたいそうキライだったそうです。ってどうでもいいぜそんな話は！）

JAVA とか VB もたいていこれに準じていて、
大体、変動する値のところの表現に？を使います。

さて、その効果たるや、
データ件数、処理内容、マシンスペックにもよりますが、
結構劇的な効果が得られますので、パフォーマンス改善の１策として、
頭のどこかに置いておくといいでしょう。

そんなわけで、今回はこれでおしまい。

その他の部屋へ戻る