質問: 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](https://i.stack.imgur.com/2LIAz.png) 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](https://i.stack.imgur.com/beMyh.png) この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' ``` ですが、実行計画に変化はありませんでした。
回答: > > なぜあのような実行計画になるのでしょうか? > > > > > 実際の`SID`カラムの型は`char(36)`です。 > > > アドホッククエリで `'f784ad8f-54ee-4fc3-ad3b-2e319f629b16'`などの即値が書かれている場合、`char(36)`とそのまま比較できるためインデックスが使えます。 しかし、パラメーターとして`varchar(8000)`型を渡してしまうと`SID`カラム側を`varchar(8000)`に拡大変換する必要があり、非効率になってしまっているのかもしれません。 パラメーターとして`char(36)`や`varchar(36)`を渡した場合に改善するかもしれません。 ただ、実行計画の時点パラメーターの型が分かっていないので同様のことはできず、クエリ側を修正する必要があるかもしれません。例えば、テーブル値パラメーターを使う等。