【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まで。