SQL Server Why hard-coded dates in WHERE clause gives faster performance then same dates as a parameters?

fkaflof6 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(105)

Why such a huge difference in performance if I put my dates in WHERE clause (first query) or if I put same dates as a parameters (second query)

--------/* Execution time 3 sec*/ 
   select 
            tblNoteDiaries.DueDate,
            col2,
            col3
    from MyTable 
    where CAST(tblNoteDiaries.DueDate as date) <= cast(getdate()as date) and cast( tblNoteDiaries.DueDate as date) >= CAST('2017-09-29' as DATE)

--------/* Execution time 10 sec*/    
    declare     @DateFrom datetime = '2017-09-29',
                @DateTo datetime = '2017-10-05'
    select 
            tblNoteDiaries.DueDate,
            col2,
            col3
    from    MyTable 
    where   CAST(tblNoteDiaries.DueDate as date)  >= @DateFrom   and cast( tblNoteDiaries.DueDate as date) <= @DateTo

I need this query as a stored procedure, what would be the best approach to utilize date parameters without degrading performance??

sql-server

来源：https://stackoverflow.com/questions/46596427/why-hard-coded-dates-in-where-clause-gives-faster-performance-then-same-dates-as

2条答案

按热度按时间

kyvafyod1#

I simulated on my database using below queries

select sum(PrinBal)
from fpc
where SnapshotDt >= '2017-06-01' and SnapshotDt <= '2017-06-30'
go

declare @sd date = '2017-06-01'
declare @ed date = '2017-06-30'
select sum(PrinBal)
from fpc
where SnapshotDt >= @sd and SnapshotDt <= @ed
go

and checked execution plan. it was 48% for first query and 52% for the second one.
Then I added option(recompile) to the second query and then both took exact the same percentage

declare @sd date = '2017-06-01'
declare @ed date = '2017-06-30'
select sum(PrinBal)
from fpc
where SnapshotDt >= @sd and SnapshotDt <= @ed
option(recompile)
go

So as Nick, said, it is parameter sniffing.
You can get rid of parameter sniffing using updated statistics or using RECOMPILE option for your queries and stored procedures.

赞(0）回复(0）举报 2022-12-10

nlejzf6q2#

因为你的变量声明为datetime，它有更高的数据类型优先级，所以你要显式地将你的DueDate列转换为date，然后再转换为datetime。
对变量使用date，您应该看到相同的性能。更好的是，如果类型已经正确，不要对日期列使用CAST。