Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

ISNULL vs COALESCE speed test

well there was once a debate on SqlTeam in which i claimed that IsNull is slower than Coalesce when used in same fashion:

IsNull (col1, 'somevalue') vs Coalesce(col1, 'somevalue')

so i did a little testing. first i inserted 500.000 rows into a table with 5 columns:

create table TestTable (col1 varchar(50), col2 varchar(50), col3 varchar(50), col4 varchar(50), col5 varchar(50))

in which for each 100k rows one column was filled with 50 chars and the rest were null.

then i ran both of the statements (one for coalesce and one for isnull) 10 times and took 10 elapsed time measures. Out of those 10 elapsed times i didn't use the 2 that were most deviated from the rest:. Then i put the 2 select statements into a stored procedure and did the same thing. I didn't take into account the first run of each sproc because of the execution plan calculation. What surprised me is that the stored procedure versions were slower by 4 seconds in average. weird… if any one can explain that i'd be glad to know.

I would really like to know how IsNull works internally, because COALESCE works like this:

COALESCE(expression1,…n) is equivalent to this CASE function:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   …
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL

that is directly from BOL. So my guess is that IsNull has something slower inside which is surprising acctually.

I ran all this on a box with Microsoft SQL Server  2000 - 8.00.760 (Intel X86), Windows Server 2003, 512 Mb of RAM and 864 MHz CPU

RESULTS:

  coalesce isnull
No Sproc 23258 ms 27613 ms
Sproc 27255 ms 31962 ms

 

The whole script:

use northwind
if object_id('tempdb..#temp') is not null
 drop table #temp

 

declare @var varchar(50)
set @var = 'fberjhreaugheagh954wz645whla2309563498743wzherusbr'
select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5
into #temp
from
(
select @var col1, null col2 , null col3, null col4, null col5 union all
select null col1, @var col2 , null col3, null col4, null col5 union all
select null col1, null col2 , @var col3, null col4, null col5 union all
select null col1, null col2 , null col3, @var col4, null col5 union all
select null col1, null col2 , null col3, null col4, @var col5
) t1
cross join
(
select @var col1, null col2 , null col3, null col4, null col5 union all
select null col1, @var col2 , null col3, null col4, null col5 union all
select null col1, null col2 , @var col3, null col4, null col5 union all
select null col1, null col2 , null col3, @var col4, null col5 union all
select null col1, null col2 , null col3, null col4, @var col5
) t2
cross join
(
select @var col1, null col2 , null col3, null col4, null col5 union all
select null col1, @var col2 , null col3, null col4, null col5 union all
select null col1, null col2 , @var col3, null col4, null col5 union all
select null col1, null col2 , null col3, @var col4, null col5 union all
select null col1, null col2 , null col3, null col4, @var col5
) t3
cross join
(
select @var col1, null col2 , null col3, null col4, null col5 union all
select null col1, @var col2 , null col3, null col4, null col5 union all
select null col1, null col2 , @var col3, null col4, null col5 union all
select null col1, null col2 , null col3, @var col4, null col5 union all
select null col1, null col2 , null col3, null col4, @var col5
) t4
cross join
(
select @var col1, null col2 , null col3, null col4, null col5 union all
select null col1, @var col2 , null col3, null col4, null col5 union all
select null col1, null col2 , @var col3, null col4, null col5 union all
select null col1, null col2 , null col3, @var col4, null col5 union all
select null col1, null col2 , null col3, null col4, @var col5
) t5

if object_id('TestTable') is not null
 drop table TestTable
create table TestTable (col1 varchar(50), col2 varchar(50), col3 varchar(50), col4 varchar(50), col5 varchar(50))

go
set ROWCOUNT 100000
go
insert into TestTable
select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col1 is not null
go
insert into TestTable
select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col2 is not null
go
insert into TestTable
select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col3 is not null
go
insert into TestTable
select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col4 is not null
go
insert into TestTable
select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col5 is not null
go
set ROWCOUNT 0
go

create procedure spTestTableCoalesce
as
declare @testValue varchar(50)
set @testValue = 'this is a 50 char sentence.i have no other idea :)'
select  coalesce(col1, @testValue) as col1,
 coalesce(col2, @testValue) as col2,
 coalesce(col3, @testValue) as col3,
 coalesce(col4, @testValue) as col4,
 coalesce(col5, @testValue) as col5
from  TestTable
go

create procedure spTestTableIsNull
as
declare @testValue varchar(50)
set @testValue = 'this is a 50 char sentence.i have no other idea :)'
select  isnull(col1, @testValue) as col1,
 isnull(col2, @testValue) as col2,
 isnull(col3, @testValue) as col3,
 isnull(col4, @testValue) as col4,
 isnull(col5, @testValue) as col5
from  TestTable
go

– this is so my disk space doesn't go to zero…
DBCC SHRINKDATABASE ('Northwind', 10)
go

select count(*) as RecordCount from TestTable
go

set statistics time on

–ran 10 times
declare @testValue varchar(50)
set @testValue = 'this is a 50 char sentence.i have no other idea :)'
select  coalesce(col1, @testValue) as col1,
 coalesce(col2, @testValue) as col2,
 coalesce(col3, @testValue) as col3,
 coalesce(col4, @testValue) as col4,
 coalesce(col5, @testValue) as col5
from  TestTable
go
–ran 10 times
declare @testValue varchar(50)
set @testValue = 'this is a 50 char sentence.i have no other idea :)'
select  isnull(col1, @testValue) as col1,
 isnull(col2, @testValue) as col2,
 isnull(col3, @testValue) as col3,
 isnull(col4, @testValue) as col4,
 isnull(col5, @testValue) as col5
from  TestTable

–ran 10 times
exec spTestTableCoalesce
go
–ran 10 times
exec spTestTableIsNull
go

drop table #temp
drop table TestTable
drop procedure spTestTableIsNull
drop procedure spTestTableCoalesce

 

Legacy Comments


Anatoly Lubarsky
2004-11-24
re: ISNULL vs COALESCE speed test
actually isnull is faster.
your test shows that sp is slower than adhoc. It is not possible in the proper test.
See my tests:

http://blogs.x2line.com/al/archive/2004/03/01/189.aspx

Tara
2004-12-02
re: ISNULL vs COALESCE speed test
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx

Roji
2004-12-02
re: ISNULL vs COALESCE speed test
http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html

Adam Machanic
2004-12-02
re: ISNULL vs COALESCE speed test
Okay, per your request I tried your sample.

On my test system, using the 'no sproc' example, ISNULL ran consistently faster by about a second (20 vs. 19), and used around 30% less processor time according to STATISTICS TIME.

Results and total runtimes were the same with the stored procedures. Strangely, however, STATISTICS TIME reported lower processor time utilization with the sprocs, and in addition there was less of a gap -- around 11%. I'm not sure why that is, but I'm assuming it's a bug with STATISTICS TIME rather than an indication of performance, considering that the runtimes were identical.

SOWJANYA KOTRA
2009-08-18
re: ISNULL vs COALESCE speed test
I run a query with both coalesce an ISNULL function, Where ISNULL is taking less time compared to Coalesce.

SOWJANYA KOTRA

Henrik Carlsen
2010-09-21
re: ISNULL vs COALESCE speed test
I created a table with 5 varchar fields and a PK. Filled it with 2000000 records with 30% null in all.

INSERT INTO Dbo.Stamp DEFAULT VALUES
SELECT COUNT(ISNULL([data1],''))
,COUNT(ISNULL([data2],''))
,COUNT(ISNULL([data3],''))
,COUNT(ISNULL([data4],''))
,COUNT(ISNULL([data5],''))
FROM [dbo].[Data]
INSERT INTO Dbo.Stamp DEFAULT VALUES
SELECT COUNT(COALESCE([data1],''))
,COUNT(COALESCE([data2],''))
,COUNT(COALESCE([data3],''))
,COUNT(COALESCE([data4],''))
,COUNT(COALESCE([data5],''))
FROM [dbo].[Data]
INSERT INTO Dbo.Stamp DEFAULT VALUES

I swapped the queries and redid the tests, no change in result. The IsNull solution averaged 173ms whereas the Coalesce user 1399ms, a factor 8 in this example.

I haven't performed the same test in a procedure.

Chris Heit
2010-09-22
re: ISNULL vs COALESCE speed test
I was helping a coworker try to keep a query from timing out over a very large dataset. After some tweaking the query would return in 15-16 seconds, versus the original minute, but we were stuck there, regardless of anything we tried. Finally, I had him switch out all the COALESCE statements with ISULL. It executed in 3 seconds.

I don't know why, but it helped.

Is it possible that it is because COALESCE, which is basically "Return the first non-null item from this list", has to evaluate each parameter to see if it is null before it returns it, while ISNULL which is basically, "If X is null, give me Y", just simply returns the second parameter if the first is null?