

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

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

[code]

    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'
    
[/code]

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

[![実行計画1](https://i.stack.imgur.com/2LIAz.png)](https://i.stack.imgur.com/2LIAz.png)

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

* * *

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

[code]

    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'
    
[/code]

以下が実行計画です。

[![実行計画2](https://i.stack.imgur.com/beMyh.png)](https://i.stack.imgur.com/beMyh.png)

この２つでは、アドホッククエリの方が数倍（３～６倍くらい？）速いです。  
本当はパラメータ化クエリを使いたいのですが、なぜあのような実行計画になるのでしょうか？  
アドホッククエリと同じ実行計画にする方法は無いでしょうか。

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

`SID char(36)`  
`YMD date`

* * *

## 追記1

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

[code]

    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'
    
[/code]

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

* * *

## 追記2

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

[code]

    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'
    
[/code]

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


CC BY-SA 4.0
2020-07-21T10:48:47.607
68789
2023-11-13T07:01:15.217
2020-07-27T09:49:14.373
8078
8078
question
java sql-server
パラメータ化クエリを使うと実行計画が複雑化し、遅くなってしまう


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

[パラメータを使うと遅くなる？ SQL Server |
MSDNフォーラム](https://social.msdn.microsoft.com/Forums/ja-
JP/c263f1b2-fdc2-44ff-9a9f-d6c675ed7d72/12497125211251312540124791243420351123581239236933123671239412)

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

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

>     Select * from t where col > @p1 or col2 > @p2 order by col1
>     option (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN))
>  
[/code]


CC BY-SA 4.0
2020-07-21T11:20:10.297
68791
2020-07-21T12:07:43.717
2020-07-21T12:07:43.717
3060
41073
68789
answer


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

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

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

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

ただ、実行計画の時点パラメーターの型が分かっていないので同様のことはできず、クエリ側を修正する必要があるかもしれません。例えば、[テーブル値パラメーター](https://docs.microsoft.com/ja-
jp/sql/connect/jdbc/using-table-valued-parameters?view=sql-server-ver15)を使う等。


CC BY-SA 4.0
2020-07-21T23:56:44.240
68801
2020-07-21T23:56:44.240
4236
68789
answer


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

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

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

  * `(SID)` の ユニークな非クラスター化[ハッシュインデックス](https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#hash-index-design-guidelines)(PK)
  * `(YMD, SID)` の クラスター化インデックス

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

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

* * *

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

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

* * *

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


CC BY-SA 4.0
2020-07-27T22:42:25.443
69040
2020-07-27T23:06:04.767
2020-07-27T23:06:04.767
2808
2808
68789
answer