Quick performance test in SQL Server
November 4, 2011 Leave a comment
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!