SQL COALESCE() Very Cool, But Slower Than ISNULL()
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:
<!--- 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.
Want to use code from this post? Check out the license.
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?
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. :-)
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.
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
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.
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.
Actually, it can be done by ISNULL as well.
DECLARE @CodeList varchar(1000)
SELECT @CodeList = ISNULL(@CodeList +', ', '') +
CAST(EmpCode AS varchar(20))
FROM Employees
SELECT @CodeList
COALESCE has 2 major advantages:
1) it is part of the ANSI 2000 standard which can
make your sql more portable. This, however, is pointless to most people, as they usually work in exactly one environment.
2) you are not limited to two values.
ISNULL, logically speaking, should perform better in most cases, although there are some occasions where that does not seem to be the case.
@Jeremy,
I switched to MySQL a few years ago and have since starting using COALESCE() with success. MySQL does have IsNull() functions, but they don't act the same way - they are simply null-value-checkers.
@Ben: Right, MySQL's ISNULL() behaves diffently to MSSQL. But shouldn't ISNULL() return whether a value is NULL or not?
For the desired result MySQL provides IFNULL(), which behaves like ISNULL() in MSSQL and has the 'correct' function naming.
Hi,
None of code of David or Wayos S. working in Sybase ASE. It always return last value from the list. Any ideas?