Why would replacing a parameter with a local variable speed up a query

Question

I have a query that has two DATE parameters, as such:

@startDate DATE,
@endDate DATE

While developing the sproc it was great (< 1 second). Moved it into a stored procedure as a child and when I ran it again, it takes minutes to run (2 to be exact).

I ran into this before (which I thought was some anomaly that I didn't pursue back then) so I tried the last "hack" that worked:

DECLARE @sDate DATE = CAST(@startDate AS DATE);
DECLARE @eDate DATE = CAST(@endDate AS DATE);

And sure enough, back to < 1s return times.

I have tried everything to figure this out and nothing seems to work. I can't find differences anywhere that changes anything. The values are exactly the same, not matter how many different ways I try to slice it.

I have also tried:

SET @startDate = CAST(@startDate AS DATE);
SET @startDate = CONVERT(date, @startDate, 101)

And I have tried re-declaring them (using any method) in the parent sproc.

It only works if I re-declare the variables in the child sproc.

So, why would re-declaring a variable of the same type, result in such an extreme difference in performance?

UPDATE - It Is Parameter Sniffing

I didn't originally think so, but all evidence points to the fact that it is, even though I haven't been able to fix it with normal methods that typically either work or help identify it. Except replacing it with a local variable which with the help from all the posters below would indicate it has to be parameter sniffing.

---

First Update

I don't think this is parameter sniffing - which was my first thought. This is what I have done to test this:

• Changed parameters (add/remove)
• Added additional criteria to the query
• Added OPTION recompile
• SET ARITHABORT ON
• Drop/Created old and new indexes

The changes above had no impact on the query.

Answer 1

It is a "parameter sniffing" workaround. I recommend to read: Slow in the Application, Fast in SSMS?

Parameters and Variables

Consider the Orders table in the Northwind database, and these three procedures:

CREATE PROCEDURE List_orders_1 AS
   SELECT * FROM Orders WHERE OrderDate > '20000101'
go
CREATE PROCEDURE List_orders_2 @fromdate datetime AS
   SELECT * FROM Orders WHERE OrderDate > @fromdate
go
CREATE PROCEDURE List_orders_3 @fromdate datetime AS
   DECLARE @fromdate_copy datetime
   SELECT @fromdate_copy = @fromdate
   SELECT * FROM Orders WHERE OrderDate > @fromdate_copy
go

In the first procedure, the date is a constant, which means that the SQL Server only needs to consider exactly this case. It interrogates the statistics for the Orders table, which indicates that there are no rows with an OrderDate in the third millennium. (All orders in the Northwind database are from 1996 to 1998.) Since statistics are statistics, SQL Server cannot be sure that the query will return no rows at all, why it makes an estimate of one single row.

In the case of List_orders_2, the query is against a variable, or more precisely a parameter. When performing the optimisation, SQL Server knows that the procedure was invoked with the value 2000-01-01. Since it does not any perform flow analysis, it can't say for sure whether the parameter will have this value when the query is executed. Nevertheless, it uses the input value to come up with an estimate, which is the same as for List_orders_1: one single row. This strategy of looking at the values of the input parameters when optimising a stored procedure is known as parameter sniffing.

In the last procedure, it's all different. The input value is copied to a local variable, but when SQL Server builds the plan, it has no understanding of this and says to itself I don't know what the value of this variable will be.

...

Key Points

In this section, we have learned three very important things:

-A constant is a constant, and when a query includes a constant, SQL Server can use the value of the constant with full trust, and even take such shortcuts to not access a table at all, if it can infer from constraints that no rows will be returned.

-For a parameter, SQL Server does not know the run-time value, but it "sniffs" the input value when compiling the query.

-For a local variable, SQL Server has no idea at all of the run-time value, and applies standard assumptions. (Which the assumptions are depends on the operator and what can be deduced from the presence of unique indexes.)

And second great article Parameter Sniffing Problem and Possible Workarounds