Query Performance When Using Variable

Question

I have a table with roughly 200 million rows. The table contains a number of columns, but at the moment only the primary key and a non clustered index based on the datetime column for indexes.

This first query will return zero rows, in less than a second.

SELECT *
FROM GenericTable
WHERE GenericDate > '01-01-1753' AND GenericDate <= '01-29-1753'

This query takes an excessively long time to return zero rows, approximately two minutes.

DECLARE @startDate DATETIME, @endDate DATETIME

SET @startDate = '01-01-1753'
SET @endDate = '01-29-1753'

SELECT *
FROM GenericTable
WHERE GenericDate > @startDate AND GenericDate <= @endDate

Using a date range that contains data, the performance is a little better? The first query will return 1000s of rows in less than a second, the second query still requires 30 seconds or more to return the same data.

EDIT: I also had it show me the execution plan, and the second query is not using the index?

1st Query:

Select <- Nested Loops (Inner Join) - 0% <- Index Seek (NonClustered) - 0%
                                         <- Key Lookup (Clustered) - 100%

2nd Query:

Select <- Parallelism (Gather Streams) - 10% <- Clustered Index Scan (Clustered) - 90%

What datatype is GenericDate? Also you should use the ANSI date format yyy-mm-dd to avoid any localization issues. (i.e. '1753-01-29') — Sean Lange
– Sean Lange, Commented Jul 13, 2016 at 20:34
Is this in a stored proc where the dates are passed in as parameters? It sounds a LOT like parameter sniffing but I don't know the context where this query is executing. — Sean Lange
– Sean Lange, Commented Jul 13, 2016 at 20:44
Any chance you could post the execution plans as attachments? They contain mountains of important information. — Sean Lange
– Sean Lange, Commented Jul 13, 2016 at 20:45
@SeanLange At the moment I'm simply running it from Management Studio in a new query window. I initially noticed the performance issue in a loop I had created to move data, but the performance issue exists outside of the loop as shown above. — Derrick Moeller
– Derrick Moeller, Commented Jul 13, 2016 at 20:48

Martin Smith · Accepted Answer · 2016-07-13 20:51:05Z

5

It's a well known issue with execution plan caching and parameter sniffing.

Quoting from this article

A while back I wrote about parameter sniffing, the situation where SQL compiles and caches an execution plan that is appropriate for a certain value of a parameter, but is non optimal for other values. There’s another side to parameter sniffing though – when the optimiser can’t sniff at all.

When a batch is submitted to the optimiser, the value of any parameters (from stored procedure, auto parametrisation or sp_executesql) are known to the optimiser. So are the value of any constants used in the SQL statement. The value of local variables, however, are not.

When the value of a variable used in the where clause is not known, the optimiser has no idea what value to use to estimate the number of affected rows. Hence, it has no idea how many rows from the table will satisfy the condition.

edited Jul 13, 2016 at 20:51

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

answered Jul 13, 2016 at 20:45

Hassan

1,4331 gold badge11 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Smith Over a year ago

And adding OPTION (RECOMPILE) will allow the values to be sniffed.

Hassan Over a year ago

@MartinSmith Absolutely

Collectives™ on Stack Overflow

Query Performance When Using Variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related