Performance-Issue with variables

It's weird, but the literal (untyped) version is faster than the version with the paramenters. Occurs with SQL 2005 SP1 64bit

how come

--FAST!!!! See execution plan

SELECT count(*)

FROM

dbo.NewsComponent ttNC

WHERE

ttNC.DateImported BETWEEN '2007-04-02T00:00:00' AND '2007-04-03T23:59:59' )

--(ttNC.DateImported > '2007-01-01T00:00:00' )

--SLOW!!!! See execution plan

DECLARE @dateBegin DATETIME; SET @dateBegin = '2007-01-01T00:00:00'

DECLARE @dateEnd DATETIME; SET @dateEnd = '2007-04-03T23:59:59'

SELECT count(*) FROM dbo.NewsComponent ttNC WHERE

(ttNC.DateImported BETWEEN @dateBegin AND @dateEnd )

--(ttNC.DateImported > @dateBegin )

This is has to do with the way the optimizer works. When you are comparing your target columns to a constant the optimizer can make a better estimate of the plan that is needed. A similar thing can potentially happen if you are using a stored procedure and you access the index with parameters to the stored procedure (parameter "sniffing"). This is a useful optimization feature. With variables instead of parameters or constants the optimizer has no ability for "sniffing".

These posts might be a useful read -- especially Umachandar's comments:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1064467&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=994587&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=989579&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=733229&SiteID=1

If you are interested, here is a more 'indepth' exclication of what happens, and how to avoid 'parameter sniffing'.

Stored Procedure ¨CParameter Sniffing
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
http://tinyurl.com/f9r2
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx