I don't remember how I ran across this initially, but I have a very complicated stored proc (500 lines+) that uses some dozen parameters. I found that when I reassign the parameters to a local variable and then only use the local variables in the actual code, the stored proc runs much faster. Is any one come across the situation???can any one guess what could be the reason,any advantages/disadvantages using the parameters in this way??? Any help is Greately apprciated. Here is the sample of StoredProcedure CREATE PROCEDURE dbo.ResultsGet @xFilter_A as uniqueidentifier, @xFilter__B as uniqueidentifier = null, @xFilter__c as uniqueidentifier = null AS DECLARE @Filter_A as uniqueidentifier, @Filter_B as uniqueidentifier, @Filter_C as uniqueidentifier SET @Filter_A = @xFilter_A SET @Filter_B = @xFilter_B SET @Filter_C = @xFilter_C Thanks Vishnu
Ive not encountered this before either. I would start by viewing the execution plans of the two versions and see if anything obvious is thrown up.
There's a bit on Google groups about this, posted awhile back but found it. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=Oa#Fb2ecBHA.2220@tkmsftngp03&rnum=1 Cheers Shaun World Domination Through Superior Software
qsv, This is due to "parameter sniffing". If you have a number of parameters with defaults as null, sql server will create the execution plan, based on the basis of these nulls. There are 2 workarounds: a. What you have done above? Assign the parameters to local variables. b. Try and use defaults which are not nulls. I understand this is not possible always. -CR
Hi Shaun and chandru Thanks a lot for your efforts solving the problem,With your postings I understood that "parameter sniffing" is the factor governing the performence.I could not find much documentation on BOL.Any way thanks for your inputs GSV
