none
ISNULL関数の結果の列に対して条件検索を行うと検索時間が異様に掛かるようになる RRS feed

  • 質問

  • データベース:SQL SERVER 2008 R2

    複数のテーブルを外部結合しているビューで、
    ISNULL関数を使用している列(例の場合は[仕入日])に対して条件を範囲指定して検索した際、
    不特定のタイミングを境に急激に検索に時間が掛かるようになります。
    (データ件数増加が関係していると見込んでいますが、境界の件数などの正確な調査は実施できていません)

    ただし、WHERE句を外した全件検索した際は時間が掛かりません。
    同様に、範囲指定を狭めて検索したり、逆に範囲指定を広げて検索しても、
    (1秒以内に検索できていたものが、1分以上掛かるようになります)


    【ビューの例】※ 実際には10テーブルほどを外部結合しています
    CREATE VIEW 仕入商品
    AS
    SELECT A.ID
         , A.商品コード
         , A.商品名
         , ISNULL(B.仕入日, A.仕入日) AS 仕入日
    FROM   A
           LEFT OUTER JOIN B
           ON B.商品コード = A.商品コード
    ;

    ※ テーブルはA、B共に下記のようなレイアウトとし、データ量は日々増えていくものとします。
      ID int not null
      商品コード nvarchar(10)  not null
      商品名 nvarchar(50) not null
      仕入日 datatime


    【検索SQL文例】
    SELECT *
    FROM   仕入商品
    WHERE  仕入日 >= '2016/12/01'
    AND    仕入日 <= '2017/05/31'
    ;


    下記解決方法を試みましたが、いずれも解決に至りませんでした。
     ・条件指定を BETWEENへ変更
     ・[仕入日]のISNULL関数をCASE文へ変更
     ・ビュー[仕入商品]を副問い合わせして検索
        【検索SQL文例】
        SELECT TEMP_仕入商品.*
        FROM   (SELECT * FROM 仕入商品) AS TEMP_仕入商品
        WHERE  TEMP_仕入商品.仕入日 >= '2016/12/01'
        AND    TEMP_仕入商品.仕入日 <= '2017/05/31'
        ;
     ・ビューをテーブル値関数化
     ・ビューを不使用し、テーブルA、Bどちらの[仕入日]に対しても範囲指定する

    もしご存知の方がおられましたら、どうぞご教示下さい。
    2017年5月11日 5:37

すべての返信

  • 直接の回答ではありませんが、例えば以下のように書くと速くなりますでしょうか?
    また、実行プランは確認されていますでしょうか?

    SELECT A.ID
         , A.商品コード
         , A.商品名
         , ISNULL(B.仕入日, A.仕入日) AS 仕入日
    FROM   A
           LEFT OUTER JOIN B
           ON B.商品コード = A.商品コード
    WHERE  ISNULL(B.仕入日, A.仕入日) >= '2016/12/01'
    AND    ISNULL(B.仕入日, A.仕入日) <= '2017/05/31'


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年5月11日 5:53
  • 補足致します、さらに謎が深まってしまうのですが、[仕入日]の条件範囲を広げると検索時間が短くなりました。。
    条件範囲のパターンをいくつか試してみたところ、下記のようになりました。
     1.'2016/09/26' ~ '2017/05/31' 1秒
     2.'2017/01/01' ~ '2017/05/31' 45秒
     3.'2016/10/01' ~ '2016/10/31' 1分超
     4.'2016/09/01' ~ '2017/01/31' 1秒

    2より1、3より4の方が条件範囲が広いにも関わらず、検索時間が圧倒的に速くますます混乱してしまっています。。

    同様の現象を経験された方がおられましたら、解決方法をぜひご教示ください。
    どうぞよろしくお願い致します。
    2017年5月11日 5:57
  • trapemiyaさん、ご対応くださりどうもありがとうございます。

    ご提示いただいたSQL文での検索は試行しましたが、検索時間は遅いままでした。(記述漏れておりました、申し訳ございません)
    実行プランも確認中なのですが、外部結合テーブルが多いため特定できずにいます。
    引き続き実行プランを確認致します。
    2017年5月11日 6:03
  • 関数の引数にはインデックスが使われないということなので、IsNullではなくて、is nullで判断した方が良いとのことです。
    これで試してみるとどうなるでしょうか?

    What makes a SQL statement sargable?
    http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable

    (追記)
    あと、IsNullの計算列を作って、そこにインデックスを貼るということも提案されています。

    there is any Performance issue while using ISNULL() in SQL Server?
    http://stackoverflow.com/questions/8924913/is-there-is-any-performance-issue-while-using-isnull-in-sql-server


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/


    2017年5月12日 0:19
  • trapemiyaさん、ご教示頂きどうもありがとうございます。

    実行プランの調査に手間取っており、調査結果を出せず恐縮です。

    なるほど、is null使用するべきなのですね、確かに関数にはインデックスが効かないため効果がありそうです、試行してみます。

    ただ、[仕入日]の範囲を変えると不規則に検索時間が変わることも気になっており(当方の2番目の投稿『補足致します~』に記載)、

    範囲が変わると実行プランが変わるのではとの推測もあり、実行プランの調査がますます重要になっていると考えています。

    ただ、実行プランが見にくく、XMLで出力してテキスト比較も試みましたが行位置がバラバラになっており単純比較できないのが残念です。

    2017年5月12日 1:30
  • 余談です。

    お気付きかとは思いますが、関数だけではなくVIEWに対する条件指定もインデックスがうまく処理されないケースは良く聞きます。

    ※個人的にはVIEWはあまり好んで使わず、テーブル値関数を使うかクエリを毎回生成するかしています。

    2017年5月12日 5:04