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


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

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

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

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

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

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2013/03/11 09:42:47
  • 終了:2013/03/18 09:45:04

回答(2件)

id:oil999 No.1

oil999回答回数1728ベストアンサー獲得回数3202013/03/11 10:22:37

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

WHERE COLA='A' AND COLB='B' COLC='C' AND...
他1件のコメントを見る
id:oil999

ISやORを使うのはコストに跳ね返ってきますね。

しかし、検索コストを考えてDB設計を見直すしか方法はありません。
そもそも、COLBが検索条件になりかつNULLを許しているというのは、DB設計上の問題があります。

2013/03/11 11:59:28
id:Sampo

あ、失礼。COLBはNULLを許していません。

@bに検索条件が指定されたなかった、ですから
WHERE COLA=@a AND (@b IS NULL OR COLB=@b) AND COLC=@c AND...

ですね。
ともあれ、そのDB設計の見直しですが、どういうのがいいやり方ですか? というのが今回の質問です。問題があることは明らかですので。

2013/03/11 12:13:42
id:oil999 No.2

oil999回答回数1728ベストアンサー獲得回数3202013/03/11 17:57:41

ポイント200pt

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

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

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

id: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の結果を返すものとして。

この手法、ちょっと掘り下げてみたいです。
パターン名なんかはついていませんかね?

2013/03/11 18:13:22
  • id:taknt
    大なり小なりの場合は、条件の指定があるときは、それぞれ指定して、なければ 最小値と最大値を指定してやればいい。
    イコールの場合は、大小、同じ値をセットしてやる。
  • id:taknt
    フラグを作ってそのフラグで判断させてやればいいかな。

  • id:Sampo
    なるほど…イコール条件であってもあえてBETWEENではさみうつ記述ですか。

    それはなるほどと思えます。
  • id:cno
    ManagementStudioが使用出来る環境であれば、実行プランの表示が参考になるかと思います。

    プログラム上にデバッグ用として、よく発行されるであろうSQLを出力させ、
    実行プランを表示させると、推奨するインデックスや、負荷が大きい箇所がある程度
    割り出せるため、それに応じてクラスタ化インデックスなどを調整することで
    改善できたりしたことがありました。

    参考になるページは
    http://www.atmarkit.co.jp/fdb/rensai/10_drk/06/drk02.html

    http://engineermemo.wordpress.com/2012/07/08/management-studio-%E3%81%A7%E5%AE%9F%E8%A1%8C%E3%83%97%E3%83%A9%E3%83%B3%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%97%E3%81%9F%E9%9A%9B%E3%81%AE%E3%82%B3%E3%82%B9%E3%83%88%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/

    http://msdn.microsoft.com/ja-jp/library/ms178071%28v=sql.105%29.aspx
    あたりでしょうか?

  • id:Sampo
    Management Studioを使っています。
    これで実行プランを見ると、やはり(@b IS NULL OR col_b=@b)方式だとインデックスが使えずフルスキャンになってしまっているのがわかります。

    そこで、アプローチの仕方自体を変えるべきなのかと今回の質問となりました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません