Why IsNull is twice slow as coalesce (same query)?

Question

We met a strange situation on SQL Server 2008 (SP1) - 10.0.2531.0 (X64) - Win2008 SP2 (X64).

Here is a one heavy query:

select t1.id, t2.id 
from t1, t2
where 
     t1.id = t2.ext_id
     and isnull(t1.vchCol1, 'Null') = isnull(t2.vchCol1, 'Null')
     and isnull(t1.vchCol2, 'Null') = isnull(t2.vchCol2, 'Null')
     .... and about 10 more comparisons with Isnull

UPD: All columns in comparison (except IDs) are varchar(~30...200)
T1 is ~130mln rows, T2 is ~300k rows.

These query on rather big Dev server run ~5 hours - this is slow, but what we can do?

And while we investigated possible ways of optimisation - we found, that changing "isnull" to "coalesce" in query above gives double performance gain - and query now run for ~2 hours

UPD: When we remove all ISNULL checks and use just t1.vchCol1 = t2.vchCol1 the query finishes after 40mins.

Question is: Is this known behavior and we should avoid using IsNull everywhere?

Answer 1

I wonder if you'd see an improvement by splitting the cases out explicitly:

...
AND ((t1.vchCol1 = t2.vchCol1) OR (t1.vchCol1 IS NULL AND t2.vchCol1 IS NULL))
AND ((t1.vchCol2 = t2.vchCol2) OR (t1.vchCol2 IS NULL AND t2.vchCol2 IS NULL))
...

Answer 2

Most of the articles you'll find on this subject seem to contradict this. ISNULL is (marginally) faster than COALESCE.

Differences between ISNULL and COALESCE

COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine.
...
This will make a performance difference and queries with COALESCE often fare worse here.

ISNULL vs. COALESCE

I ran these tests several times on a few different servers, and ISNULL appears to pretty consistently out-perform COALESCE by an average of 10 or 12 percent. But that's the difference between 6 seconds and 5.3 seconds (the approximate average runtimes per test on my servers), over the course of a million exections. Hardly worth the functionality and standards compliance sacrifice, at least in the scenarios I use these functions for.

COALESCE vs ISNULL vs IS NULL OR

the best performer is IS NULL OR case, while the difference between all 3 of them is minor.