SQL Server使用“或"运算符左联接 [英] SQL Server Left Join With 'Or' Operator

问题描述

我有四个表，TopLevelParent，两个中级表MidParentA和MidParentB，以及一个子表，该表可以具有MidParentA或MidParentB的父级(一个或另一个midParent必须存在).两个中级表都有一个TopLevelParent父表.

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.

顶层"表如下所示:

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

MidParent表如下:

The MidParent tables look like this:

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

Child表如下所示:

The Child table look like this:

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

我在较大的存储过程中使用了以下左连接，该存储过程正在超时，看起来最后一个左连接上的OR运算符是元凶:

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?

推荐答案

最后，我要做的是将执行时间从52秒减少到4秒.

Here is what I did in the end, which got the execution time down from 52 secs to 4 secs.

SELECT * 
FROM (
    SELECT tpl.*, a.MidParentAId as 'MidParentId', 1 as 'IsMidParentA' 
    FROM TopLevelParent tpl 
    INNER JOIN MidParentA  a ON a.TopLevelParentId = tpl.TopLevelParentID
UNION
    SELECT tpl.*, b.MidParentBId as 'MidParentId', 0 as 'IsMidParentA'  
    FROM TopLevelParent tpl 
    INNER JOIN MidParentB b ON b.TopLevelParentId = tpl.TopLevelParentID
UNION
    SELECT tpl.*, 0 as 'MidParentId', 0 as 'IsMidParentA'  
    FROM TopLevelParent tpl 
    WHERE tpl.TopLevelParentID NOT IN (
       SELECT pa.TopLevelParentID 
       FROM TopLevelParent tpl
       INNER JOIN MidParentA  a ON a.TopLevelParentId = tpl.TopLevelParentID
    UNION
       SELECT pa.TopLevelParentID 
       FROM TopLevelParent tpl
       INNER JOIN MidParentB b ON h.TopLevelParentId = tpl.TopLevelParentID
    )
) tpl
LEFT JOIN MidParentA a ON a.TopLevelParentId = tpl.TopLevelParentID
LEFT JOIN MidParentB b ON b.TopLevelParentId = tpl.TopLevelParentID
LEFT JOIN 
(
        SELECT  [ChildId]
                ,[MidParentAId] as 'MidParentId'
                ,1 as 'IsMidParentA'
        FROM Child c
        WHERE c.MidParentAId IS NOT NULL
   UNION
        SELECT [ChildId]
               ,[MidParentBId] as 'MidParentId'
               ,0 as 'IsMidParentA'
        FROM Child c
        WHERE c.MidParentBId IS NOT NULL
) AS c
ON c.MidParentId = tpl.MidParentId  AND c.IsMidParentA = tpl.IsMidParentA

这消除了正在发生的表扫描，因为我已经将顶级记录与它的中级父级(如果存在)进行了匹配，并将其标记在该记录上.

This eliminates the table scanning that was happening, as I have matched the top level record to its midlevel parent up front if it exists, and stamped it on that record.

我对子记录也做了同样的事情，这意味着我可以将子记录加入MidParentId的顶级记录中，并使用IsMidParentA位标志来区分存在两个相同MidParentId的位置(即一个ID IsMidParentA和IsMidParentB为1).

I have also done the same with the child record meaning I can then just join the child record to the top level record on the MidParentId, and I use the IsMidParentA bit flag to differentiate where there are two identical MidParentIds (ie an Id of 1 for IsMidParentA and IsMidParentB).

感谢所有花时间回答的人.

Thanks to all who took the time to answer.

这篇关于SQL Server使用“或"运算符左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！