March 20, 2009 at 12:39 pm
I was told some time ago to stay away from nesting proc calls in a stored procedure because the nested procs do not get an optimized execution plan or the the plan that it comes up w/ has nothing to do w the contents of the nested proc...
Is this true?
March 20, 2009 at 1:35 pm
A procedure is compiled at the point that it is called, providing there is no matching plan in cache. If there is no plan in cache, then the procedure is compiled and optimised in isolation (ignoring what may have happened before or where it was called from) and the current values of the parameters are used to optimise the queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2009 at 7:10 am
No, it's not true. Depending on how you're calling nested queries you may not get the plan you think you'd get, as Gail outlined, but the plans will be created and reused along the same lines as any other. As a matter of fact, in cases where your queries are following some kind of control flow, select here IF, etc. that nested queries work much better than having all the queries in one big procedure because each plan is created and recompiled individually (although statement level recompiles make this somewhat less of a problem in SQL Server 2005/2008).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply