We have a stored procedure where we are getting the list of customer IDs. Within that procedure, we have a filter condition for Date. When we use hard-coded date there the query performance becomes much much better than when we use a "date parameter":
ALTER PROCEDURE [dbo].[Sp_GetCustomers] ( @StartDate DATE, @ProductCode VARCHAR(20) = NULL ) AS BEGIN Select DISTINCT c.CustomerId From Custmer c INNER Join [Order] o On c.CustomerId = o.CustomerId WHERE o.ProductCode = @ProductCode AND -- Filter# 1: Below hard coded date results better performance o.date_inserted >= '2019-06-01' -- Filter# 2: Below dynamic date (variable) kills the performance o.date_inserted >= @StartDate END
In above query, see Filter# 1 and Filter#2
If we enable Filter# 1, it works great. But if we enable filter# 2 it kills the performance.
Any idea what could be the reason?