FrumRoll FrumRoll - 1 year ago 76
SQL Question

Query Performance When Using Variable

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.

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.


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

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%

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download