Dates and Indexes in Query

[Ross]

I'm running into this really weird behaviour. When
querying (usually via stored procedures but also in Query
Analyzer) Date values the only way SQL 7.0 uses the index
is if the date is hard coded (not a variable) unless the
comparison is an equal.

When I look at the execution plan here is what I see in
these examples (there is a non-unique index on StartDate):

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '09/01/2002'
SET @EndDate = '09/11/2002'

SELECT * From Clients WHERE StartDate = @StartDate
This uses the index

SELECT * From Clients WHERE StartDate > @StartDate
This does a table scan and ignores Index

SELECT * From Clients WHERE StartDate = '09/01/2002'
This uses Index

SELECT * from Clients WHERE StartDate Between '09/01/2002'
AND '09/12/2002'
This uses Index

SELECT * from Clients WHERE StartDate Between @StartDate
AND @EndDate
This does a table scan.

Obviously the issue is the ability to report efficiently.
If I hardcode the values the query executes in under 2
second. If I pass variables to the procedure it takes over
45 seconds (large table!).

This makes no sense to me! Thanks in advance for the help.

Ross

[Dean Thompson]

Here, you are using variables, so you are absolutely right that the
query will not use the indexes the way you think.

Instead try using parameters...

exec sp_executesql N'SELECT * from Clients WHERE StartDate Between
@StartDate AND @EndDate', N'@StartDate DATETIME, @EndDate DATETIME',
@StartDate = '09/01/2002', @EndDate = '09/11/2002'

This should use the indexes more often IF they are appropriate.

This is the same effect as you would see with a stored procedure. For
example:

CREATE PROCEDURE MyProc
@pStartDate DATETIME -- This is a parameter, not a variable
@pEndDate DATETIME
AS

DECLARE @StartDate DATETIME,
@EndDate DATETIME

SET @StartDate = '09/01/2002'
SET @EndDate = '09/11/2002'

-- This is more likely to use an index properly

SELECT
*
from
Clients
WHERE

StartDate Between @pStartDate AND @pEndDate

-- This is not

SELECT
*
from
Clients
WHERE

StartDate Between @vStartDate AND @vEndDate
END

HTH,
Dean Thompson <de...@txsqlusers.com>
Texas SQL Users <http://www.txsqlusers.com>
-------------------------------------------------
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which can be cut and pasted into Query Analyzer is appreciated.
-------------------------------------------------
Add your SQL Server or Developer website to our Web Resources
directory at
http://www.txsqlusers.com/txLinks/add.asp
Sign up for our free monthly newsletter at
http://lb.bcentral.com/ex/manage/subscriberprefs.aspx?customerid=9828

[Dean Thompson]

This should have been:

CREATE PROCEDURE MyProc
@pStartDate DATETIME -- This is a parameter, not a variable
@pEndDate DATETIME
AS
DECLARE @StartDate DATETIME,
@EndDate DATETIME

SET @vStartDate = '09/01/2002'
SET @vEndDate = '09/11/2002'

-- This is more likely to use an index properly
SELECT
*
from
Clients
WHERE
StartDate Between @pStartDate AND @pEndDate

-- This is not
SELECT
*
from
Clients
WHERE
StartDate Between @vStartDate AND @vEndDate
END

exec MyProc '09/01/2002', '09/11/2002'

Sorry about that.

Dean

[ross]

Dean,

Many thanks. This worked.

A programmer friend of mine and I have a saying about
something being "A design flaw from the very beginning"
(of course we've NEVER been guilty of that!). It would
seem to me that this behaviour in SQL Server is either
that or an actual bug!

Regards,

Ross

>.
>

[Dean Thompson]

Its really not either a design flaw or a bug. Its has to do with
typical use.

For example, a client application that "hard codes" the date values
will always have the best index usage (assuming accurate statistics)
because SQL Server will be able to determine the value to use and
apply it. The disadvantage is that each query will typically require
a recompile and therefore lose any gains that the index provided if
the batch is called frequently.

When parameters are used, then a more general index is used (one based
upon standard deviation rather than the actual value contained). With
variables, though my example did not illustrate why, SQL Server cannot
be sure what the value will be until it actually hits that step, long
after the batch has been compiled. The disadvantage here is that it
may not always use the best index as a value query would. However, it
would require far fewer recompiles than the value query did, and
generally result in better performance all around.

In your example of the variable being equal to the date column, SQL
Server used a technique known as auto-parameterization to 'guess' that
the value passed could be reused at a later point and that is why it
was able to use the index on the variable as you requested.

HTH,
Dean

[Dean Thompson]

Another mistype so I have re-orged my reply below:

For example, a client application that "hard codes" the date values
will always have the best index usage (assuming accurate statistics)
because SQL Server will be able to determine the value to use and
apply it. The disadvantage is that each query will typically require
a recompile and therefore lose any gains that the index provided if
the batch is called frequently.

When parameters are used, then a more general index is used (one based

upon standard deviation rather than the actual value contained). The

disadvantage here is that it may not always use the best index as a
value query would. However, it would require far fewer recompiles
than the value query did, and generally result in better performance
all around.

With variables, though my example did not illustrate why, SQL Server
cannot be sure what the value will be until it actually hits that
step, long after the batch has been compiled.

In your example of the variable being equal to the date column, SQL
Server used a technique known as auto-parameterization to 'guess' that
the value passed could be reused at a later point and that is why it
was able to use the index on the variable as you requested.

HTH,
Dean