次の方法で共有

パラメータを使うと遅くなる？

  • [アーティクル]

質問

2010年12月14日火曜日 13:08

 

はじめまして。かざど と申します。

奇妙な現象にぶつかってしまったので、皆様の知恵をお借りしようと質問しました。

SQLServer2005(SP3)で、新規データベースを作成し、新規で下記のテーブルを作成しました。

drop TABLE [dbo].[DummyNames]
go
CREATE TABLE [dbo].[DummyNames](
    [id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Kana] [nvarchar](50) NOT NULL,
    [sex] [nvarchar](50) NOT NULL,
    [birthday] [datetime] NOT NULL,
 CONSTRAINT [PK_DummyNames] PRIMARY KEY CLUSTERED (id)
)
go
CREATE NONCLUSTERED INDEX [IX_DummyNames] ON [dbo].[DummyNames] (birthday ASC)
go

 

このテーブルに5000件程度のデータをInsertし、下記のSQLを実行しました。

declare @dt datetime 
declare @dt2 datetime

set @dt='1980/1/1'
set @dt2='1980/1/2'

--Select[1]
select *
from dummynames
where birthday between '1980/1/1' and '1980/1/2'

--Select[2]
select *
from dummynames
where birthday between @dt and @dt2

 

すると、Select[1]の方はbirthdayに作成したインデックスを使用しているのに、

[2]の方は、インデックスを使わないでテーブルスキャン（正確にはクラスタインデックススキャンですが…）になってしまいます。
当然ながら、実行計画のサブツリーコストは5倍の差が出てしまいました。

暗黙の型変換が発生している[1]ではなく、明示的に型を宣言している[2]がインデックスを無視してしまっています。

このようになる原因、解決方法等ご存じの方がいらしたらご教示いただけないでしょうか？

すべての返信 (4)

2010年12月17日金曜日 4:01 ✅回答済み

次の MSDN のブログ（英語）に、ヒントを与えて対処する方法が書かれていました。
これは今回の話に関係ないでしょうか？

SQL Programmability & API Development Team Blog
OPTIMIZE FOR UNKNOWN - a little known SQL Server 2008 feature
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

重要そうと思ったところだけ抜粋します。
表現が違ってたり間違ってたりするかもしれないのでご注意ください。

引用っぽいの開始
Select * from t where col1 > @P1 or col2 > @P2 order by col1

col1：ユニークで常に増加する値、クラスターインデックス
col2：1000 個の異なる値、非クラスターインデックス
t は全部で１千万行

という状況で、実行計画の作成に使用されるパラメータが次の２つの場合を考えると、それぞれの計画結果は大きく異なる。
ケース１：@P1 = 1、      @P2 = 99
ケース２：@P1 = 6000000、@P2 = 550

しかし、SQL Server は、１つのクエリに対して１つの実行計画しかキャッシュしない。

SQL Server 2008 より前でとれる対策は次の通り。
・RECOMPILE ヒントを与える（CPU 負荷が上がる？）。
・パラメータクエリーを使わない。
・OPTIMIZE FOR ヒントを明示する。
・インデックスの使用を強制する。
・プランガイドを使用する。

SQL Server 2008 からは、OPTIMIZE FOR UNKNOWN ヒントを使用するという別の選択肢が提供されている。
これが指定されると SQL Server は、パラメータ値が無い時と同じような標準的な計画をいつも行うようになる。

Select * from t where col > @p1 or col2 > @p2 order by col1
option (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN))
引用っぽいの終わり

RECOMPILE や OPTIMIZE FOR については、MSDN ライブラリに説明が書かれてました。

プラン ガイドについて（SQL Server 2005）
http://msdn.microsoft.com/ja-jp/library/ms190417(SQL.90).aspx

詳細について、次のブログ（英語）に書かれていました。

Using the RECOMPILE Query Hint to Solve Parameter Sniffing Problems
http://www.sqlmag.com/article/sql-server/-using-the-recompile-query-hint-to-solve-parameter-sniffing-problems.aspx

かざとさんは 2005 の環境とのことですから、一度、SQL を以下のようにしてみてはいかがでしょうか？

select *
from dummynames
where birthday between @dt and @dt2
OPTION (RECOMPILE)

ちなみに、2008 以降で使える UNKNOWN のヒントについては、MSDN に次のように書かれていました。

「クエリ オプティマイザーでのクエリの最適化時に、初期値の代わりに統計データを使用してローカル変数の値を決定することを指定します。」

2010年12月15日水曜日 9:18

一般論になってしまいますが、パラメータを使っていないときと使っているときは意味合いは同じでも
SQL Serverは違うクエリとして認識します。そのため実行計画を作る段でかわっていると思います。
統計情報は最新になっているでしょうか。念のため更新したときにどのような動作になるか見てはいかがでしょうか。
今までの経験上、どうしてもIndex Seekが適していることが自明であればクエリヒントを使いました。

2010年12月15日水曜日 11:21

統計情報を手動で更新したのですが、結果は変わりませんでした。

どうやら、日付の範囲指定が原因のひとつであるようです。

（範囲指定ではなく＝で指定すると両方ともインデックスを使用した）

>どうしてもIndex Seekが適していることが自明であればクエリヒントを使いました。

なるほど、それも含め検討してみようと思います。

2010年12月17日金曜日 11:23

>これは今回の話に関係ないでしょうか？

確認したところ、完全に当てはりました。

 

> 一度、SQL を以下のようにしてみてはいかがでしょうか？

たしかに、「OPTION (RECOMPILE)」で、元質問文の[1]と同じ実行計画になりました。
そうなる（なってしまう）仕組みについても納得しました。実行計画の作成タイミングを考えると当然そうなるわけですね。

OPTION (RECOMPILE)は、実行計画のキャッシュというパラメータクエリのメリットを殺す形になるわけで、安易に使うのは危険かもしれないですね。

 

とりあえず、範囲指定がポイントだというのもわかったので、SQLの作成段階で極力気をつけるようにして、同しようもない場合は、OPTION (RECOMPILE)の使用も視野に入れたいと思います。

ありがとうございました。