@ local variable makes query slower?

Hi all,

I have a query that scans huge table consists of 8 or more millions
records. The funny thing is that if I use the query with local
variable, the query takes more than 1 minutes, whereas if I hard code
the value into the query, it takes about 1 second. Here are the
queries:

WITH VARIABLE:
------

DECLARE @.i_StartDate DATETIME
DECLARE @.i_EndDate DATETIME
SET @.i_StartDate = '2004-04-26'
SET @.i_EndDate = '2004-04-28'

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)

...And some other conditions

-------

WITHOUT VARIABLE:

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')

...And some other conditions

-------

The later one runs significantly faster than the first one. I've
isolated the problem at the local variable @.i_StartDate and
@.i_EndDate. Can somebody help me out, Please...

Thank you,
Michelle."Michelle" <michelletran@.harmonyremote.com> wrote in message
news:56c5b7ab.0404260656.281cfc40@.posting.google.c om...
> Hi all,
> I have a query that scans huge table consists of 8 or more millions
> records. The funny thing is that if I use the query with local
> variable, the query takes more than 1 minutes, whereas if I hard code
> the value into the query, it takes about 1 second. Here are the
> queries:
> WITH VARIABLE:
> ------
> DECLARE @.i_StartDate DATETIME
> DECLARE @.i_EndDate DATETIME
> SET @.i_StartDate = '2004-04-26'
> SET @.i_EndDate = '2004-04-28'
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)
> ...And some other conditions
> -------
> WITHOUT VARIABLE:
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')
> ...And some other conditions
> -------
> The later one runs significantly faster than the first one. I've
> isolated the problem at the local variable @.i_StartDate and
> @.i_EndDate. Can somebody help me out, Please...
> Thank you,
> Michelle.

This may be an example of parameter sniffing - see this post, for example,
which describes an almost identical case:

http://groups.google.com/groups?hl=...ftngp13.phx.gbl

Simon|||Thanks Simon.
Michelle.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!