人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQLクエリの動的組み立てを避ける方法について。

今使っている処理系はSQL Server 2008ですが、一般的なRDBについての回答でもSQL Serverに限った回答でもかまいません。

レコード検索画面を作るとき、各検索条件が指定されていればそれで絞り込むという動作をさせたいです。よくある要求だと思います。

このとき、検索条件が指定されていればWHERE句にAND式を追加するという動的組み立てをどうしてもせざるを得ません。
動的組み立てを避けて AND(@cond IS NULL OR coldate = @cond)
のような式を書くと実行コストが跳ね上がりますので。

かといって、各条件が指定されているかどうかで分岐してそれぞれカスタマイズされたクエリが呼ばれるようにしようとすると組み合わせ爆発です。

このような場合、どう問い合わせを書くのがいいやり方なのでしょう?


●質問者: Sampo
●カテゴリ:コンピュータ ウェブ制作
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● oil999
●0ポイント

下記の式は左側から評価されるので、途中でfalseになればスキップしますし、想像されているほどコストがかかるものではありません。

WHERE COLA='A' AND COLB='B' COLC='C' AND...

Sampoさんのコメント
お示しの式ならば問題ないのです。 問題はCOLBでの絞込が指定されなかった場合で、 WHERE COLA=@a AND COLC=@c AND... としたければ動的組み立てをせざるを得ず、 WHERE COLA=@a AND (COLB IS NULL OR COLB=@b) AND COLC=@c AND... とすると、実測で実行コストが11倍くらいになりますもので。

oil999さんのコメント
ISやORを使うのはコストに跳ね返ってきますね。 しかし、検索コストを考えてDB設計を見直すしか方法はありません。 そもそも、COLBが検索条件になりかつNULLを許しているというのは、DB設計上の問題があります。

Sampoさんのコメント
あ、失礼。COLBはNULLを許していません。 @bに検索条件が指定されたなかった、ですから WHERE COLA=@a AND (@b IS NULL OR COLB=@b) AND COLC=@c AND... ですね。 ともあれ、そのDB設計の見直しですが、どういうのがいいやり方ですか? というのが今回の質問です。問題があることは明らかですので。

2 ● oil999
●200ポイント

下記サイトの正規化の手順を参考にしてください。
http://jibun.atmarkit.co.jp/lskill01/rensai/db/07/01.html

カラム数がやたら多いテーブルよりは、複数テーブルに分割した方がメンテナンス性が高くなりますし、selectで表示するカラムを明示してやることで、結合のコストは帳消しにできます。

正規化を行っていれば、NULL値を持つような従属カラムは存在しなくなるはずです。(そのようなレコードをinsertする必要がないから)
これで、検索コストやデータ容量の節約になります。


Sampoさんのコメント
なるほど垂直分割…… 参照クエリの姿としては、 FROM メインテーブル LEFT JOIN サブテーブルAを絞り込むかもしれないストアド関数 @a USING(record_id) LEFT JOIN サブテーブルBを絞り込むかもしれないストアド関数 @b USING(record_id) LEFT JOIN サブテーブルCを絞り込むかもしれないストアド関数 @c USING(record_id) ... のようなものをイメージすればいいのですかね。 ストアド関数は、引数がNULLならテーブル内容を単にSELECT、引数がNON-NULLならSELECT-WHEREの結果を返すものとして。 この手法、ちょっと掘り下げてみたいです。 パターン名なんかはついていませんかね?
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ