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 |
|
john.burns
Posting Yak Master
100 Posts |
 Posted - 2006-10-20 : 14:11:35
|
| Has anyone had great performance gains by using 'with recompile' on proceduresthat take 50+ parameters??Thanks |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-20 : 14:23:38
|
| That depends. 'with recompile' will not automatically improve anything no matter how many parameters there are. Are the parameters used in queries that would be optimized differently, are there up to date statistics on the tables used in the proc, are there indexes on the tables used in the proc etc. You need to do all the normal best practice things to optimize your queries, then test the proc with 'with recompile' - if it performs better then good, if not, then it's just a proc that isn't affected by a recompile (and many won't be). |
 |
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-10-20 : 14:30:49
|
| Yes the parameters I presume would have a drastic effect on the final query optimization |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-10-20 : 14:49:47
|
| General statement coming:One of the exceptions to "you shouldn't use dynamic sql" can be when an SP as tons of parameters but any given execution of the SP results in a very simple and efficient statement. I suppose that a non-dynamic solution could get a similar benefit from using "with recompile". I personally never used "with recompile" unless I was testing something.Are you trying to solve a performance problem?Be One with the OptimizerTG |
|
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-10-20 : 15:25:46
|
| Yes high CPU consuming stored procs |
|
|
|
|
|
|
with recompile