November 9, 2006 at 10:40 pm
Hi All,
I created an inline function which has 3 arguments when I am calling this function directly by pass constant values, it gives reply in miliseconds but when I'm passing values as variable it takes 3-4 seconds the same is happening when calling the same function from SP. Any help?
Example:
SELECT Z.* FROM DBO.FN_GETADJUSTEDSTOCK('2006-11-10','8754/100','WHITE') AS [Z]
Server replies in no-time
DECLARE @NOW DATETIME
DECLARE @ART_NO VARCHAR(20)
DECLARE @SHADE VARCHAR(20)
SET @NOW = '2006-11-10'
SET @ART_NO = 'ABC'
SET @SHADE = 'SHADE1'
SELECT Z.* FROM DBO.FN_GETADJUSTEDSTOCK(@NOW,@ART_NO,@SHADE) AS [Z]
Server takes 3-4 seconds.
Datatype of function's argument are matching with variables declared above.
Thanks, Govind
November 9, 2006 at 11:55 pm
Please don't be confused by different values I have used in my examples
November 10, 2006 at 1:59 am
you should examine the query plans for both calls, this type of behaviour is well documented probably under parameter sniffing. Casting the parameters in the function to variables within the function may solve the problem for this particular situation.
It's all to do with how the plan is initially created. There are some sneaky things you can do with query plans in 2005 which can resolve some of this.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply