株式会社ジーワンシステムの代表取締役。 新しいものを生み出して世の中をあっといわせたい。イノベーションってやつ起こせたらいいな。

【SQL講座2】スカラ値ファンクションとパフォーマンス

»

 前回、法人格を揃えるためのファンクションをAccess、Excelで作りましたが、今回はそのパフォーマンスについてです。

 On Error で法人格マスタの読み込みは1回になるようにしていますが、そもそもVBAの関数というのはC++で書かれた組み込み型のファンクションより遅いです。

 他のRDBMSでも同じことが言えるようでファンクションは遅いです。前回、

コードの途中にメッセージボックスを仕込むと、どのタイミングで関数が評価(再計算)されるかよく分かります

と書きましたが、メッセージボックスを仕込んで実行してみてください。

 WHERE句で利用して他の抽出条件がなければ、テーブルの全件数回。

 Excelで自動計算しない設定にしていなければ、どこか1つのセルでも修正したタイミングで、セルに使った個数と同じ回数実行されることが分かります。

 ちなみに、DOS版のころは、自動計算なんて遅すぎて使い物にならなかったので、F9キーで自動計算するようにしていました。つまり、CPUがV30 12MHz、メインメモリーが640KByteくらいのときにできていた、いにしえのテクです。マシンが遅いのでギリギリまで効率的な処理を目指したおかげで、わたしは直感的に分かるようになったのでしょうけれど。

 それはさておき、とにかく、SELECT句に置くサブクエリも同じですが、

出力される行数 × 使ったファンクション(サブクエリ)の数

実行されるということを覚えてください。サブクエリも出力されるレコード件数と同じ回数、実行されるわけです。

 以前、とあるプロジェクトでスカラ値ファンクションを教えたときに、「JOINより使いやすい!」と言い出して、すべてのマスターに関数を作り出してしまったことがあります。

 例えば、以下のような関数です。

fn得意先(キー, カラム名 OR カラム位置)

とすれば、JOINしなくても名称などを取れます。

 戻り値が数値のときは文字型にしてたのかな……。

 確かに取れます。ライブラリを読み込んでSQLを動的生成すれば汎用なものも作れますけれど……。

 SQL文にすると、以下みたいなものが

 SELECT
   jh.受注NO
   , jh.得意先ID
   , tm.得意先名
   , tm.住所
   , tm.電話番号
   ……
 FROM
   受注トラン jh
   INNER JOIN 得意先マスタ tm
     ON jh.得意先ID = tm.得意先ID
 WHERE
   ……

以下のようになります。

 SELECT
   jh.受注NO
   , jh.得意先ID
   , fn得意先(jh.得意先ID, '得意先名') AS 得意先名
   , fn得意先(jh.得意先ID, '住所') AS 住所
   , fn得意先(jh.得意先ID, '電話番号') AS 電話番号
 FROM
   受注トラン jh
 WHERE
   ……

 この方が分かりやすいですか?

 分かりやすいと喜んでいた人がいたので、他の言語に慣れている人はこちらの方が楽なのでしょうかね。

 しかし、オーバーヘッドの大きな関数が大量に実行され、得意先のマスタの読み込み回数が3倍以上になってしまいますから、場合によっては大変なパフォーマンスの問題がおきます。やらないようにしてください。

 もっとも、UI側でデータアクセスオブジェクトなんて作ったら、同じことをネットワークを挟んでやっちゃうので、それよりは遙かにマシですけどね。

 他に集計するもの(ファンクション・サブクエリ)についても、FROM句に書くよりも、SELECT句に書いた方が読み易いことは多いです。FROM句に書いたときは、JOINするときにインデックスが使えない、という欠点がありますので、何度も実行されるか、結合にインデックスを使わなくても一括して集計した方が良いか、判断が必要になります。

 この辺は、ExcelでDBEngineの動作を確認すると、どちらが良いか、ある程度分かるようになります。実際には両方書いて良い方を選ぶことが多いですけれど、予測できるようになった方が良いでしょう。

 今日はここまで。

 ハンズオンのセミナーをやることになりました。第一回目は5月15日に大阪です。詳しくはこちら

 ご興味のある方はinfo@g1sys.co.jpまで。

Comment(0)

コメント

コメントを投稿する