September 1, 2011 at 4:30 pm
Hello,
I am working in sql 2005.
I have an sp (proc 0) that calls the sp below (proc 1) to find a specific returnvalue. This sp will be called several hundred times in a day and meet every scenario below, so I want to make sure that I am optimizing this to perform the best it can.
Query 1, 2 and 3 are completely different - meaning although the returnvalue is coming from the same table, the id is not so the way to get at the returnvalue is to join to different tables.
Should I put the queries in seperate sps? How many levels deep can you go calling an sp within an sp before there is a performance issue? I assume this would help with caching query plans for 1, 2, and 3; but i wanted to get someone elses opinion.
create proc 1 (
@id
@type,
@returnvalue output)
as
if @type = a
begin
run query 1 to find returnvalue for id
end
if @type = b
begin
find subtype for id
if subtype = c
run query 2 to find returnvalue for id
if subtype = d
run query 3 to find returnvalue for id
end
Thanks in advance,
C
September 2, 2011 at 7:57 am
If-statements in queries could cause some sub-optimal execution plans. See the following for reference: http://sqlinthewild.co.za/index.php/2010/12/14/do-if-statements-cause-recompiles/
My suggestion would be to have separate procs if the queries are that different and it makes sense to do so.
You can nest stored procedures up to a max of 32-levels according to BOL (http://msdn.microsoft.com/en-us/library/ms190607.aspx). I would be cautious not to go overboard with the nesting though, as it could make troubleshooting very cumbersome and difficult.
I don't think the nesting will have any impact on performance, but you have to keep in mind what might happen if one of the nested sp's fail...you should probably think of encapsulating the calls in explicit transactions.
Hope this helps.
September 2, 2011 at 9:00 am
In general, in these situations, I nest stored procedures. It's not like nesting views or functions, so performance should be fine. Check the execution plans of your queries to be sure they're working well though. That's completely independent from the nesting question.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply