TransWikia.com

パラメータ化クエリを使うと実行計画が複雑化し、遅くなってしまう

スタック・オーバーフロー Asked by mok2pok on November 5, 2021

JDBCとSQL Server 2016 Standardを使っています。
単純なSQLなのに不可解な実行計画になってしまい、性能が出ずに困っています。

アプリケーション側からJDBCを介して発行されるクエリは以下のような形です。
(SQL Server Profilerで採取)

exec sp_executesql 
N'select * from KRCDT_DAY_TIME where SID in (@P0,@P1,@P2,@P3,@P4) and YMD between @P5 and @P6',
N'@P0 varchar(8000),@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 varchar(8000),@P5 date,@P6 date',
'f784ad8f-54ee-4fc3-ad3b-2e319f629b16','6d57123b-c7db-4efb-9976-12b92f7a3aa5','362da6e6-a893-46e4-bdf9-22cd9441f17c',
'fccb8f4f-2fde-4c14-bf68-4d7bc605863c','4d442a4c-36a7-403d-9dfd-3dd0b713c4e8',
'2020-07-06','2020-07-06'

これをSSMS上で実行して実行計画を見ると、以下のようになりました。

実行計画1

IN句が増えると、Merge Joinの入れ子がその分だけ増えます。


続いて、先述のパラメータ化クエリを次のようなアドホッククエリに書き直して、SSMS上で実行してみます。

select * from KRCDT_DAY_TIME 
where SID in (
'f784ad8f-54ee-4fc3-ad3b-2e319f629b16','6d57123b-c7db-4efb-9976-12b92f7a3aa5','362da6e6-a893-46e4-bdf9-22cd9441f17c',
'fccb8f4f-2fde-4c14-bf68-4d7bc605863c','4d442a4c-36a7-403d-9dfd-3dd0b713c4e8'
) 
and YMD between '2020-07-06' and '2020-07-06'

以下が実行計画です。

実行計画2

この2つでは、アドホッククエリの方が数倍(3~6倍くらい?)速いです。
本当はパラメータ化クエリを使いたいのですが、なぜあのような実行計画になるのでしょうか?
アドホッククエリと同じ実行計画にする方法は無いでしょうか。

なお、対象テーブルKRCDT_DAY_TIMEのインデックスは、主キーのクラスタ化インデックス1つだけであり、以下の2つのカラムで構成されます。

SID char(36)
YMD date


追記1

コメントを受けてちょっと気になったので、SIDカラムの実際の型char(36)を使って、パラメータ化クエリの型宣言?部分を書き換えてみました。
varchar(8000)char(36)

exec sp_executesql 
N'select * from KRCDT_DAY_TIME where SID in (@P0,@P1,@P2,@P3,@P4) and YMD between @P5 and @P6',
N'@P0 char(36),@P1 char(36),@P2 char(36),@P3 char(36),@P4 char(36),@P5 date,@P6 date',
'f784ad8f-54ee-4fc3-ad3b-2e319f629b16','6d57123b-c7db-4efb-9976-12b92f7a3aa5','362da6e6-a893-46e4-bdf9-22cd9441f17c',
'fccb8f4f-2fde-4c14-bf68-4d7bc605863c','4d442a4c-36a7-403d-9dfd-3dd0b713c4e8',
'2020-07-06','2020-07-06'

ですが、実行計画に変化はありませんでした。


追記2

decoyさんの回答を受けて、ヒントを追記してみました。

exec sp_executesql 
N'select * from KRCDT_DAY_TIME where SID in (@P0,@P1,@P2,@P3,@P4) and YMD between @P5 and @P6
 option (OPTIMIZE FOR (@P0 UNKNOWN, @P1 UNKNOWN, @P2 UNKNOWN, @P3 UNKNOWN, @P4 UNKNOWN))',
N'@P0 char(36),@P1 char(36),@P2 char(36),@P3 char(36),@P4 char(36),@P5 date,@P6 date',
'f784ad8f-54ee-4fc3-ad3b-2e319f629b16','6d57123b-c7db-4efb-9976-12b92f7a3aa5','362da6e6-a893-46e4-bdf9-22cd9441f17c',
'fccb8f4f-2fde-4c14-bf68-4d7bc605863c','4d442a4c-36a7-403d-9dfd-3dd0b713c4e8',
'2020-07-06','2020-07-06'

ですが、実行計画に変化はありませんでした。

3 Answers

(希望している回答とは方向性の違うものになりますが…)

対象テーブルKRCDT_DAY_TIMEのインデックスは、主キーのクラスタ化インデックス1つだけ

(SID)をクラスター化ユニークインデックス(PK)に指定している、ということだと思いますが、今回のテーブルにおいてSIDの並び順(アルファベット順)に特に意味はないと思いますので、検索パフォーマンスだけを考えると(つまり更新パフォーマンスは無視すると)

の2本立てでいくのが有利かと思います。

(今回のクエリだと…どちらが使われるんでしょうかね…)


なぜあのような実行計画になるのでしょうか?

クラスター化ユニークインデックススキャンだし、そんなコストかからんやろ…という気持ちは分からなくもないかな、と考えますがどうでしょう…


追記2の部分の検証では、@P5, @P6 も対象にしてあげる必要があるように思います。

Answered by DEWA Kazuyuki - 出羽和之 on November 5, 2021

なぜあのような実行計画になるのでしょうか?

実際のSIDカラムの型はchar(36)です。

アドホッククエリで 'f784ad8f-54ee-4fc3-ad3b-2e319f629b16'などの即値が書かれている場合、char(36)とそのまま比較できるためインデックスが使えます。
しかし、パラメーターとしてvarchar(8000)型を渡してしまうとSIDカラム側をvarchar(8000)に拡大変換する必要があり、非効率になってしまっているのかもしれません。

パラメーターとしてchar(36)varchar(36)を渡した場合に改善するかもしれません。

ただ、実行計画の時点パラメーターの型が分かっていないので同様のことはできず、クエリ側を修正する必要があるかもしれません。例えば、テーブル値パラメーターを使う等。

Answered by sayuri on November 5, 2021

古い記事ですが、これはどうでしょうか?

パラメータを使うと遅くなる? SQL Server | MSDNフォーラム

一般論になってしまいますが、パラメータを使っていないときと使っているときは意味合いは同じでもSQL Serverは違うクエリとして認識します。そのため実行計画を作る段でかわっていると思います。

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))

Answered by decoy on November 5, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP