Geeks With Blogs

News
DevJef's Mumbo-Jumbo «There's a bit of SQL in all of us»

In order to test Performance in SQL Server, normally you would take a look at how may I/O or how many cycles a statement takes to complete. To make it less complex, you can take a look at how long a statements takes, just by looking at the execution-time. In most cases, network latency, SQL Server hardware and workstation performance are also included in this result (time to create result set, and time needed to send across the internet or internal network). And if a statement takes les then a second to execute, you need to perform some magic with dates, times, etc. It is what it sounds like: overkill. There is a simple way to test performance in SQL Server.

 

In order to understand the statement, first a small explanation of the keywords:

 

@@CPU_BUSY: Returns the ticks “spend” since the last execution of the statement

ISNULL: Replaces NULL with the specified replacement value

COALESCE: Returns the first non-NULL value from it’s replacement values

 

So basically ISNULL and COALESCE provide you with the same functionality( at least, the way I used it in the script below), except COALESCE can be used to prevent the inevitable bunch of nested ISNULL statements.

 

To test the statements, I used the following script:

 

  
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY DECLARE @StartDate DATETIME SET @StartDate = GETDATE() WHILE @Loops <= 1000000
BEGIN
    IF COALESCE('123', '456') = '456'
            PRINT 1
    SET @Loops = @Loops + 1
END PRINT 'COALESCE, both non-NULL'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO --================================================== DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE() WHILE @Loops <= 1000000 BEGIN
    IF ISNULL('123', '456') = '456'
            PRINT 1
    SET @Loops = @Loops + 1
END PRINT 'ISNULL, both non-NULL'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO

If you execute this statement, you will see the following results:

 

COALESCE, both non-NULL
Total CPU time: 16
Total milliseconds: 566
 
ISNULL, both non-NULL
Total CPU time: 19
Total milliseconds: 640

 

As you can see, the COALESCE takes less time to complete then the ISNULL function. The total CPU time needed is shorter, and looking at the used time, it saves you 74 milliseconds. In this case it isn’t that much, but with larger queries this can save you a lot of time.

 

The actual CPU time and milliseconds vary because of different hardware, caching, etc. But the outcome is the most important.

 

For those people who want to comment about COALESCE; I know! COALESCE is not exactly the same as ISNULL, but like I said, in this script it is!

Posted on Wednesday, September 28, 2011 2:33 PM SQL Scripts , SQL Server | Back to top


Comments on this post: Quick performance test in SQL Server

# re: Quick performance test in SQL Server
Requesting Gravatar...
I like this. Quick and simple and a nice metric to provide an easy, comparative baseline.
Left by Terry Grignon on Jan 30, 2013 3:59 PM

# re: Quick performance test in SQL Server
Requesting Gravatar...
Thanks for the script! I am wondering what does the following output mean?

Arithmetic overflow occurred.
COALESCE, both non-NULL
Arithmetic overflow occurred.
Total CPU time: 0
Total milliseconds: 53243

Arithmetic overflow occurred.
ISNULL, both non-NULL
Arithmetic overflow occurred.
Total CPU time: 0
Total milliseconds: 51116
Left by D on May 16, 2017 6:53 AM

Your comment:
 (will show your gravatar)


Copyright © DevJef | Powered by: GeeksWithBlogs.net