Sql Server Programming :: Problem: Trivial?


	Problem: Trivial? Hi! I have a Stored Procedure which takes 48 parameters and have a few hundreds of SQL-code lines. The SP uses few temporary tables, 10-15 selects, some aggregate functions, groupings etc... Case 1: When I execute SP's SQL-code as a 'simple' query (instead of input parameters I declare all the variables that procedure uses and set values to all of them, and just copy-paste the rest of SP's SQL-code (SP's 'body') - query execution time is about 60 times faster then other way (case 2 - SP call). Case 2: SP call: again - declaring the variables, setting the values to exactly same values, but this time really executing SP (EXECUTE spName @p1, @p2, ..., @p48) using variables as arguments. Question: Could anyone tell me what's the different between this two tests and what should I do to speed up the SP execution? I suspect it might be something trivial - I just can't get it!? Same code, same parameters (number and values), same result - but first way is that much faster (case 1: 2-3 sec vs. case 2: 2-3 min)... DBMS is (off course) SQL Server 2005, and I also tried '... WITH RECOMPILE' option. (Excuse my bad English and thanks for any possible answers.) Google "parameter sniffing". -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Bobby" <bobby_orla@yahoo.com> wrote in message news:f3i8ko$ji8$1@ss408.t-com.hr... Hi! I have a Stored Procedure which takes 48 parameters and have a few hundreds of SQL-code lines. The SP uses few temporary tables, 10-15 selects, some aggregate functions, groupings etc... Case 1: When I execute SP's SQL-code as a 'simple' query (instead of input parameters I declare all the variables that procedure uses and set values to all of them, and just copy-paste the rest of SP's SQL-code (SP's 'body') - query execution time is about 60 times faster then other way (case 2 - SP call). Case 2: SP call: again - declaring the variables, setting the values to exactly same values, but this time really executing SP (EXECUTE spName @p1, @p2, ..., @p48) using variables as arguments. Question: Could anyone tell me what's the different between this two tests and what should I do to speed up the SP execution? I suspect it might be something trivial - I just can't get it!? Same code, same parameters (number and values), same result - but first way is that much faster (case 1: 2-3 sec vs. case 2: 2-3 min)... DBMS is (off course) SQL Server 2005, and I also tried '... WITH RECOMPILE' option. (Excuse my bad English and thanks for any possible answers.) -----------------------------------------------Reply----------------------------------------------- Where did you use WITH RECOMPILE? In the proc definition? When calling the proc? As a hint to individual statements in the proc? -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://sqlblog.com "Bobby" <bobby_orla@yahoo.com> wrote in message news:f3i8ko$ji8$1@ss408.t-com.hr... - Hide quoted text - > Hi! > I have a Stored Procedure which takes 48 parameters and have a few > hundreds of SQL-code lines. The SP uses few temporary tables, 10-15 > selects, some aggregate functions, groupings etc... > Case 1: > When I execute SP's SQL-code as a 'simple' query (instead of input > parameters I declare all the variables that procedure uses and set values > to all of them, and just copy-paste the rest of SP's SQL-code (SP's > 'body') - query execution time is about 60 times faster then other way > (case 2 - SP call). > Case 2: > SP call: again - declaring the variables, setting the values to exactly > same values, but this time really executing SP (EXECUTE spName @p1, @p2, > ..., @p48) using variables as arguments. > Question: > Could anyone tell me what's the different between this two tests and what > should I do to speed up the SP execution? > I suspect it might be something trivial - I just can't get it!? > Same code, same parameters (number and values), same result - but first > way is that much faster (case 1: 2-3 sec vs. case 2: 2-3 min)... > DBMS is (off course) SQL Server 2005, and I also tried '... WITH > RECOMPILE' option. > (Excuse my bad English and thanks for any possible answers.) "Tom Moreau" <t@dont.spam.me.cips.ca> wrote in message news:uV1z2IkoHHA.4124@TK2MSFTNGP02.phx.gbl... > Google "parameter sniffing". Can you please tell me how I can implement: if (data is atypical) if (data has significantly changed) "Parameter sniffing", recompiles blablabla...really means Mr./Ms. user it's your problem. The real issue is how naive people are to swallow this non-sense. Where there is no shame arrogance apparently rules. If enough users get out their 'return to sender' stamp the design mess might get straigthened out. You say Google "parameter sniffing". I say Google "gullible". http://beyondsql.blogspot.com -----------------------------------------------Reply----------------------------------------------- "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:%232DIxOkoHHA.1244@TK2MSFTNGP04.phx.gbl... > Where did you use WITH RECOMPILE? > In the proc definition? When calling the proc? I tried both (not in the same time). > As a hint to individual statements in the proc? This I did not try. -----------------------------------------------Reply----------------------------------------------- "Tom Moreau" <t@dont.spam.me.cips.ca> wrote in message news:uV1z2IkoHHA.4124@TK2MSFTNGP02.phx.gbl... > Google "parameter sniffing". Your hint has simply enlightened me! ;-) All input parameters had (and still have) some typical default values, so I guess optimizer and 'parameters sniffing' have caused me that huge performance delay. At first - I tried to remove the default values, but it didn't change performance time at all (!?). Finally I declared new variables (inside SP); set them all to their associated input values (left original parameter defaults) and let the rest of the procedure use only internal variables. (Off course - I used old parameter identifiers for variable names and renamed parameters.) It works just perfect now! Thanks a lot... Regards! -----------------------------------------------Reply----------------------------------------------- "Steve Dassin" <rac4sqlnospam@net> wrote in message news:em4GWMloHHA.3856@TK2MSFTNGP03.phx.gbl... > You say Google "parameter sniffing". > I say Google "gullible". Thanks for your support - but Tom's hint has actually helped me. :-) Regards! -----------------------------------------------Reply----------------------------------------------- Happy to help. :-) -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Bobby" <bobby_orla@yahoo.com> wrote in message news:f3l2cd$dqg$1@ss408.t-com.hr... "Tom Moreau" <t@dont.spam.me.cips.ca> wrote in message news:uV1z2IkoHHA.4124@TK2MSFTNGP02.phx.gbl... > Google "parameter sniffing". Your hint has simply enlightened me! ;-) All input parameters had (and still have) some typical default values, so I guess optimizer and 'parameters sniffing' have caused me that huge performance delay. At first - I tried to remove the default values, but it didn't change performance time at all (!?). Finally I declared new variables (inside SP); set them all to their associated input values (left original parameter defaults) and let the rest of the procedure use only internal variables. (Off course - I used old parameter identifiers for variable names and renamed parameters.) It works just perfect now! Thanks a lot... Regards!