SQL Performance Issue Using Variable Dates vs. Literal Dates

[Christopher D. Wiederspan]

I thought I knew enough about SQL Server 2000 that I understood my queries,
but I've recently come across a situation that's very perplexing to me. The
situation is that I've written a fairly straight forward query that runs
very quickly one way, but very slowly when I make what I thougth was a minor
change. The fast version is this:

SELECT
VS.PartnerID,
VS.PartnerCode,
COUNT(DISTINCT VS.VisitorSessionID) 'Clicks',
COUNT(A.ApplicationID) 'Applications',
SUM(A.Revenue) 'Revenue'

FROM VisitorSessions AS VS (NOLOCK)

LEFT JOIN VisitorApplications AS VA (NOLOCK)
ON VS.VisitorSessionID = VA.VisitorSessionID

LEFT JOIN PayableApplications AS A (NOLOCK)
ON VA.ApplicationID = A.ApplicationID

WHERE VS.StartDate >= '1/1/2004' -- *** NOTICE THIS
AND VS.StartDate < '1/2/2004' -- *** NOTICE THIS

GROUP BY VS.PartnerID, VS.PartnerCode

My problems began when I though I'd make my life simpler by replacing the
"hard-coded" dates in the WHERE clause with variable dates, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '1/1/2004'
SET @EndDate = '1/2/2004'

SELECT
VS.PartnerID,
VS.PartnerCode,
COUNT(DISTINCT VS.VisitorSessionID) 'Clicks',
COUNT(A.ApplicationID) 'Applications',
SUM(A.Revenue) 'Revenue'

FROM VisitorSessions AS VS (NOLOCK)

LEFT JOIN VisitorApplications AS VA (NOLOCK)
ON VS.VisitorSessionID = VA.VisitorSessionID

LEFT JOIN PayableApplications AS A (NOLOCK)
ON VA.ApplicationID = A.ApplicationID

WHERE VS.StartDate >= @StartDate
AND VS.StartDate < @EndDate

GROUP BY VS.PartnerID, VS.PartnerCode

All of the sudden, my query that previously took 1 or 2 seconds was now
taking 45 seconds. My tables are nothing out of the ordinary, and contain a
lot, but not unreasonable amounts of data. When using a combination of Query
Analyzer and SQL Profiler, I can definitely see that SQL Server is executing
the queries much differently, but I don't understand why...

I've since gone back to some of the 10's (if not 100's) of queries and
Stored Procedures within our database and tested to see if they suffer from
the same problem - THEY DO! With that said, you can understand that I feel
like I may be loosing a ton of performance by doing things this way.

Please let me know if you have any insight into this issue - I can defnitely
use the help!

Thanks,
Chris

[Aaron Bertrand [MVP]]

Yep, this is often called "parameter sniffing."
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=u7BMLhvCCHA.1732%40tkmsftngp02

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

"Christopher D. Wiederspan" <wiede...@netquote.com> wrote in message
news:OXeurqr4...@tk2msftngp13.phx.gbl...

[Rajesh Patel]

try to put your where condition like below
WHERE VS.StartDate = VS.StarDate and VS.StartDate >= @StartDate AND
VS.StartDate < @EndDate

May be this works.

Regards,

Rajesh Patel

"Christopher D. Wiederspan" <wiede...@netquote.com> wrote in message
news:OXeurqr4...@tk2msftngp13.phx.gbl...

[Christopher D. Wiederspan]

Actually, this did the trick. Can you give me any insight as to why the
VS.StartDate = VS.StartDate is making a difference?

Thanks,
Chris

"Rajesh Patel" <rdp...@hotmail.com> wrote in message
news:e9f4RHt4...@TK2MSFTNGP09.phx.gbl...

[Christopher D. Wiederspan]

That's exactly the situation that I've encountered. I also found that
Rajesh's suggestion solved the problem - is that because the "compiler" can
use the existing values of StartDate when divising the execution plan? In
any case, this is extremely interesting...

Thanks for the help!

Chris

"Aaron Bertrand [MVP]" <aa...@TRASHaspfaq.com> wrote in message
news:OHCefUs4...@TK2MSFTNGP11.phx.gbl...

[Vinodk]

The parameter sniffing does explain your case I suppose. Thats the only
documented reason we have in our hand now.

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

"Christopher D. Wiederspan" <wiede...@netquote.com> wrote in message

news:%23pMNKcv...@TK2MSFTNGP12.phx.gbl...

[neeraj...@nimbusitsolutions.com]

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id (SLOWER Query)
-------------------------------

Do this
DECLARE @sql varchar(max)
SET @sql='SELECT * FROM View1 WHERE ID ='+CAST(@id as varchar)
EXEC (@sql)

Solves your problem

[neeraj...@nimbusitsolutions.com]

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id