Variables make query performance worse

Question

I have this query and the performance slows down drastically when I declare variables:

DECLARE @StartDate DATETIME,
        @EndDate   DATETIME

SET @StartDate = '2018-08-13'
SET @EndDate   = '2018-08-19'

SELECT *
FROM [DIME_WH].[dbo].[FactOrderLines2] FL (nolock)
WHERE DD_OrderDate >= @StartDate
  AND DD_OrderDate <= @EndDate

This is much slower than this SQL statement:

SELECT *
FROM [DIME_WH].[dbo].[FactOrderLines2] FL (nolock)
WHERE DD_OrderDate >= '2018-08-01'
  AND DD_OrderDate <= '2018-08-17'

Both queries will return the same results in the end.

Answer 1

SELECT * FROM [DIME_WH].[dbo].[FactOrderLines2] FL (nolock)
WHERE DD_OrderDate >= '2018-08-01'
  AND DD_OrderDate <= '2018-08-17'

When constant is used in parameter, then Optimiser create special plan for this query.so if same query is executed with same value then plan is reuse, if value is change then another plan is created.

So Parameter with constant value is fast.

SELECT *
FROM [DIME_WH].[dbo].[FactOrderLines2] FL (nolock)
WHERE DD_OrderDate >= @StartDate
  AND DD_OrderDate <= @EndDate

When variable is use in parameter.Then Optimizer create Execution plan for the First parameter value that was passed .

For Example @StartDate='2018-08-01' and @EndDate='2018-08-07' value were pass for first time. Then optimal execution plan is created by optimiser. This plan is good enough for this value. Next Time @StartDate='2018-08-01' and @EndDate='2018-08-31' value is pass then same previous plan is use which may not be optimal for this parameter.

In another word same plan which was Optimal for first value is Sub optimal for another value.

so query may perform poor and slow.This is known as Parameter sniffing.

There are several ways to overcome this problem.

Parameter Sniffing

Note : In this thread we are only focussing on why variable performance is slow while other factor remaining constant.

Answer 2

This is because SQL Server does not know the values of your variables at optimization time - when it makes an estimate and can not look up any statistics for it (as a one possibility), so it's (most likely) just scans the whole table instead of make a lookup (seek).
They can be "sniffed" if used inside of stored procedure or parameterized with sp_executesql

Answer 3

The problem could be parameter sniffing, maybe not. I'll skip that topic since @KumarHarsh already covered it. The most important question here is: What data type is FactOrderLines.DD_OrderDate? This is important for performance reasons as well as correctness.

First for performance. If DD_OrderDate is a DATE datatype and your variables or parameters are DATETIME then the optimizer has to jump through extra hoops to utilize your index or will be forced to do a scan instead of a seek. Note the following sample data:

USE tempdb;
GO

IF OBJECT_ID('#FactOrderLines') IS NOT NULL DROP TABLE #FactOrderLines;
GO
CREATE TABLE #FactOrderLines(someId INT IDENTITY, DD_OrderDate DATETIME NOT NULL);

CREATE CLUSTERED INDEX nc_factOrderLines ON #FactOrderLines(DD_OrderDate);

INSERT #FactOrderLines(DD_OrderDate) 
SELECT TOP (10000) DATEADD(DAY,CHECKSUM(NEWID())%100, getdate())
FROM sys.all_columns;
GO

Now let's compare the execution plans for the following queries:

-- AS DATE
DECLARE @StartDate DATE = '2018-08-01',
        @EndDate   DATE = '2018-08-20';

SELECT * 
FROM #FactOrderLines
WHERE DD_OrderDate >= @StartDate
AND   DD_OrderDate <= @EndDate
OPTION (RECOMPILE)
GO

-- AS DATETIME
DECLARE @StartDate DATETIME = '2018-08-01',
        @EndDate   DATETIME = '2018-08-31';

SELECT * 
FROM #FactOrderLines
WHERE DD_OrderDate >= @StartDate
AND   DD_OrderDate <= @EndDate
OPTION (RECOMPILE);

Execution plans:

For this reason - you want to make sure that you're using the same datatype for your variables/parameters as for the column they are working against.

Now about correctness; note this query:

DECLARE @StartDate DATE = '2018-08-01',
        @EndDate   DATE = '2018-08-20';

SELECT
  [getdate as datetime]  = GETDATE(),
  [@enddate as datetime] = CAST(@EndDate AS DATETIME),
  [getdate as date]      = CAST(GETDATE() AS DATE),
  [datetime equality]    = IIF(GETDATE() > @EndDate,'yep','nope'),
  [date equality]        = IIF(CAST(GETDATE() AS DATE) > @EndDate,'yep','nope');

Results:

getdate as datetime     @enddate as datetime    getdate as date datetime equality date equality
----------------------- ----------------------- --------------- ----------------- -------------
2018-08-20 13:52:46.247 2018-08-20 00:00:00.000 2018-08-20      yep               nope

Values of the date format translate into datetime as 0 hour, 0 second...