@ local variable makes query slower?

[Michelle]

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.

[Simon Hayes]

"Michelle" <mi**********@harmonyremote.com> wrote in message
news:56**************************@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=e...tngp13.phx.gbl

Simon

[Michelle Tran]

Thanks Simon.
Michelle.

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