SQL Server Performance Forum – Threads Archive
columns w/ lots of nulls
selectsum(case when a.col1 <> b.col2 then 1
when a.col1 is null and b.col2 is not null then 1
when a.col1 is not null and b.col2 is null then 1
else 0)
from a, b
where a.col10 = b.col10 Now 95% of the rows in both col1 and col2 are null, and so there
are no indexes on a.col1 or b.col2. The performance of this query is very
slow(10+ hours). How can I improve its performance. Thank you so much. Chris
try having a non-clustered index on table a (col10, col1) and table b(col10, col2) Cheers
Twan
also try
select sum( ua.col_tot )
from ( — first two cases
select sum( case when a.col1 <> b.col2 then 1
when a.col1 is not null and b.col2 is null then 1
else 0 end) as col_tot
from a, b
where a.col1 is not null
and a.col10 = b.col10
union all
— need to also get the third case
select count( b.col2 ) as col_tot
from a, b
where b.col2 is not null
and a.col1 is null
and a.col10 = b.col10
) ua with indexes a( col1, col10 )
b( col2, col10 )
a( col10, col1 )
b( col10, col2 ) Cheers
Twan
]]>