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:
[sql]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
[/sql]
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.
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
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.)
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 🙂
Scarifying = sacrificing, hehe
The fourth test is invalid in this script !
Because you use COALESCE instead of ISNULL at line 48
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
———–
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)
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.
"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.
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.
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.
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().
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.
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
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.
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.
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
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
Adam:
I see your point.
Very happy to receive a feedback from you.
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).
I think it makes sense to test this even further.
Since COALESCE can handle more than two and here and there we add two isnull statements, this should be compared. We may find a more significant difference.
ISNULL(ISNULL(A,B),C)
vs.
COALESCE(A,B,C)
So here are the results on an SQL 2008 r2 machine. (with the script correction)
Note that I reversed the order the queries were executed in.
This made a difference. Whichever query ran last, ran faster.
COALESCE, first two column null
Total CPU time: 71
Total milliseconds: 22043
ISNULL, first two column null
Total CPU time: 71
Total milliseconds: 19763
COALESCE, left and right column null
Total CPU time: 71
Total milliseconds: 21376
ISNULL, left and right column null
Total CPU time: 70
Total milliseconds: 20470
COALESCE, middle and right column null
Total CPU time: 67
Total milliseconds: 23700
ISNULL, middle and right column null
Total CPU time: 63
Total milliseconds: 21010
@Peter
So ISNULL still wins! I guess it’ll probably cross over at 3 arguments? Let us know if you test it.
–Adam