SQL ServerのSQLのミスリード

なんか無性に腹が立ったので記事化。
問題は、以下のページ、
http://japan.internet.com/developer/20070206/26.html
のヒント9のもの、SQLで、COALESCEで条件分岐させるもの。
この解説で、
「この方法は、クエリの対象行が数百万に及ぶ場合でも、きわめて高速に動作します。」
なんていっているけど、絶対そんなわけがない。
このSQLは数百万行ある場合には絶対に書いては駄目なSQL。この筆者は一体何を考えているのだろう。
この前のプロジェクトでもCOALECSEを積極的に使おうというものがあって、結局性能が出ずに、結合テスト終盤でSQLを直したという苦い経験があるので、こんな記事を見ると本当にすごく嫌な気分になる。

詳しくは、「インサイドSQLServer 2005 T-SQL編」の8.4.動的SQLの使用方法のP.419に書いてあるんだけど、まとめると以下の２点。

・COALESCEを使うと、引数がNULLのときも値を指定したときも、同じ実行プランとなり、引数がNULLの場合、非常に効率が悪い実行プランで実行してしまう。
・実行プランを組み立てた後、変数に値を代入するので、OPTION RECOMPILEをつけて、毎回実行プランを作り直したとしても、まったく同じ効率の悪いSQLを作ってしまう。

要は、SQL ServerではCOALESCEはアドホックな使い方か、小さなテーブルしか使っては駄目で、プログラム内に書くのは良くないっていうこと。


上のページのSQLであれば、SELECT文のところ面倒でもCOALESCEを使わずに以下の組み立てSQL文にするべきです。

DECLARE @SQL AS NVARCHAR(4000);
SET @SQL =
N'SELECT * FROM CUSTOMERS WHERE 1=1'
+ CASE WHEN @PrmFirstName IS NOT NULL THEN N' AND FirstName = @PrmFirstName' ELSE '' END
+ CASE WHEN @PrmLastName IS NOT NULL THEN N' AND LastName = @PrmLastName' ELSE '' END
+ CASE WHEN @PrmAddress IS NOT NULL THEN N' AND Address = @PrmAddress' ELSE '' END
+ CASE WHEN @PrmCity IS NOT NULL THEN N' AND City = @PrmCity' ELSE '' END
+ CASE WHEN @PrmState IS NOT NULL THEN N' AND State = @PrmState' ELSE '' END
+ CASE WHEN @PrmZip IS NOT NULL THEN N' AND Zip = @PrmZip' ELSE '' END;

EXEC sp_executesql @SQL,
N'@PrmCity AS varchar(50), @PrmState AS varchar(50), @PrmZip AS varchar(50),@PrmFirstName AS varchar(50), @PrmLastName AS varchar(50), @PrmAddress varchar(50)',
@PrmCity = @City, @PrmState = @State, @PrmZip = @Zip, @PrmFirstName = @FirstName, @PrmLastName = @LastName, @PrmAddress = @Address;


前のプロジェクトは、以下のように、さらに悪い部分一致をしていました。こちらも、リリース前に直しが発生しました。

where Address like '%'+@keyword+'%'
で、@keywordの指定が無い場合、@keywordに空文字('')を指定すること


開発効率最優先じゃなくて、効率も気を留めておかないと非機能検証で、すべてがご破算になっちゃうよぉ。