SQL query with lots of JOIN conditions is very slow

Question

I've inherited a SQL Server 2008 R2 project that, amongst other things, does a table update from another table:

• Table1 (with around 150,000 rows) has 3 phone number fields (Tel1,Tel2,Tel3)
• Table2 (with around 20,000 rows) has 3 phone number fields (Phone1,Phone2,Phone3)

.. and when any of those numbers match, Table1 should be updated.

The current code looks like:

UPDATE t1
SET surname = t2.surname, Address1=t2.Address1, DOB=t2.DOB, Tel1=t2.Phone1, Tel2=t2.Phone2, Tel3=t2.Phone3,
FROM Table1 t1 
inner join Table2 t2
on
(t1.Tel1 = t2.Phone1 and t1.Tel1 is not null) or
(t1.Tel1 = t2.Phone2 and t1.Tel1 is not null) or
(t1.Tel1 = t2.Phone3 and t1.Tel1 is not null) or
(t1.Tel2 = t2.Phone1 and t1.Tel2 is not null) or
(t1.Tel2 = t2.Phone2 and t1.Tel2 is not null) or
(t1.Tel2 = t2.Phone3 and t1.Tel2 is not null) or
(t1.Tel3 = t2.Phone1 and t1.Tel3 is not null) or
(t1.Tel3 = t2.Phone2 and t1.Tel3 is not null) or
(t1.Tel3 = t2.Phone3 and t1.Tel3 is not null);

However, this query is taking over 30 minutes to run.

The execution plan suggests that the main bottleneck is a Nested Loop around the Clustered Index Scan on Table1. Both tables have clustered indexes on their ID column.

As my DBA skills are very limited, can anyone suggests the best way to improve the performance of this query? Would adding an index for Tel1,Tel2 and Tel3 to each column be the best move, or can the query be changed to improve performance?

Answer 1

On first look, I would recommend eliminating all your OR Conditions from the select.

See if this is faster (it's converting your update into 3 distinct updates):

UPDATE t1
SET surname = t2.surname, Address1=t2.Address1, DOB=t2.DOB, Tel1=t2.Phone1, Tel2=t2.Phone2, Tel3=t2.Phone3,
FROM Table1 t1 
inner join Table2 t2
on
(t1.Tel1 is not null AND t1.Tel1 IN (t2.Phone1, t2.Phone2, t2.Phone3);

UPDATE t1
SET surname = t2.surname, Address1=t2.Address1, DOB=t2.DOB, Tel1=t2.Phone1, Tel2=t2.Phone2, Tel3=t2.Phone3,
FROM Table1 t1 
inner join Table2 t2
on
(t1.Tel2 is not null AND t1.Tel2 IN (t2.Phone1, t2.Phone2, t2.Phone3);

UPDATE t1
SET surname = t2.surname, Address1=t2.Address1, DOB=t2.DOB, Tel1=t2.Phone1, Tel2=t2.Phone2, Tel3=t2.Phone3,
FROM Table1 t1 
inner join Table2 t2
on
(t1.Tel3 is not null AND t1.Tel3 IN (t2.Phone1, t2.Phone2, t2.Phone3);