June 16, 2008 at 2:55 am
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.
Thanks!
June 16, 2008 at 4:47 am
Hello,
we can only guess if you don't post table structure (as a CREATE TABLE statement, including CREATE for indexes), some sample data and the entire query.
The first obvious guess is that the tables are not properly indexed - but there can be other reasons that will show up only after we know more (like what datatype is the date column, and so on).
BTW, you could also look up some posts and articles about "parameter sniffing" if you just want to find out, why the query performs differently with hardcoded values.
June 16, 2008 at 6:10 am
The query & table def are pretty huge, a bit much for a help forum.
I've got around the problem by loading the required rows of the FB table into a temp table and then using that in the main query.
Not perfect but it'll do until I have more time to look into it
June 16, 2008 at 6:55 am
I'd suggest two things, first, get an estimated execution plan with the parameters and compare it to an actual execution plan without. Also, because you're setting your date values to strings, you're getting implicit conversion of the data and that convert will prevent the use of indexes in most cases. I'd suggest getting the data type for the variables to datetime or smalldatetime, whichever is appropriate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2008 at 7:37 am
The datatype of the date field in the FB table is smalldatetime, same as the parameter variables.
I compared execution plans this morning & yes, the query takes entirely differnt path when the parameter variables are used.
I still dont understand why the parameter and the hardcoded value are treated differently 🙁
June 16, 2008 at 8:49 am
You're hitting a variation of parameter sniffing (here's Ken Henderson's description of parameter sniffing). Usually this is a good thing, but sometimes it isn't. Ken shows a method for helping, but it might not work in your case. You should go ahead and finish the procedure (like they do here). Then test that(just get an estimated execution plan for it for the initial test). It might work better than the parameterized query. If not, try using Ken's approach. If not that, look into the OPTIMIZE FOR query hint in Books Online.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2008 at 8:54 am
I would go for "RECOMPILE" statement option. That way you will get the appropriate plan for any value pair. This of course comes at a recompilation time price, it may or it maynot be prohivitive for your app but you are the only one who can tell 😉
* Noel
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply