Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LearningSQLKid
Yak Posting Veteran
51 Posts |
 Posted - 2011-10-14 : 00:25:36
|
| Hi Everyone.I have a strange problem with mt T-SQL. MY T-SQL qury rums fat in Query window but when i pack it in stored proc it is taking forever to run.Any idea why ?ThanksSelect Knowledge from LearningProcess |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-14 : 01:09:04
|
| are you having parameters in your procedure? If yes, try declaring variables inside procedure and assign values of parameters to them and use them in query instead and see if the execution time ins improving.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
LearningSQLKid
Yak Posting Veteran
51 Posts |
Posted - 2011-10-16 : 18:26:02
|
| Thanks Vishak16It worked for me. The stored procedure executed fast but is there any way to force the SQL to use best execution pla or does Microsoft recommend 'Parameter Snoffing' ?Select Knowledge from LearningProcess |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-17 : 01:28:51
|
| the reason for bad performance in above case is parameter sniffing. the solutions are either use variables inside as i suggested or use WITH RECOMPILE hint------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-17 : 02:36:32
|
| Well to add to what Visakh has already suggested you can add (OPTIMIZE FOR UNKNOWN) option or convert the query in dynamic sql and execute using sp_executesql so that the same query plan will be reused which was compiled for the first time.Also keep the statistics updated on the columns whose values you are passing as parameters to the SP.PBUH |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-17 : 04:00:51
|
| yep..thats a good option to use if you're on SQL 2008 and above------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Stored Proc Running Slow