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

Entering TRY blocks is not free, but still several times cheaper than catching exceptions.

The overhead of entering a TRY block is relatively small, but the overhead of catching an exception is more substantial. If we manage to use CASE or IF to prevent an exception, we can noticeably improve performance. We shall run benchmarks and see for ourselves - in our example, catching an exception will be approximately nine times more expensive than exiting the TRY block without it.

Entering TRY blocks is not free

In this section we shall wrap the body of a stored procedure in a TRY CATCH block, and measure the overhead. The following procedure does not use a TRY CATCH block:

CREATE ALTER PROCEDURE dbo.PerformDivision
    
@dividend INT ,
    
@divisor INT ,
    
@Quotient INT OUT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SELECT  @Quotient = CASE WHEN @divisor = 0 THEN NULL
                    
ELSE @dividend / @divisor
                
END ;
        
RETURN @@ERROR ;
    
END ;
GO

Let us benchmark it. Whether the divisor is zero or not, the procedure runs at the same speed:

CREATE PROCEDURE dbo.PerformDivision100kTimes
    
@dividend INT ,
    
@divisor INT
AS
    BEGIN
;
        
DECLARE @i INT ,
            
@Quotient INT ;
        
SET @i = 1 ;
        
WHILE @i <= 100000
            
BEGIN ;
                
EXEC dbo.PerformDivision @dividend = @dividend,
                    
@divisor = @divisor, @Quotient = @Quotient OUT ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO

EXEC dbo.PerformDivision100kTimes
    
@dividend = 1 ,
    
@divisor = 1
    
-- cpu 1250 ms
GO
EXEC dbo.PerformDivision100kTimes
    
@dividend = 1 ,
    
@divisor = 0
    
-- cpu 1250 ms
GO
  

Let us remove the CASE expression, so that the division can raise an exception, and wrap the division operator in a TRY CATCH block:

ALTER PROCEDURE dbo.PerformDivision
    
@dividend INT ,
    
@divisor INT ,
    
@Quotient INT OUT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
BEGIN TRY
            
SELECT  @Quotient = @dividend / @divisor ;
        
END TRY
        
BEGIN CATCH
            
SELECT  @Quotient = NULL ;
        
END CATCH ;
        
RETURN @@ERROR ;
    
END ;
GO
  

Even when the division does not raise an exception, the procedure runs 36% slower, 1703 ms vs 1250 ms:

EXEC dbo.PerformDivision100kTimes
    
@dividend = 1 ,
    
@divisor = 1
    
-- cpu 1703 ms
GO

Catching exceptions is a noticeable overhead 

When the divisor is zero, and division does raise an exception that is caught in the CATCH block, the procedure runs 4.36 times slower, 5453 ms vs 1250 ms:

EXEC dbo.PerformDivision100kTimes
    
@dividend = 1 ,
    
@divisor = 0
    
-- cpu 5453 ms
GO
 

As we have seen, catching exceptions in CATCH blocks is slow. In this particular scenario, it is approximately nine times more expensive than just entering the TRY block.

If we manage to use CASE or IF to avoid an exception, we can noticeably improve performance.

Published Tuesday, July 26, 2011 11:15 AM 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

 

Jack Corbett said:

Interesting.  What about a scenario where you are calling across a linked server and the linked server is unavailable?    

July 26, 2011 11:49 AM
 

Alexander Kuznetsov said:

Jack,

I am not an expert with linked servers. Can you run some benchmarks and post your results?

July 26, 2011 12:15 PM
 

Thiago Dantas said:

this is consistent with how .NET handles it

August 1, 2011 12:34 PM
 

Alexander Kuznetsov said:

Thiago,

Yes, I agree, this advice holds true for .Net as well.

August 1, 2011 9:45 PM
 

Jacob Wagner said:

Exception handling is intended to be just that, an exceotion. Predictable error conditions (especially common ones) should be checked for unless the overhead of checking them is excessive.

August 2, 2011 5:04 PM
 

Alexander Kuznetsov said:

Jacob,

Absolutely, and these benchmarks show a typical case when the overhead of checking, preventing an exception, is small compared to the overhead of catching it.

August 2, 2011 5:52 PM
 

Naomi said:

Interesting, thanks, I'll keep it in mind.

August 14, 2011 9:24 PM
 

David said:

Excellent and succinct post.  Thank you for doing the benchmarking work for us and showing us just how costly not doing proper validation can be.

August 15, 2011 12:16 AM
 

Alexander Kuznetsov said:

Naomi and David, I am glad you liked it.

The original version was probably five or six times longer. I was in no hurry, and took my time simplifying the post, until I could not find anything else to remove.

August 15, 2011 10:51 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 as an agile developer.

This Blog

Syndication

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