THE SQL Server Blog Spot on the Web

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

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Performance: ISNULL vs. COALESCE

Mladen aka spirit1 posted a speed test of COALESCE vs. ISNULL. Reported result: COALESCE is faster.

But leave it to Anatoly Lubarsky to argue with what was posted. He posted his own speed test, showing that ISNULL is faster.

Anatoly's results showed a miniscule difference, "52 seconds" vs. "52-53 seconds". Mlanden's tests show a larger difference, around 15%. But I don't trust either of these results.

One thing in common with both of the tests I linked to, and which makes them both flawed, is that they return data to the client. This factors greatly into testing time. What if there was a network hiccup, or what if the client UI did something different when rendering the results? We're not testing the network's ability to send data or the client's ability to render it. What's being tested is very specific: Speed of COALESCE vs. ISNULL.

So this leads me to present Adam's Number 1 Rule of Performance Testing: When performance testing a specific feature, do everything in your power to test only that feature itself. Isolate your test as much as possible so that there is no way network traffic or unrelated UI code will get in the way. If you aren't careful about this, you will end up testing these other resources instead of your goal. And when testing against tables in SQL Server, it's especially important to be careful given SQL Server's caching mechanisms. So when testing using tables, I'll always throw out the first few test runs, or even restart the server between tests, in order to control the cache in whever way is logical for the feature being tested.

Before getting to my own tests, I'd like to jump off on a quick tanget. COALESCE vs. ISNULL? Who cares! This isn't a performance question, this is a question of standards-conformant vs. proprietary code. ISNULL is non-standard and provides less functionality than COALESCE. Yet a lot of SQL Server developers love to use it, I suspect because it's a lot easier to remember (and spell). So learn a new word and type two extra characters and you'll end up with more maintainable, more functional code. Sounds good to me -- which is why I am a big fan of COALESCE.

But I am still curious... Which is faster?

In this case, no test data is needed. We're testing performance of the COALESCE and ISNULL functions themselves, not using them to access data from a table. So the most effective test, in my opinion, is to run COALESCE and ISNULL a bunch of times each (one million) and see which runs faster:

 

DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()

WHILE @i <= 1000000
BEGIN
IF COALESCE('abc', 'def') = 'def'
PRINT 1
SET @i = @i + 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 @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()

WHILE @i <= 1000000
BEGIN
IF ISNULL('abc', 'def') = 'def'
PRINT 1
SET @i = @i + 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

DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()

WHILE @i <= 1000000
BEGIN
IF COALESCE(null, 'abc') = 'def'
PRINT 1
SET @i = @i + 1
END

PRINT 'COALESCE, first column null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO

DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()

WHILE @i <= 1000000
BEGIN
IF COALESCE(null, 'abc') = 'def'
PRINT 1
SET @i = @i + 1
END

PRINT 'ISNULL, first column null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO

You'll notice that I'm not using STATISTICS TIME to get the CPU and run time. Unfortunately, STATSTICS TIME returns once per statement, so it is not usable for this test -- we would wind up with one million 0 millisecond results. If you're running on a quiet server (and you should always run targeted performance tests on a quiet server; that may have to become Adam's Number 2 Rule if I can't think of something better) @@CPU_BUSY will give a close enough approximation of how much CPU time the test is using. And DATEDIFF will give us a good enough time reading. Note that the predicate in the IF statement will never return true, so we know that we're not testing our network or client.

I ran these tests several times on a few different servers, and ISNULL appears to pretty consistently out-perform COALESCE by an average of 10 or 12 percent. But that's the difference between 6 seconds and 5.3 seconds (the approximate average runtimes per test on my servers), over the course of a million exections. Hardly worth the functionality and standards compliance sacrifice, at least in the scenarios I use these functions for.


Published Wednesday, July 12, 2006 10:16 PM by Adam Machanic
Filed under: ,

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

 

Thomas said:

There's a possible pitfall with coalesce. Try this:

declare @t table (id int, f1 int, f2 int)

insert into @t values(1,1,null)

insert into @t values(2,1,null)

insert into @t values(3,1,2)

insert into @t values(4,1,null)

insert into @t values(5,1,5)

insert into @t values(6,1,null)

declare @p int

set @p = null

select f2, coalesce(@p,f2) as [coalesce(@p,f2)], [f2=coalesce] = case

when f2 = coalesce(@p,f2) then 'true' else 'false' end

from @t

February 5, 2008 3:49 AM
 

Alex said:

As to Thomas' post: Thomas, there is not pitfall with coalesce per say, as null does not equal null. You would have to check to see if the values are equal ~or~ both null. (If you want 'true' if both values are null.)

April 25, 2008 3:16 PM
 

Tewr said:

I think that it is hard to compare your test with the tests of Lubarsky at http://blogs.x2line.com/al/archive/2004/03/01/189.aspx . In his case, the different methods are compared in a context; to use as a wrapper for optional parameters. Why does this matter? Well, there is a big difference between putting a constant as the second parameter in ISNULL or COALESCE compared to a table value (like in tbl.field = COALESCE(@myOptionalInputVar, tbl.field)). If the optional paramter is not set (=is NULL), the server has to read the second value each time, to compare it with itself.

When using a lot of optional parameters (in my case I have seven) there is a _very_ significant difference in speed between using ISNULL() Or COALESCE(), compared to the last method ((@myOptionalInputVar IS NULL) Or (tbl.field = @myOptionalInputVar)), as the expression is short-circuited when the first sub-expression evaluates to true. Try including these thoughts in your tests, and your conclusion about scarifying standards might be different. At least if you use this functions in an optional parameter context :)

November 19, 2008 10:22 AM
 

Tewr said:

Scarifying = sacrificing, hehe

November 19, 2008 10:24 AM
 

Nickywan said:

The fourth test is invalid in this script !

Because you use COALESCE instead of ISNULL at line 48

December 30, 2008 5:40 AM
 

Adam Machanic said:

Hi Nickywan,

Nice catch--funny that it took four years for someone to notice :-)

Interestingly, any difference appears to be gone in SQL Server 2008.  I just ran a fixed version of the script on both a 2005 and 2008 instance, on the same machine.  Following are the results:

2005

-----------

COALESCE, both non-null

Total CPU time: 39

Total milliseconds: 1423

ISNULL, both non-null

Total CPU time: 44

Total milliseconds: 1500

COALESCE, first column null

Total CPU time: 40

Total milliseconds: 1393

ISNULL, first column null

Total CPU time: 45

Total milliseconds: 1513

-----------

2008

-----------

COALESCE, both non-null

Total CPU time: 44

Total milliseconds: 1716

ISNULL, both non-null

Total CPU time: 47

Total milliseconds: 1720

COALESCE, first column null

Total CPU time: 41

Total milliseconds: 1626

ISNULL, first column null

Total CPU time: 48

Total milliseconds: 1733

-----------

December 31, 2008 9:46 AM
 

Rahul said:

Hi Adam,

I am using ISNULL in the stored procedure in my sql server 2000. I am going to migrate to all to SQL server 2008. Will be there any problem? What we need to take care while migrating from 2000 to 2008 (stored procedures,tables,views,functions)

December 11, 2009 2:11 PM
 

Adam Machanic said:

Hi Rahul,

ISNULL is still very much supported in SQL Server 2008.

Before you upgrade, it is recommended that you run the SQL Server Upgrade Advisor tool which Microsoft created to help find potential problems. Google for more information.

December 11, 2009 3:25 PM
 

Ahmad said:

"and ISNULL appears to pretty consistently out-perform COALESCE by an average of 10 or 12 percent. "

and your results in the post

"Interestingly, any difference appears to be gone in SQL Server 2008.  I just ran a fixed version of the script on both a 2005 and 2008 instance, on the same machine.  Following are the results:"

....

its some controversies or .....?

Please do let explain?

Thank you.

May 20, 2010 3:15 AM
 

Kit said:

Why don't you fix the script instead of simply musing that it took 4 years for someone to notice the error? And update the summary? We have to read to the bottom of the comments to find the important info.

May 20, 2010 10:31 PM
 

Adam Machanic said:

Kit, what would be the fun in that? Reading blogs is an adventure.

Perhaps a new post is in order, but I generally don't like to update posts that are several years old unless it's to fix a major issue. And this one is certainly not major. As a matter of fact, hold that thought. New post coming soon.

May 21, 2010 9:32 AM
 

Rob G said:

How much of the time elapsed is down to the WHILE loop and the incrementing of @i?  Factoring that into the results will probably yield an even small different in timings between ISNULL() and COALESCE().

May 25, 2010 11:48 AM
 

Adam Machanic said:

Rob: It shouldn't matter how long those take. Assuming that the elapsed time for those operations is equivalent in both cases, then the only variable is the time it takes to do the ISNULL or COALESCE.

May 25, 2010 2:04 PM
 

Adam Machanic said:

Almost six years ago--in November of 2004--I posted what would turn out to be one of my most popular

June 30, 2010 3:12 PM
 

Jatin said:

I just tried following two SQL statements and if you compare execution plans of both on SQL 2008 R2,COALESEC is badly screwing it up..no clue why?

SELECT COALESCE

(

   (SELECT MAX(FirstName)

       FROM Person p2

       WHERE p2.ID=p1.ID),  

   ''

)

FROM Person p1

GO

SELECT ISNULL

(

   (SELECT MAX(FirstName)

       FROM Person p2

       WHERE p2.ID=p1.ID),  

   ''

)

FROM Person p1

GO

August 26, 2011 9:04 PM
 

Paul said:

I have seen coalesce give incorrect results on SQL2005 when used on a binary datatype. It doesn't seem to recognize the null even though a select of the column shows the null. So, beware using COALESCE on binary datatypes.

February 7, 2012 6:06 PM
 

Zak said:

Too right Paul, they do behave differently. When passing the result of a query (coalesce(value, 0) in which the record is null to a SqlReader, reader.GetBoolean reports the cast as invalid. IsNull(value, 0) behaves correctly though.

May 10, 2012 1:37 AM
 

Pavel Nefyodov said:

My tests are showing difference of approx 40% depending on the size of data set used. (change "coalesce" in the code below to "isnull").

DECLARE @i INT

SET @i = 1

DECLARE @CPU INT

SET @CPU = @@CPU_BUSY

DECLARE @StartDate DATETIME

SET @StartDate = GETDATE()

WHILE @i <= 100000

BEGIN IF coalesce(

(select col1 from (select 1 as col1, 3 as col2 union all select 4, 4 union all select 2, 4

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

union all select 1, 99

union all select 2, 99

union all select 3, 99

union all select 4, 99

union all select 5, 99

) as a1 where col1=col2)

, @i) = 100000

PRINT 'Done!'

SET @i = @i + 1

END

PRINT 'Coalesce'

PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)

PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))

PRINT ''

GO

May 28, 2013 1:01 PM
 

Adam Machanic said:

Pavel:

Interesting test case, and one I can repro on this end. Total time jumps from 1.1 seconds to 1.9 seconds... But remember that that's over 15.3 MILLION iterations. Unless you're in a serious, serious high performance computing environment, I don't think that .0000523ms per call, even if you're doing a whole lot of them, is cause for concern. There are probably a lot of other things that are going to greatly overshadow any gain or loss.

--Adam

May 28, 2013 2:20 PM
 

Pavel Nefyodov said:

Adam:

I see your point.

Very happy to receive a feedback from you.

May 28, 2013 5:49 PM
 

Pavel Nefyodov said:

Had another go at it.

What I wanted to achieve

1. Remove tricky multiple ISNULL/COALESCE calls.

2. Take advantage of the fact that COALESCE(expression1,...n) is equivalent of CASE

  WHEN (expression1 IS NOT NULL) THEN expression1

  WHEN (expression2 IS NOT NULL) THEN expression2

  ...

  ELSE expressionN

END  

Notice that that for example expression1 is processed twice, which should in theory take twice as long as ISNULL for non-trivial expressions.

Results are shown below (code is not perfect but it is only to prove the concept):

CREATE FUNCTION [dbo].[One_second_delay]

(

       @para1 int

)

RETURNS int

AS

BEGIN

DECLARE @StartDate DATETIME

SET @StartDate = GETDATE()

DECLARE @i int

-- Start the loop to ensure that there is a 1 second wait

WHILE CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))<1000

BEGIN

SET @i=2

END

       RETURN 1

END

GO

-------

DECLARE @CPU INT

SET @CPU = @@CPU_BUSY

DECLARE @StartDate DATETIME

SET @StartDate = GETDATE()

BEGIN IF coalesce(

(select [dbo].[One_second_delay](1) FROM (select 1 as col1) as tab1)

, 1) = 1

PRINT 'Done!'

END

PRINT 'Coalesce'

PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)

PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))

PRINT ''

GO

-----

DECLARE @CPU INT

SET @CPU = @@CPU_BUSY

DECLARE @StartDate DATETIME

SET @StartDate = GETDATE()

BEGIN IF ISNULL(

(select [dbo].[One_second_delay](1) FROM (select 1 as col1) as tab1)

, 1) = 1

PRINT 'Done!'

END

PRINT 'ISNULL'

PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)

PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))

PRINT ''

GO

As a result ISNULL runs twice as fast as COALESCE (on my SQL SERVER 2008R2 Express).

June 22, 2013 9:47 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

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