I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

 

Print | posted on Wednesday, November 24, 2004 9:29 PM |

Feedback

Gravatar

# 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
11/24/2004 11:17 PM | Anatoly Lubarsky
Gravatar

# ISNULL or COALESCE

11/29/2004 3:45 AM | Vinod Kumar's Blog
Gravatar

# re: ISNULL vs COALESCE speed test

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx
12/2/2004 1:06 AM | Tara
Gravatar

# re: ISNULL vs COALESCE speed test

http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html
12/2/2004 4:08 PM | Roji
Gravatar

# 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.
12/2/2004 11:02 PM | Adam Machanic
Gravatar

# T-SQL: Coalesce vs. IsNull

4/22/2005 3:11 PM | Little Tidbits of Random Knowled
Gravatar

# T-SQL: Coalesce vs. IsNull

4/22/2005 3:11 PM | Little Tidbits of Random Knowled
Gravatar

# Performance: ISNULL vs. COALESCE

Originally posted here.
Mladen aka spirit1 posted a speed test of COALESCE vs. ISNULL. Reported result:...
7/13/2006 12:17 AM | Adam Machanic
Gravatar

# 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
8/18/2009 7:57 AM | SOWJANYA KOTRA
Gravatar

# 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.
9/21/2010 12:25 PM | Henrik Carlsen
Gravatar

# 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?
9/22/2010 4:05 PM | Chris Heit
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET