April 22, 2008 at 12:29 pm
This has probably been discussed to death in this forum already, but I have the following question.
I'm aware of the differences between ISNULL and COALESCE.
I have read this article:
Four Rules for NULLs
http://www.sqlservercentral.com/articles/Advanced+Querying/fourrulesfornulls/1915/
Here is my question:
I have a sproc that contains 265 occurencies of COALESCE (and none of ISNULL). No matter how miniscule the performance difference between ISNULL and COALESCE (ISNULL is supposed to be slightly faster), wouldn't it make sense replacing all COALESCE occurencies with ISNULL?
COALESCE is being used in this case in its simplest form:
COALESCE (colName, '')
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 22, 2008 at 12:35 pm
My rulle of thumb is regardless of what "everyone says" is best to run both ways and compare the results. Then you'll know for sure. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 22, 2008 at 2:04 pm
perf difference is negligible, not important. pick one you like and use that.
I prefer coalesce as you can pass multiple args, plus it's ANSI. Also it's harder to spell.
furthermore, ISNULL is named stupidly. any function that starts with IS should return a BOOL.
---------------------------------------
elsasoft.org
April 22, 2008 at 2:18 pm
what's negligible? 1%?
even if COALESCE is 1% faster, having it occur hundreds/thousands of times in frequently run code, should still incur a small overhead, would it not?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 22, 2008 at 2:28 pm
Adam says ISNULL is about 10% faster:
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx
but 10% of what? The time your query spends evaluating ISNULL or COALESCE will generally be very tiny compared to the time it spends doing other things, like seeks, scans, joins, etc.
if you are really curious, write two procs, one using ISNULL only, and the other using COALESCE only. Run each one many times and compare. What I am saying is that there will be no noticable difference.
---------------------------------------
elsasoft.org
April 22, 2008 at 2:34 pm
prefer coalesce.
Not only because it is ansi, but also because it can do more for you.
isnull(thecol, thereplacement)
coalesce(thecol,thefirstreplacement, usethisifthefirstreplacementisNULL,...)
and I've had some issues with sql2000 when using isnull is certain scenarios :Whistling:
(usage of view on view using isnull and parallel plans)
... workaround .... rewrite code to coalesce.:doze:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 22, 2008 at 2:35 pm
jezemine (4/22/2008)
Adam says ISNULL is about 10% faster:http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx
but 10% of what? The time your query spends evaluating ISNULL or COALESCE will generally be very tiny compared to the time it spends doing other things, like seeks, scans, joins, etc.
if you are really curious, write two procs, one using ISNULL only, and the other using COALESCE only. Run each one many times and compare. What I am saying is that there will be no noticable difference.
It's probably also a question of query complexity, size of tables involved, indexing etc.
This blog reported much more dramatic results:
http://www.bennadel.com/blog/196-SQL-COALESCE-Very-Cool-But-Slower-Than-ISNULL-.htm
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 22, 2008 at 3:13 pm
I just ran a simple test:
create table #NullTest (
ID int identity primary key,
Date datetime)
insert into #nulltest (date)
select
case
when number%10 > 0 then dateadd(day, number, '1/1/2000')
else null
end
from common.dbo.bignumbers
set statistics io on
set statistics time on
declare @Date datetime
select @date = isnull(date, getdate())
from #nulltest
I used both coalesce and isnull in the final query. Coalesce took an average of 350 milliseconds, isnull took an average of 328 milliseconds.
More complex queries might make a bigger difference, but coalesce was consistently about 8-9% slower in this case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2008 at 5:29 pm
if a few msec on a query is important to you, then use ISNULL.
I guess what I am saying is, I never came across a system where the bottleneck was use of COALESCE instead of ISNULL. There are always bigger problems than this. Which one you choose to use is not relevant from a practical standpoint.
---------------------------------------
elsasoft.org
April 23, 2008 at 6:25 am
Until you're down to squeezing the last 10-20ms out of a query, I don't think it matters. COALESCE has a lot more flexibility than ISNULL, so, I'd say, if you need the added functionality and you can pay the extra milliseconds, using COALESCE just isn't a problem.
Still, it's all very interesting information.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2008 at 8:52 am
Grant Fritchey (4/23/2008)
Until you're down to squeezing the last 10-20ms out of a query, I don't think it matters. COALESCE has a lot more flexibility than ISNULL, so, I'd say, if you need the added functionality and you can pay the extra milliseconds, using COALESCE just isn't a problem.Still, it's all very interesting information.
Keep in mind, this is 20 ms difference on 1-million rows. On smaller queries, the difference is unmeasurable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2008 at 9:05 am
Thanks all for your replies.
This seems to be more of an academic question then.
Cheers!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply