WHERE clause problem with input parameters

[Waynest]

Hi

I have a query which takes 1 sec to return results when the WHERE clause is as follows

WHERE (FT.FT_BAD_TRAN=0)
AND (FB.FB_DATE BETWEEN '20080602' AND '20080608')


However, the query is to become a stored procedure with input parameters. When I declare & set variables for the date range the query just executes indefinitely & never returns rows:

DECLARE @FromDate smalldatetime
DECLARE @ToDate smalldatetime
SET @FromDate = '20080602'
SET @ToDate = '20080608'

WHERE (FT.FT_BAD_TRAN=0)
AND (FB.FB_DATE BETWEEN @FromDate AND @ToDate)

A debug message shows the date variables are set correctly

Any ideas what could be going on? The database is an ERP type product with large amounts of data. There is a date index on the FB table which I'm guessing is not being used when parametes are in the WHERE clause but I have no idea why this would be.

Thanks!

 

[bborissov]

How about if you change it to:

WHERE  (FT.FT_BAD_TRAN=0)
   AND (FB.FB_DATE >= @FromDate AND FB.FB_DATE <= @ToDate)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Waynest]

No difference already tried that. Also tried various CONVERT things to no avail.

The execution plans are very different when using hard coded values vs parameter ones, I don't get why its doing this.

 

[ca8msm]

Could it be an issue with parameter sniffing?

[!]
-------------------------------------------------------

Mark,

Join me at the new IT Community of the 21st Century: [!][URL unfurl="true"]http://lessthandot.com[/!][/url]

[/!]

 

[bborissov]

What is the type of FB.FB_DATE?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Waynest]

its smalldatetime, same as the variables I used

The usual fix for param sniffing as I understand it is to transfer the values of ypur input params to local variables and use those within your query... but I'm geting the issue before my query has made it to a SP.

Ive got the query up to speed by loading the rows I need from FB into a temp table and then using that in the main query but its kind of skiring around the problem.

Still perplexed by this, never come across it in umpteen years of sql

 

[SQLSister]

HOw are the execution plans different? Is the second not using the date index? If so can you force it to do so using index hints?

"NOTHING is more important in a database than integrity." ESquared