THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Will wrapping up code in a reusable stored procedure hurt performance?

In T-SQL programming, stored procedure calls are not free, and nested stored procedure calls cost even more. First, we shall prove it by running benchmarks. Next, we shall determine if the cost of calling stored procedures is high as compared to the rest of workload.

 

Proving that stored procedure calls are not free

Let us begin with a very simple benchmark, using a trivial stored procedure that increments a variable:

CREATE PROCEDURE dbo.IncreaseByOne @i INT OUT
AS
    BEGIN
;
      
SET @i = @i + 1 ;
    
END ;
GO
  

In order to get more or less meaningful comparison, we shall invoke the procedure 100K times, and compare its real execution costs versus the cost of incrementing the variable directly in the body of the loop, as shown in the following script:

CREATE PROCEDURE dbo.DirectUpdateInLoop
    
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
              
SET @i = @i + 1 ;
            
END ;
        
SELECT @i AS [@i] ;
    
END ;
GO

CREATE PROCEDURE dbo.ProcedureCallInLoop
    
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
EXEC dbo.IncreaseByOne @i = @i OUT ;
            
END ;
        
SELECT @i AS [@i] ;
    
END ;
GO
  

EXEC dbo.DirectUpdateInLoop @NumIterations = 100000;
GO
EXEC dbo.ProcedureCallInLoop @NumIterations = 100000;
  

To exclude parse and compile time from our comparison, we can just invoke our stored procedures more than once, and disregard their first executions.

The second procedure, dbo.ProcedureCallInLoop , consistently runs much slower -  970 ms vs. 93 ms on my laptop, running SQL 2008 R2. Naturally, your mileage may vary.

 

Proving that nested stored procedure calls are even more expensive

Clearly, invoking a stored procedure is not free. To determine if nested stored procedure calls are more expensive, we shall create and run the following modules:

CREATE PROCEDURE dbo.WrappedIncreaseByOne @i INT OUT
AS
    BEGIN
;
      
EXEC dbo.IncreaseByOne @i = @i OUT ;
    
END ;
GO

CREATE PROCEDURE dbo.WrappedProcedureCallInLoop
    
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
EXEC dbo.WrappedIncreaseByOne @i = @i OUT ;
            
END ;
        
SELECT @i AS [@i] ;
    
END ;
GO

EXEC dbo.WrappedProcedureCallInLoop @NumIterations = 10;
GO

EXEC dbo.WrappedProcedureCallInLoop @NumIterations = 100000;

Nested calls are consistently slower - dbo.WrappedProcedureCallInLoop runs in 1620 ms vs. 970 ms for the previous tests, dbo.ProcedureCallInLoop.

 

Comparing the overhead of calling procedures to the overhead of other actions

As we have seen, stored procedure calls are not free, especially nested ones. However, our benchmarks were not very realistic - our stored procedures were extremely trivial.

If a stored procedure actually executes some DML, how big is the overhead of calling it as compared to the overhead of running the DML inside it?

The following procedure executes just a little bit of DML - it updates one row:

CREATE TABLE dbo.Counters
    
(
      
CounterId INT NOT NULL
                    
CONSTRAINT PK_Counters PRIMARY KEY ,
      
Amount INT NOT NULL
    ) ;
GO
INSERT INTO dbo.Counters
        
( CounterId, Amount )
VALUES  ( 1,
          
0  
          
) ;
GO
CREATE PROCEDURE dbo.IncreaseCounterByOne @CounterId INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
UPDATE  dbo.Counters
        
SET     Amount = Amount + 1
        
WHERE   CounterId = @CounterId ;
    
END ;
GO
  

As before, we shall be invoking it in a loop, as follows:

CREATE PROCEDURE dbo.IncreaseCounterViaProcedure
    
@CounterId INT ,
    
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
EXEC dbo.IncreaseCounterByOne @CounterId = @CounterId ;

              
SET @i = @i + 1 ;
            
END ;
    
END ;
GO
  

Let us cut-and-paste the body of dbo.IncreaseCounterByOne into the calling module and see if i it improves the performance:

CREATE PROCEDURE dbo.IncreaseCounterViaDirectUpdate
    
@CounterId INT ,
    
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
UPDATE  dbo.Counters
                
SET     Amount = Amount + 1
                
WHERE   CounterId = @CounterId ;
              
              
SET @i = @i + 1 ;
            
END ;
    
END ;
GO
 

Cut-and-pasting the body of dbo.IncreaseCounterByOne into the loop definitely improves the performance, but not very much: CPU goes down from 3400 ms (dbo.IncreaseCounterViaProcedure) to 2400 ms (dbo.IncreaseCounterViaDirectUpdate), and duration does not change very much either.

For more complex stored procedures, the overhead of making a call should be even less pronounced as compared to the cost of executing DML.

Conclusion

As we have seen, sometimes we can eliminate stored procedure calls and save some CPU. However, we should not expect huge savings, unless we are flattening out deeply nested modules.

Also we have seen that although wrapping up reusable code in a stored procedure does slow down the execution, usually the performance penalty is relatively small.

My other posts discussing T-SQL code reuse and performance are as follows:

More specific queries may run faster, but we should not overdo it.

Reuse Your Code with Table-Valued UDFs

Comparing the cost of INSERT ... EXEC and its alternatives
Published Wednesday, July 20, 2011 2:03 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Your sentence beginning with "For more complex stored procedures, the " seems to be truncated.

July 20, 2011 10:34 PM
 

Alexander Kuznetsov said:

Fixed. Thanks, Adam!

July 20, 2011 10:48 PM
 

Dave Wentzel said:

I find I call stored procedures so I can further "modularize" complex logic that I may wish to reuse elsewhere.  A scalar function or "macro" TVF may not work due to complicated logic.  So I create a temp table in the caller with a set of keys and then populate that temp table with my complex logic that I want to modularize in the called proc.  I can then follow that pattern whenever I want to reuse the called proc logic elsewhere.  I've always wondered what the overhead of calling these reusable procs is.  This seems to be a more real-world example.  I'm not sure calling these reusable procs in a loop is as realistic as calling the outer proc that has to make the sub-proc call.  Yes I know we can use table-valued params, but not all of us are able to use the latest releases of SQL Server.  

July 21, 2011 4:27 PM
 

Alexander Kuznetsov said:

Dave,

Can you provide more details about your scenario? I am not sure if I understand what you mean.

Anyway, one single stored procedure call is too fast for benchmarking. We cannot compare performance until we repeat it at least tens of thousands of times.

July 21, 2011 5:04 PM
 

Dave Wentzel said:

pseudo code:

create proc Caller AS

BEGIN

--do some work but call a common, "expensive" module that aggregates data or whatever

CREATE TABLE #something (id int, CalcA int, CalcB int...)

EXEC CalledProc

--do more work

END

GO

create proc CalledProc AS

BEGIN

UPDATE #something SET...

END;

--CalledProc can be called from lots of different callers, not just Caller, and it may be called with frequently.  The question is, from a performance perspective only, how much more expensive is it to "in-line" CalledProc's common code vs making the calls to CalledProc directly?  I hope that made better sense.  Again, I'm assuming CalledProc needs to be reusable and performant and we can't do this in an inline TVF due to limitations and we aren't on a release that yet supports table parameters.  

July 21, 2011 9:52 PM
 

Alexander Kuznetsov said:

Dave,

Now I understand your scenario, thanks! Temporary tables and table variables also incur overhead. I intend to discuss it soon.

July 22, 2011 12:24 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement