July 23, 2008 at 3:04 pm
I have two options to compare in terms of performance executing multiple stored procedures inside one, or taking the code in the procedures and putting the select statements into one procedure. Here are the examples:
1. Single Stored Procedure that contains a
SELECT x FROM y WHERE z ORDER BY zz statement
exec spsProc2
exec spsProc3
2. Single Stored Procedure that contains same code above but uses the three procedures SELECT statements in one procedure:
SELECT x FROM y WHERE z ORDER BY zz statement
SELECT x FROM y WHERE z ORDER BY zz statement
SELECT x FROM y WHERE z ORDER BY zz statement
I've found that in the Proc Cache executing example 1 generates three records in the proc cache, where as example 2 only has one record. (I did DBCC FREEPROCCACHE in between executes)
I've also found that the execution plans (graphical version) use the same physical and logical operators to execute the "master" procedure.
What other things might make one choice better or worse for performance that I'm not thinking of?
July 23, 2008 at 9:20 pm
Is y a OLTP or OLAP table? I would start going down and analyzing which proc had the smallest transaction scope, especially if y is a heavily used oltp table.
July 24, 2008 at 7:38 am
As long as you don't have conditional execution of the procedures, you can really go either way. You should get identical plans. This becomes a problem when people put IF statements in like this:
IF @somevalue = 42
BEGIN
SELECT....FROM....Here WHERE Stuff
END
ELSE
BEGIN
SELECT....FROM... SomewhereElse WHERE OtherStuff
END
Here what happens is the optimizer will have to create different plans as @somevalue changes because each time it finds a different path through the query it has to generate a new plan. But your example doesn't hit this at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 24, 2008 at 8:08 am
Thanks, yeah there are no conditional statments just simple exe spsProc.
July 24, 2008 at 10:04 am
I use the Master Proc-Sub Procs idea a lot. Makes for more modular code, much easier to document and maintain.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply