Why hardcoded date filter works much better than dynamic parameter?

Question

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?

Answer 1

We cannot know for sure without both execution plans. Below are three common reasons.

Filtered indexes

It could be the use of a filtered index such as:

        CREATE INDEX IX_Order_ProductCode_CustomerId_Filtered
        ON [dbo].[Order](ProductCode,CustomerId)
        INCLUDE(date_inserted)
        WHERE date_inserted >= '2019-06-01';

This should pop up as an unmatched indexes warning in the execution plan. More on these here

Parameter sniffing

Another common reason would be parameter sniffing, where the value that runs with the stored procedure first / on plan creation is cached for following executions, until the plan is recompiled. Recompilation can for example happen due to statistics of one of the referenced tables being updated.

More on parameter sniffing here

If you want sql server to see the parameter as a hardcoded value then you would have to add OPTION(RECOMPILE) to the query.

Wrong datatypes

if the date_inserted column is a string datatype such as varchar() then your query would not be able to seek on the parameter. It would do an implicit conversion of the varchar() field to a date field on a residual predicate instead of a seek predicate.

The difference between below two queries could then be immense

SELECT * FROM [Order] o 
WHERE o.date_inserted < '2019-06-01' 

DECLARE @date date = '2019-06-01' 

SELECT * FROM [Order] o 
WHERE o.date_inserted < @date

With more than 2 seconds execution time on the second query

Due to the residual predicate:

where the entire date_inserted column is converted to a date datatype before filtering.