Parameterized queries complicate and slow execution plans

I use JDBC and SQL Server 2016 Standard.
Even though it's simple SQL, it's a mysterious execution plan, and I'm having trouble performing.

The query issued by the application via JDBC looks like the following:
(collected in 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'@P0varchar(8000), @P1varchar(8000), @P2varchar(8000), @P3varchar(8000), @P4varchar(8000), @P5date, @P6date',
'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'

When I ran this on the SSMS and looked at the execution plan, I saw the following:

As the IN clause increases, the Merge Join nesting increases by that amount.

Next, try rewriting the above parameterized query to an ad hoc query similar to the following and running it on the 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'

The following is the execution plan:

With these two, ad hoc queries are several times faster (about 3 to 6 times faster?).
I really want to use a parameterized query, but why is it such an execution plan?
Is there any way to make it the same as an ad hoc query?

Note that the index of the target table KRCDT_DAY_TIME is only one clustered index of the primary key and consists of two columns:

SID char(36)
YMD date

After receiving the comment, I rewritten the type declaration? part of the parameterized query using the actual type char(36) in the SID column.
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'@P0char(36), @P1char(36), @P2char(36), @P3char(36), @P4char(36), @P5date, @P6date',
'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'

However, there was no change in the execution plan.

After receiving Decoy's answer, I added a hint.

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(@P0UNKNOWN,@P1UNKNOWN,@P2UNKNOWN,@P3UNKNOWN,@P4UNKNOWN))',
N'@P0char(36), @P1char(36), @P2char(36), @P3char(36), @P4char(36), @P5date, @P6date',
'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'

However, there was no change in the execution plan.

java sql-server

2022-09-30 14:10

3 Answers

It's an old article, but how about this one?

Does the parameter slow you down? SQL Server | MSDN Forum

Generally speaking, SQL Server recognizes it as a different query, even though the meaning is the same when the parameters are not being used and when they are being used.That's why I think it's changing to make an action plan.

SQL Server 2008 offers another option: to use the OPTIMIZE FOR UNKNOWN hint.
Once this is specified, SQL Server will always have the same standard plan as when there are no parameter values.

Select * from where col>@p1 or col2>@p2order by col1
option(OPTIMIZE FOR(@p1UNKNOWN,@p2UNKNOWN))

2022-09-30 14:10

Why do you have such an action plan?

The actual SID column type is char(36).

If the ad hoc query contains immediate values such as 'f784ad8f-54ee-4fc3-ad3b-2e319f629b16', the index can be used because it can be compared to char(36).
However, if you pass the varchar(8000) type as the parameter, the SID column side needs to be expanded to varchar(8000), which may be inefficient.

It may be improved if you pass char(36) or varchar(36) as parameters.

However, since we don't know the type of time parameter in the execution plan, we can't do the same, and we may need to modify the query side.For example, use the table value parameter.

2022-09-30 14:10

(The direction is different from the answer I wanted...)

The target table KRCDT_DAY_TIME has only one clustered index of the primary key

I understand that (SID) is designated as a clustering unique index (PK), but I don't think the order of SID in this table (in alphabetical order) is particularly meaningful, so considering the search performance (i.e., ignore the update performance)

Unique de-clustered hash index(PK)
Clustering Index for (YMD,SID)

I think it would be advantageous to go with the two-piece stand.

(For this query... which one will be used?)…)

Why do you have such an action plan?

It's a clustered unique index scan, and I'm sure you don't understand why it's so expensive...

Additionally, I think that @P5, @P6 should also be covered in the verification of part 2.

2022-09-30 14:10

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656