SQL Server Left Join With 'Or' Operator

Question

I have a four tables, TopLevelParent, two mid level tables MidParentA and MidParentB, and a Child table which can have a parent of MidParentA or MidParentB (One or the other midParent must be in place). Both mid level tables have a parent table of TopLevelParent.

The Top Level table look like this:

TopLevelId | Name
--------------------------
1          | name1   
2          | name2   

The MidParent tables look like this:

MidParentAId | TopLevelParentId |           MidParentBId | TopLevelParentId |
------------------------------------       ------------------------------------
1            |        1         |           1            |        1         |
2            |        1         |           2            |        1         |

The Child table look like this:

ChildId | MidParentAId | MidParentBId
--------------------------------
1       |     1        |   NULL
2       |    NULL      |     2

I have used the following left join in a larger stored procedure which is timing out, and it looks like the OR operator on the last left join is the culprit:

SELECT *    
FROM TopLevelParent tlp
LEFT JOIN MidParentA a ON tlp.TopLevelPatientId = a.TopLevelPatientId
LEFT JOIN MidParentB a ON tlp.TopLevelPatientId = b.TopLevelPatientId
LEFT JOIN Child c ON c.ParentAId = a.ParentAId OR c.ParentBId = b.ParentBId

Is there a more performant way to do this join?

Answer 1

Given how little of the query is being exposed; a very rough rule of thumb is to replace an Or with a Union to avoid table scanning.

Select..
LEFT JOIN Child c ON c.ParentAId = a.ParentAId 
union
Select..
left Join Child c ON c.ParentBId = b.ParentBId