スタック・オーバーフロー 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上で実行して実行計画を見ると、以下のようになりました。
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つでは、アドホッククエリの方が数倍(3~6倍くらい?)速いです。
本当はパラメータ化クエリを使いたいのですが、なぜあのような実行計画になるのでしょうか?
アドホッククエリと同じ実行計画にする方法は無いでしょうか。
なお、対象テーブルKRCDT_DAY_TIME
のインデックスは、主キーのクラスタ化インデックス1つだけであり、以下の2つのカラムで構成されます。
SID char(36)
YMD date
コメントを受けてちょっと気になったので、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'
ですが、実行計画に変化はありませんでした。
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'
ですが、実行計画に変化はありませんでした。
(希望している回答とは方向性の違うものになりますが…)
対象テーブル
KRCDT_DAY_TIME
のインデックスは、主キーのクラスタ化インデックス1つだけ
(SID)
をクラスター化ユニークインデックス(PK)に指定している、ということだと思いますが、今回のテーブルにおいてSID
の並び順(アルファベット順)に特に意味はないと思いますので、検索パフォーマンスだけを考えると(つまり更新パフォーマンスは無視すると)
(SID)
の ユニークな非クラスター化ハッシュインデックス(PK)(YMD, 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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP