Geek Answers Handbook

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

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

Here is one heavy request:

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 comparison columns (except identifiers) varchar(~ 30 ... 200)
T1 is ~ 130 million rows, T2 is ~ 300 thousand rows.

These requests work on a rather large Dev server ~ 5 hours - this is slow, but what can we do?

And although we investigated possible optimization methods - we found that changing “isnull” to “coalescing in the query above gives a double performance gain - and the query now runs for ~ 2 hours

UPD . When we remove all tags ISNULLand use only t1.vchCol1 = t2.vchCol1, the request ends after 40 minutes .

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

+5
source share
4 answers

I wonder if you will see an improvement by dividing the boxes 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))
...
+9
source

Most of the articles you find on this subject seem to contradict this. ISNULLless (less) than COALESCE.

Differences between ISNULLandCOALESCE

COALESCE CASE ISNULL .
...
COALESCE .

ISNULL vs. COALESCE

ISNULL COALESCE 10 12 . 6 5,3 ( ), . , , .

COALESCE vs ISNULL vs IS NULL OR

IS NULL OR case, 3 .

+3

, , . , , . - :

Alter Table T1 Add CheckSumId As CHECKSUM(vchCol1, vchCol2, vchCol3)
Alter Table T2 Add CheckSumId As CHECKSUM(vchCol1, vchCol2, vchCol3)

Create NonClustered index idx_T1_Checksum On T1(id, CheckSumId)
Create NonClustered index idx_T2_Checksum On T2(ext_id, CheckSumId)

...

select t1.id, t2.id 
from t1 Inner Join t2
       On t1.id = t2.ext_id
       And T1.CheckSumId = T2.CheckSumId
where  isnull(t1.vchCol1, 'Null') = isnull(t2.vchCol1, 'Null')
     and isnull(t1.vchCol2, 'Null') = isnull(t2.vchCol2, 'Null')

, , , 2 , . , , . , .

+2

, , ...

For this kind of column matching, you can use EXCEPT. In addition, EXCEPT treats NULL as another value instead of “It could be anything!”, As I like to call it.

"When you compare strings to determine individual values, two NULL values ​​are considered equal." - from http://msdn.microsoft.com/en-us/library/ms188055.aspx

+1
source

More articles:


All Articles