SQL COALESCE() Very Cool, But Slower Than ISNULL()
Posted August 4, 2006 at 5:25 PM
As I just blogged, COALESCE() was just pointed out to me. Its a cool function, but I was curious to see how it compared in speed to ISNULL(). It seems that COALESCE() is about 2 to 3 times slower on enormous queries:
Launch code in new window » Download code as text file »
- <!--- Test the COALESCE() SQL method. --->
- <cftimer label="COALESCE" type="outline">
-
- <cfquery name="qCoalesce" datasource="...">
- SELECT
- id,
- COALESCE( date_created, getDate() ) AS date_created
- FROM
- web_stats_hit
- WHERE
- COALESCE( date_created, getDate() ) IS NOT NULL
- AND
- COALESCE( id, 0 ) IS NOT NULL
- </cfquery>
-
- </cftimer>
-
- <!--- Test the ISNULL() SQL method. --->
- <cftimer label="ISNULL" type="outline">
-
- <cfquery name="qIsNull" datasource="...">
- SELECT
- id,
- ISNULL( date_created, getDate() ) AS date_created
- FROM
- web_stats_hit
- WHERE
- ISNULL( date_created, getDate() ) IS NOT NULL
- AND
- ISNULL( id, 0 ) IS NOT NULL
- </cfquery>
-
- </cftimer>
The ISNULL() method performed on average at about 550 ms. The COALESCE() method performed on average at about 1500 ms. The other interesting thing was that ISNULL() was very very consistent. COALESCE() on the other hand, would range from 1000 ms to 2000 ms from test to test.
I am not doing this to point out that one is better than the other; they do different things. I am just saying, consider one or the other based on the situation.
Download Code Snippet ZIP File
Comments (6) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
So, what do you recommend? I am use isNull() a lot, especially when working with date. But not on enourmous queries. Do you think I should switch to coalesce?
Posted by Kit Claudio on Aug 7, 2006 at 8:10 AM
COALESCE is actually very useful in certain situations. For example, if you had a table with two quantity columns and you wanted to sum the difference between them, you would do,
select sum(coalesce(quanitty1, 0) - coalesce(quantity2, 0)) as difference
If you didn't use coalesce, then the sum will result in a NULL if any quantity1 or quantity2 field is NULL.
I know this comment is a little late, but I found this blog doing a google search and thought I might add to the discussion. :-)
Posted by Leon on Sep 29, 2006 at 2:37 PM
Leon,
I appreciate what you are saying, however I think the same thing could be done with ISNULL as well:
select sum(ISNULL(quanitty1, 0) - ISNULL(quantity2, 0)) as difference
I am not argueing one way or the other. From what people have said, COALESCE is more of a standard across database management systems.
Posted by Ben Nadel on Sep 29, 2006 at 4:08 PM
Coalesce is much more useful for something like this
DECLARE @CodeList varchar(1000)
SELECT @CodeList = COALESCE(@CodeList +', ', '') +
CAST(EmpCode AS varchar(20))
FROM Employees
SELECT @CodeList
Where EmpCode is a column in the Employees table, this will return a comma-delimited list of the employees' codes
Posted by David on Dec 19, 2006 at 5:10 PM
Dave,
Nice tip. I have never really worked on building a string using a SELECT statement, but I have seen it done and it's probably something I should try.
Posted by Ben Nadel on Dec 20, 2006 at 7:30 AM
I would say isnull() is 'as' standard as coalesce(). Oracle and Sybase use it just like MS..
They are the same 'unless' you're testing multiple values. That's the ONLY reason you should use coalesce() ...unless of course its for code consistency.
Posted by Rob Seliga on Oct 15, 2007 at 10:06 AM
