2014年7月2日水曜日

SQLでユーザー関数は要注意!

久しぶりのお仕事系技術ネタ。相変わらずマイクロソフトのSQL Serverを使っているのですが、WHERE句にユーザー関数を使うのは危険だ!って話です。ふと思い出したので書いてます。

※当ブログは技術系ブログとは違います。こんな話題は滅多にありません。他は何の参考にもならない、愚にもつかない記事達です。すみません。

※過去に書いた仕事系技術ネタを検索してみました。あまり参考にはならんと思いますが(^_^;)

カモノハシ通信3: 32bitのJP1から64bitでバッチを実行する
カモノハシ通信3: MS SQL ServerでDB間連携
カモノハシ通信3: ORA-00922
カモノハシ通信3: VB6は嫌いだ
カモノハシ通信3: 妻のためにアプリ作ったよ

SQLの知識ですが、こういうのって知ってる人からすれば「常識」なのですが、知らない人からしたらビックリ(゚д゚)!で、しかもシステム全体のパフォーマンスに大きく関わることなので影響もデカイという結構重大な話。そういう重大な基礎知識を持っている人がプロジェクトに何人いるかが、開発にはとっても大事なような気がします。あと知ってる人が見て「気になった」ときに、それをチームで共有するだけの度量と親切心があるか、もね。

結論から言うと、WHERE句にユーザー関数を使うとインデックスが効かない可能性があるということです。

例えば、昨日の日付を返す「get昨日の日付()」みたいなユーザー関数を作ってあったとします。普通そんな愚かな関数は作らないと思いますが、実際には例えば営業日を勘案したりなんだかんだで似たような関数を作るケースはあるかも。

そして、システムからこんなSQLを呼び出したとします。

SELECT ほにゃほにゃ FROM なんとか WHERE 日付=get昨日の日付()

もちろん正常に結果を返してくれますが、パフォーマンス的にこれはアウツ。なぜか?「日付」項目にインデックスが貼ってあったとしてもインデックスが効かないからです。

インデックスとは検索を効率化するための索引で、例えば「日付」で検索することが多いのならば予め「日付」をインデックスに設定しておくことでスピードは段違いに速くなりえます。書籍に例えるなら本文全部を頭から検索するよりかは、インデックス=目次であたりをつけてから検索する方が圧倒的に効率的なのと同じです。

ですが右辺にユーザー関数が使われていると「これはなんか動的な値が来るのかな?」とDBが勘違いして(本当か?)インデックスを見てくれません。知ってました?

これが例えば

SELECT ほにゃほにゃ FROM なんとか WHERE 日付='2014-06-30'
とか
SELECT ほにゃほにゃ FROM なんとか WHERE 日付=DATEADD(day,-1,@日付)
※@日付にはdatetimeが入ってる

ならばインデックスが効いて高速に検索することができます。DATEADDは決定的組み込み関数なのでセーフです。

WHERE DATEADD(day,-1,日付)=@日付
とか
WHERE TO_CHAR(日付,'YYYYMMDD')='20140630'
とか
WHERE ほげほげ IS NOT NULL
とか
WHERE ほげほげ <> 'abc'
なんか
で、インデックスが効かないのは、熟知してる人も多いと思いますが、ユーザー定義関数を右辺に使った場合もアウトなのは意外と盲点かと思います。

ちなみに左辺に計算式を使いたいのならば計算列に対するインデックス付与とかビュー・インデックスなんかを検討すべきでしょう。

WHERE句右辺のユーザー関数をやめるだけでクエリーのパフォーマンスが数十倍、数百倍になるケースだってきっと多々あることでしょう。知らずに作り上げてしまったとしたら、けっこう恐ろしいことです。(今後パフォーマンス改善の余地が大いにあるのでラッキー!という考え方もありますが(^_^;))

以上、たまーに書く技術系のネタ(と言うのもおこがましい程度ですが)でした。ではまた。

この本は基本Oracle Exadataの宣伝ですが(^_^;)、考え方で色々と参考になることも多かったです!おすすめ。

0 件のコメント :

コメントを投稿