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

Comparing the cost of INSERT ... EXEC and its alternatives

It is well known that INSERT ... EXEC is not free, but how expensive is it as compared to its alternatives? We shall run benchmarks and see that:

  • Invoking a multi-statement UDF is approximately two times faster than capturing the output of a stored procedure in a table variable via INSERT ... EXEC;
  • INSERT ... EXEC into a table variable is approximately two times faster than INSERT ... EXEC into a temporary table;
  • Inline UDFs are much cheaper than multi-statement UDFs;
  • The cost of invoking a multi-statement UDF or running INSERT ... EXEC may be several times higher than the cost of running the DML inside the UDF or stored procedure.

We shall only run benchmarks and estimate real execution costs. Whenever we choose whether to cut-and-paste code or to reuse it, whether via INSERT...EXEC, or in a multi-statement UDF, knowing these numbers may be very useful.

For an in-depth analysis of what contributes to the overhead, make sure that you have read the following great post by Adam Machanic:
The Hidden Costs of INSERT EXEC

Also when we use INSERT...EXEC a lot, we increase the workload on tempdb, but this is beyond the scope of this post.

Determining the cost of INSERT ... EXEC into a table variable.

The following stored procedure is very fast, so that the cost of its execution should be quite low. Also it does not contain any DML and as such it does not perform any reads when it runs.

CREATE PROCEDURE dbo.GetProduct @int1 INT, @int2 INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SELECT  @int1 * @int2 AS Product ;
        
RETURN @@ERROR ;
    
END ;
GO


Suppose also that we need the product of two integers in an output parameter, not in a result set. To accomplish that, we certainly can capture its output via INSERT ... EXEC, as follows:

CREATE PROCEDURE dbo.ReuseGetProduct
    
@int1 INT ,
    
@int2 INT ,
    
@product INT OUT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
DECLARE @res TABLE ( product INT ) ;
        
INSERT  @res
                
( Product
                
)
                
EXEC dbo.GetProduct @int1 = @int1, @int2 = @int2 ;
        
SELECT  @product = Product
        
FROM    @res ;
    
END ;
GO

Alternatively, we can cut and paste some code and get the product of two integers directly into an output parameter:

CREATE PROCEDURE dbo.GetProductDirectly
    
@int1 INT ,
    
@int2 INT ,
    
@product INT OUT
AS
    BEGIN
;
        
SET @product = @int1 * @int2 ;
    
END ;
GO

To benchmark these two approaches, let us create and execute the following two procedures:

CREATE PROCEDURE dbo.LoopReuseGetProduct
AS
    BEGIN
;
        
DECLARE @i INT ,
            
@product INT ;
        
SET @i = 1 ;
        
WHILE @i <= 100000
            
BEGIN ;
                
EXEC dbo.ReuseGetProduct @int1 = 1, @int2 = 2,
                    
@product = @product OUT ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO
DROP PROCEDURE dbo.LoopGetProductDirectly
CREATE PROCEDURE dbo.LoopGetProductDirectly
AS
    BEGIN
;
        
DECLARE @i INT ,
            
@product INT ;
        
SET @i = 1 ;
        
WHILE @i <= 100000
            
BEGIN ;
                
EXEC dbo.GetProductDirectly @int1 = 1, @int2 = 2,
                    
@product = @product OUT ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO

EXEC dbo.LoopReuseGetProduct ;
GO
EXEC dbo.LoopGetProductDirectly ;

The difference in performance is dramatic: while LoopGetProductDirectly uses 1094 ms CPU and no reads, LoopReuseGetProduct uses 21000 ms CPU and performs 1.9M reads.

So the overhead of every INSERT...EXEC into a table variable in this particular case is approximately 19 reads and 0.2 ms of CPU.

Note: if you are wondering why our benchmarks are wrapped as stored procedures, read an excellent post by Linchi Shea entitled "Performance impact: stored procedures, SQL batches, and CPU usage"

 Comparing INSERT ... EXEC into a table variable vs. INSERT ... EXEC into a temporary table

As we have seen, INSERT ... EXEC into a table variable is not cheap. Let us replace the table variable with a temporary table and see if it runs faster:

ALTER PROCEDURE dbo.ReuseGetProduct
    
@int1 INT ,
    
@int2 INT ,
    
@product INT OUT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
CREATE TABLE #res( product INT ) ;
        
INSERT  #res
                
( Product
                
)
                
EXEC dbo.GetProduct @int1 = @int1, @int2 = @int2 ;
        
SELECT  @product = Product
        
FROM    #res ;
    
END ;
GO
  

When we rerun LoopReuseGetProduct, the performance is even worse: it uses 41600 ms CPU and performs 4.6M reads.

So the overhead of every INSERT...EXEC into a temporary table in this particular case is approximately 46 reads and 0.4 ms CPU.

 

Comparing INSERT ... EXEC into a table variable vs. invoking a UDF

To reuse our code, we also can wrap it in a multi-statement UDF, or in an inline one. As before, we shall wrap our benchmarks in stored procedures:

CREATE FUNCTION dbo.GetProduct_MultiStatement ( @int1 INT, @int2 INT )
RETURNS @ret TABLE ( Product INT )
AS
    BEGIN
;
        
INSERT  @ret
                
( Product )
                
SELECT  @int1 * @int2 ;
        
RETURN ;                
    
END ;
GO

CREATE PROCEDURE dbo.LoopGetProduct_MultiStatement
AS
    BEGIN
;
        
DECLARE @i INT ,
            
@product INT ;
        
SET @i = 1 ;
        
WHILE @i <= 100000
            
BEGIN ;
                
SELECT  @product = product
                
FROM    dbo.GetProduct_MultiStatement(1, 1) ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO

CREATE FUNCTION dbo.GetProduct_Inline ( @int1 INT, @int2 INT )
RETURNS TABLE
AS
RETURN
    
( SELECT    @int1 * @int2 AS Product
    
) ;
GO

CREATE PROCEDURE dbo.LoopGetProduct_Inline
AS
    BEGIN
;
        
DECLARE @i INT ,
            
@product INT ;
        
SET @i = 1 ;
        
WHILE @i <= 100000
            
BEGIN ;
                
SELECT  @product = product
                
FROM    dbo.GetProduct_Inline(1, 1) ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO
  

Here are the results of our benchmarking:

  • dbo.LoopGetProduct_Inline   uses 563 ms of CPU and no reads;
  • dbo.LoopGetProduct_MultiStatement uses 10266 ms of CPU and performs exactly 1M reads.

Clearly the inline UDF performs much faster.

In this scenario, the overhead of invoking a multi-statement UDF is 10 reads and 0.1 ms of CPU.

 

Is this overhead high as compared to the cost of executing DML?

If our DML is fast, it may take less resources to execute our DML than to invoke a multi-statement UDF or to run an INSERT...EXEC. To demonstrate that, let us create a small table with just 10K rows:

CREATE TABLE dbo.Products
    
(
      
Multiplier1 INT NOT NULL ,
      
Multiplier2 INT NOT NULL ,
      
Product INT NOT NULL ,
      
CONSTRAINT PK_Products PRIMARY KEY(Multiplier1, Multiplier2 )
    ) ;
GO

INSERT  INTO dbo.Products
        
( Multiplier1 ,
          
Multiplier2 ,
          
Product
        
)
        
SELECT  n1.n ,
                
n2.n ,
                
n1.n * n2.n
        
FROM    dbo.Numbers AS n1
                
CROSS JOIN dbo.Numbers AS n2
        
WHERE   n1.n <= 100
                
AND n2.n <= 100 ;
GO
  

Also let us alter our procedures and UDFs to read from this table:

ALTER PROCEDURE dbo.GetProduct @int1 INT, @int2 INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SELECT  Product
        
FROM    dbo.Products
        
WHERE   Multiplier1 = @int1
                
AND Multiplier2 = @int2 ;
        
RETURN @@ERROR ;
    
END ;
GO

ALTER FUNCTION dbo.GetProduct_MultiStatement ( @int1 INT, @int2 INT )
RETURNS @ret TABLE ( Product INT )
AS
    BEGIN
;
        
INSERT  @ret
                
( Product
                
)
                
SELECT  Product
                
FROM    dbo.Products
                
WHERE   Multiplier1 = @int1
                        
AND Multiplier2 = @int2 ;
        
RETURN ;                
    
END ;
GO

ALTER FUNCTION dbo.GetProduct_Inline ( @int1 INT, @int2 INT )
RETURNS TABLE
AS
RETURN
    
( SELECT    Product
      
FROM      dbo.Products
      
WHERE     Multiplier1 = @int1
                
AND Multiplier2 = @int2
    
) ;
GO
  

Also let us revert the INSERT...EXEC back to its original version, which uses a table variable and is two times faster than the one with a temporary table:

ALTER PROCEDURE dbo.ReuseGetProduct
    
@int1 INT ,
    
@int2 INT ,
    
@product INT OUT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
DECLARE @res TABLE ( product INT ) ;
        
INSERT  @res
                
( Product
                
)
                
EXEC dbo.GetProduct @int1 = @int1, @int2 = @int2 ;
        
SELECT  @product = Product
        
FROM    @res ;
    
END ;
GO
  

Let us rerun our benchmarks:

EXEC dbo.LoopReuseGetProduct ;
-- EXEC dbo.LoopReuseGetProduct ; cpu 21906 reads 2100115

GO
EXEC dbo.LoopGetProduct_MultiStatement ;
-- EXEC dbo.LoopGetProduct_MultiStatement ; cpu 11109 reads 1200010

GO
EXEC dbo.LoopGetProduct_Inline ;
--EXEC dbo.LoopGetProduct_Inline ; cpu 1079 reads 200000

Our table Products is small; it takes just two reads two perform a simple select ad look up the value in it. Because we look up the value 100K times, we use 200K reads to do the actual useful work.

In this scenario, less than 20% of CPU and reads are spent on executing the DML. The rest is the overhead of invoking a multi-statement UDF or of running an INSERT...EXEC.

Of course, if we are running more complex and/or slower DML, the ratio of useful work vs the overhead will be higher. As such, we might be more willing to reuse our code via INSERT...EXEC or via a multi-statement UDF, even though the overhead is quite substantial.

Conclusion

As we have seen, neither INSERT...EXEC nor the multi-statement UDF are cheap. Whenever we are deciding whether to use them, we need to consider their impact on performance, preferably by running benchmarks.

One more thing: I am running my benchmarks on SQL 2008 R2, on my laptop. The results I am getting on my servers are similar. However, you might get different results in other environments, on other versions of SQL Server. I encourage you to rerun my scripts, and post here whether you are getting similar results, and what is your environment.

 

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

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

 
Reuse Your Code with Table-Valued UDFs

Published Monday, July 25, 2011 8:15 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

 

Kenny said:

Nice post!

A link to Linchi Shea's post "Performance impact: stored procedures, SQL batches, and CPU usage" [http://sqlblog.com/blogs/linchi_shea/archive/2011/07/22/performance-impact-stored-procedures-sql-batches-and-cpu-usage.aspx] would be helpful.

August 1, 2011 10:28 AM
 

Alexander Kuznetsov said:

Kenny,

I concur, I added the link. Glad you liked the post.

August 1, 2011 9:43 PM
 

Quigley said:

The task within this field looks really diverse.

I do believe I would take pleasure in working for an agency doing casual work so that

at some point I would do event management and the next, getting a job as a

security guard. The number of choices are usually unlimited,

it looks.

November 8, 2012 3:30 AM

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 as an agile developer.

This Blog

Syndication

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