RDBで動的検索条件が必要な場合、動的SQLを利用すべき理由

SQL ServerのOPTION (RECOMPILE)や、他DBの同等機能の利用で十分なケースも多いでしょうが、それらを利用せずに静的なSQLだけで解決するのは良くないよと言うお話です。

RDBを利用した業務アプリを作っていたりすると、次のようなケースはよくあると思います。

• 検索画面に複数の条件を入力するコントロールが並んでいる
• 検索条件の入力は任意であり、どの項目が入力されるか、されないかは、利用者の操作によって決定される

こういった場合、データベースへの問い合わせ式的には大きく次の二つに分けられると思います。

1. 検索条件の入力値に合わせてSQLを動的に生成して実行する
2. 検索条件の入力有無に依存しない静的なSQLを利用して実行する

後者の静的SQLは例えば次のようなSQLのことを指します。
ここではSQL Servarのサンプルデータベースである、AdventureWorksのSalesOrderDetailテーブルを例にとって説明していきます。

SELECT 
    SalesOrderID,
    ProductID
FROM 
    Sales.SalesOrderDetail
WHERE
    (SalesOrderID = @SalesOrderID OR @SalesOrderID IS NULL)
AND   (ProductID = @ProductID OR @ProductID IS NULL)

なおSalesOrderIDもProductIDもいずれもインデックスが作成済みな列です。
このように実装することで、例えば「@SalesOrderID」がNULLの場合

OR @SalesOrderID IS NULL

の部分が常に正となるため、正しく動作します。
このパターンは実装や単体テストのコストが、動的SQLを生成するケースと比較して大幅に削減できてるため、一見良いパターンのように思えます。
しかしこの静的SQLは動的SQLに比較して、速度的に非常に不利です。

次のようにSQLを実行して、実行時の実際の実行計画を見てみると、その理由はすぐに判明します。

DECLARE @SalesOrderID int = NULL
DECLARE @ProductID int = 978


SET STATISTICS PROFILE ON
SELECT 
    SalesOrderID,
    SalesOrderDetailID,
    ProductID
FROM 
    Sales.SalesOrderDetail
WHERE
    (SalesOrderID = @SalesOrderID OR @SalesOrderID IS NULL)
AND   (ProductID = @ProductID OR @ProductID IS NULL)

@SalesOrderIDがNULLで、@Productが指定されており、Product列はインデックスが作成されているため、IndexをSeekして結果が取得されることを期待するでしょう。
このSQLを実際に実行した際の実行計画が次の通りです。

赤線で囲った範囲に注目してください。
期待しているのはIndex Seekですが、Index Scanになってしまっています。
Index Scanの場合、インデックス内の項目を全て舐める動作になっており、行の特定にインデックスが働いていないことが見て取れます。
ちなみに、次のようなSQLを実行すると、ちゃんとIndex Seekになります。

なぜ静的なSQLではIndex Seekではなく、Index Scanになってしまうのでしょうか？これはSQL Serverのクエリの実行プロセスを考えると理解することができます（他のDBでも多くは似たようなものでしょう） 。

SELECT文を実行する場合、通常は次のような手続きを踏んで実行されます。

1. 発行されたクエリから、作成済みでキャッシュされている実行計画を探す
2. キャッシュされている実行計画がなかった場合、クエリオプティマイザにより実行計画が作成される
3. 実行計画にクエリパラメーターを適用して検索を実行する

実行計画には検索処理の手続きが定義されています。
ざっくり言うと、次の手続きを複数含んでいます。

1. どのテーブルを処理するか？
2. そこから、どうやってデータを抽出するか？（インデックスを使うか使わないか？SeekかScanか？）

一度SQLが実行されると、適正な期間実行計画はキャッシュされ、同じSQLが発行されると実行計画を再利用します。

ポイントは「実行計画はクエリーパラメーターを適用する前に作成される」と言う点にあります。実行計画をキャッシュして再利用する都合上、そう実装されていることは極めて自然な設計でしょう。
しかし、その点に静的SQLでインデックスが効かずScanになってしまう理由があります。

それでは改めてSQLを見てみましょう。
Where区を説明のため一列のみに変更しています。

SELECT 
    SalesOrderID,
    ProductID
FROM 
    Sales.SalesOrderDetail
WHERE
    ProductID = @ProductID OR @ProductID IS NULL

このSQLから実行計画を作成する場合、まだパラメーターが確定されていません。

@ProductIDがNULLではない（検索条件が指定された）場合、IndexをSeek（対称行のみ探索）して結果を絞り込めます。
しかしNULLの場合、全行を返す必要があるためIndexをScanする（全行舐める）のが適切です。

そして、NULLでもNULLじゃない場合でも、どちらでも動作する実行計画を作成するためには、SeekではなくScanせざるを得ないわけです。

と言うわけで、この解決策はSQL Serverの場合、二つの手段があります。

1. Where区を動的に生成する（SQLインジェクションに注意）
2. OPTION (RECOMPILE)を利用する

前者の場合、検索条件の指定の有無の組み合わせ分（だいたい2の検索項目数乗）の実行計画が作成される可能性がありますが、キャッシュが効く可能性があるため性能的に多くの場合有利です。しかし実装と単体テストの手間が膨らみます。

後者の場合、クエリの発行のつどクエリオプティマイザにより、その時発行されたクエリパラメーターを加味した最適な実行計画が作成されます。そのため（利用可能なインデックスがあれば）Index Seekにはなりますが、毎回クエリオプティマイザが動作するため前者に比較して遅いケースが多いです。とはいえ、ケースによっては気になるレベルではないかもしれません（実際、大量の同時リクエストが発生しない場合、体感はできないでしょう）。
使い方はこんな感じです。

SELECT 
    SalesOrderID,
    ProductID
FROM 
    Sales.SalesOrderDetail
WHERE
    ProductID = @ProductID OR @ProductID IS NULL
OPTION (RECOMPILE)

簡単ですね。

いずれもメリット・デメリットありますので、利用シーンに合わせて、いずれかを利用し単純な静的SQLのみでの利用は避けた方が良いかと思います。

参考資料 https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://www.sommarskog.se/dyn-search-2008.html

以上、今日はここまで！