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 |
|
RoyalSher
Yak Posting Veteran
95 Posts |
 Posted - 2003-05-01 : 01:11:49
|
| Hi,I was just wondering what would be effect on performance of SP's which are nested.?Say I have a 4 or .. 8 Proc's..Proc1 Calls Proc2Proc2 Calls Proc3Proc3 Calls Proc4Proc5 Calls ..........Can ne body help in analysing extent of performance ?RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-05-01 : 03:52:21
|
| Try running profiler. It will help you analyse the performance.Damian |
 |
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-05-01 : 23:39:50
|
| HTH,Thanx Merkin for your reply. Does ne body else in the forum have an idea what can be the results ? As a thumb rule if ne for my query !!RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-02 : 00:10:59
|
| Need more specifics.If you can post the sp's that would be great.Maybe some ddl and what you're planning we can give you better opionions. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-05-02 : 01:01:25
|
| Or.... run profiler and find out. I was being serious. It is 100% the best way to find out what is the best option for performance in your application.Damian |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-05-02 : 01:32:57
|
| Royal, I have to agree with Merkin and Valter on this one. The performance is completely based on what each one is doing. Nesting them may or may not affect performance significantly as opposed to lumping them together. There is no generic 1-size-fits-all answer here. But here are a couple of things to watch out for...1) Recursive calls. This is a situation where Proc1 calls Proc2 which calls Proc3 which calls Proc1 again. This may be more likely to happen in triggers instead of SPs, but be aware of it and plan accordingly.2) Recompilation. I can't go into all the factors that cause sprocs to be recompiled, but by having multiple sprocs in your process, I'd expect the risk of recompilation to go up, unless you specifically plan around this and design to prevent it.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-05-02 : 03:14:15
|
| HTH,Thanx yrybody for ur help. I think I shall do profiling first and post the problems.Thanx once again.RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
|
|
|
|
|
|
Query on Performance of Nested SP's