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?