[SQL] SQL Server Left Join With 'Or' Operator

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?

Continue reading...