Performance issues calling stored proc from within a stored proc

Question

Hello,

I worked on a project that had the following requirement:

TableA is the parent table. Whenever any children records of TableA are updated the 'LastActivityDate' field in TableA should be updated with the current UTC date.

Now, I know there are many ways of doing this. My post is NOT about the many different ways this could be accomplished.

I originally suggested using triggers for this requirement and our DBAs shot me down because they don't want triggers in this DB (I don't know why, and it isn't important to my question). I ended up creating a stored proc that's only purpose was to update TableA.LastActivityDate. I then coded the update/insert stored procedures of TableA's children to call this stored proc. So, for example, a child's update stored proc would look like this:

Create Procedure TableB_UPD
(
 @TableBId INT
 @TableBName VARCHAR(30)
)
AS
BEGIN
 UPDATE dbo.TableB
 SET TableBName = @TableBName
 WHERE
  (TableBId = @TableBId)

 DECLARE @TableAId INT
 SELECT
  @TableAId = TableAId
 FROM
  dbo.TableB
 WHERE
  (TableBId = @TableBId)

 EXEC dbo.TableA_LastActivityDate_UPD @TableAId
END

It's pretty straight forward code, I call the dbo.TableA_LastActivityDate_UPD stored proc from within the TableB_UPD stored proc. When our DBAs saw this they refused to allow it in their DB. They told me that there is a huge performance hit from calling a stored procedure within a stored procedure. I have been unable to find any good online articles to support this statement (and the DBAs haven't been able to give me any either).

I've seen this kind of code in lots of databases and never heard of any performance issues until now. My question is this: Can someone explain the performance issues around this type of code? I would greatly appreciate references to articles as well.

This is in SQL Server 2005.

Thank you.

Answer 1

When in doubt, look at execution plans and SQL Profiler to make sure your procedures perform optimally. They can tell you more about the situation than we ever could.

The only issue I can think involving nested stored procedures that could even remotely be considered a problem would be error handling in nested stored procedures with transactions, but you don't seem to have that going on here. And really, my example is more of a case of "Write your stored procedure properly."

As it stands though, nested stored procedures being a "huge performance hit" is news to me, and I sure can't find anything supporting that statement either.

Answer 2

There's nothing wrong with your solution of nesting the procedure calls. But to appease your DBAs, why not just write out the update statement to update the LastActivityDate in the procedure that updates table B?

I'm sure a little bit of duplicated code will be the least of your problems in this database

Answer 3

Nested stored procs work fine. We use them pretty much constantly in a time-sensitive data warehouse situation. We have procs that call procs that call procs to format all the data on a monthly cycle once we receive it from our clients.

There is no degradation of performance doing it this way or running the procs directly or running them as queries - we have done extensive efficiency testing on almost all our procedures.

Based on your question, you may be working with completely incompetent DBAs. A little knowledge is a dangerous thing.

Answer 4

i'm with @mike, there shouldn't be any issues with your code. To me it sounds like they heard something or saw something once and it became law. Instead, prove to them that your code works. Ask them for a test instance and let it fly.

Answer 5

Regardless of performance, I would do it differently, in one transaction, and simpler:

Create Procedure TableB_UPD
(
 @TableBId INT
 @TableBName VARCHAR(30)
)
AS
BEGIN
 SET XACT_ABORT ON;
 BEGIN TRAN;
 UPDATE dbo.TableB
 SET TableBName = @TableBName
 WHERE
  (TableBId = @TableBId)

 UPDATE dbo.TableA
 SET LastActivityDate = CURRENT_TIMESTAMP
 WHERE
  (TableBId = @TableBId)

 COMMIT;
END

Answer 6

Hi, actually I have similar problem. Example

procA begin

create #temp table insert into #temp something

exec procB(here using temp table #temp)

end

Execution of this procedure last 7 sec, and if I copy same code from procedure B into procedure A duration is 2 sec, I am looking tuning template in profiler and even don't see those 5 sec... Both of procedures are quite complex (and there are also few indexed views in procB) I have no idea whats is happening... and if somebody can explain this I will appreciate. Thanks in advance