CODEWITHSUNDEEP
sql-servercastingquery-performancedateadd
Mike
Mike

Reputation: 47

Performance difference when using DATEADD and CAST in different order

I was shown a query by a co-worker where he was experiencing some dramatic performance issues while using DATEADD and CAST together. By switching the order he got much better performance. Why does the order matter so much performance wise?

This where clause runs well:

WHERE IHist.[DateTime] BETWEEN DATEADD(DD, -30,CAST(GETUTCDATE() AS Date)) AND DATEADD(DD, 1, CAST(GETUTCDATE() AS Date))

This where clause takes forever:

WHERE IHist.[DateTime] BETWEEN CAST(DATEADD(DD, -30,GETUTCDATE()) AS DATE) AND CAST(DATEADD(DD, 1, GETUTCDATE()) AS DATE)

Upvotes: 0

Views: 1324

Answers (1)

Eralper
Eralper

Reputation: 6612

Since the values are static, it is better to define them at the beginning 

declare @d1 date, @d2 date
select
    @d1 = DATEADD(DD, -30, CAST(GETUTCDATE() AS Date)),
    @d2 = DATEADD(DD,   1, CAST(GETUTCDATE() AS Date))

And then use in WHERE clause

Upvotes: 1

Related Questions