March 28, 2011 at 8:16 am
I have a strange issue.
I have a procedure with two parameters. If I execute that procedure by giving some values to the parameters it took around 1 min.
But if I run its equivalent query by giving same values in where condition it's executing within 10 sec.
Can anybody explain why it's happening and what should I do to make the procedure to execute withing 10 sec?
Thank you in advance.
March 28, 2011 at 8:58 am
You should look up "Parameter Sniffing". It sounds like this is what's happening. Basically, when a stored procedure's first executed, it uses the first parameters supplied to generate and store a plan - in some cases this plan is sub-optimal for other sets of parameters.
If the parameter's you're supplying are representative, you may want to recompile the stored procedure, then run it with those parameters to store the new plan. If the parameters always differ wildly (from the perspective of the optimiser, e.g. they can range from being very selective to being quite wide) you may find that it's worth adding the recompile option to the procedure so that each execution produces a new plan (bearing in mind that this also has an overhead)
March 28, 2011 at 9:00 am
ashokdasari (3/28/2011)
I have a strange issue.I have a procedure with two parameters. If I execute that procedure by giving some values to the parameters it took around 1 min.
But if I run its equivalent query by giving same values in where condition it's executing within 10 sec.
Can anybody explain why it's happening and what should I do to make the procedure to execute withing 10 sec?
Thank you in advance.
Just out of curiosity... if you declare local variables in the procedure and set the values of them equal to the values of the parameter, then use the variables instead of the parameters does this improve performance?
i.e.
CREATE PROCEDURE test
@param1 int,
@param2 varchar(10)
AS
DECLARE @vara1 int
DECLARE @vara2 varchar(10)
SET @vara1=@param1
SET @vara2=@param2
If you do, then SQL 2008's parameter sniffing "optimization" may be in play here... in which case you can work around it by using the local variables instead of the parameters where necessary, or disabling it completely with Traceflag 4136.
March 28, 2011 at 9:59 am
Thanks to mtassin and HowardW.
Both of you are correct, "Parameter Sniffing" is what happening with my procedure. Now my problem is solved.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply