How can a simple date variable hurt my runperformance?-CodePudding

I have a select statement that runs in 10 seconds when I hardcode a date.

But if i create a simple variable that contains the date, my performance takes hours.

This happens inside a stored procedure or even just in new query.

The execution plan doesn't show anything notable. I have a bunch of needed indexes for these joins.

Any ideas on why there would be such a difference?

declare @datetouse datetime = '2022-03-05'
SELECT   i.*, isnull(fm.guidsolo, sm.guidsolo) as guidsoloExists
        FROM IngestItems i
    left join Ingested_Main nm on i.guidsolo = nm.guidsolo
    left join Files_Main fm on nm.CollectionName = fm.CollectionName
                            and nm.CollectionYear = fm.CollectionYear
                            and nm.CollectionMonth = fm.CollectionMonth
                            and nm.CollectionDay = fm.Collectionday
    left join FilesStaged_Main sm on nm.CollectionName = sm.CollectionName
                            and nm.CollectionYear = sm.CollectionYear
                            and nm.CollectionMonth = sm.CollectionMonth
                            and nm.CollectionDay = sm.CollectionDay
    Where i.DateRetreived > @datetouse   ---- with this line takes forever
    --WHERE i.DateRetrieved > '2022-03-05'   ------ with this line it takes 10 seconds
    ORDER BY i.description;

CodePudding user response：

See generally Query Processing Architecture Guide.

With the variable SQL Server must select a query plan that will work acceptably for any value of the variable.

If it were a stored procedure parameter, or a client API parameter then SQL Server would select a query plan that will work acceptably for any value, but could pick a plan that favors the first value used (aka Parameter Sniffing).

If you hard-code the value, SQL Server can pick a plan that is optimal for that one value since that query plan won’t be reused for any other value.

Adding OPTION RECOMPILE or OPTION OPTIMIZE FOR query hints you can change this behavior.

    OPTION( RECOMPILE )

    OPTION( OPTIMIZE FOR (  @datetouse =  '2022-03-05' ) )

But if you use OPTIMIZE FOR may need to update the query every day or week to keep it close to the current date.