在SQL Server中与OR子句左连接

Question

我需要获取用户数。当我在WHERE子句中添加OR条件时，执行查询需要更多时间。如何优化此SQL查询以最小化执行时间？

SELECT SM.ModelId, 
    SM.NameID, 
    U.UserCount INTO #SM 
FROM Mapper AS SM (NOLOCK) CROSS APPLY (
SELECT COUNT(DISTINCT U.UserNumber) AS UserCount 
FROM Team T (NOLOCK) 
JOIN Equ E (NOLOCK) ON T.Equid = E.Equid 
JOIN EquReq ER (NOLOCK) ON E.EquReqid = ER.EquReqid 
LEFT JOIN Information I (NOLOCK) ON T.Equid = I.Equid 
JOIN USER U (NOLOCK) ON ER.Key = U.Key 
WHERE (T.NameID = SM.NameID 
    OR I.NameID = SM.NameID) 

Answer 1

你可以尝试运行下面的 -

SELECT SM.ModelId, SM.NameID, U.UserCount 
    INTO #SM  
    FROM Mapper AS SM (NOLOCK) 
CROSS APPLY (SELECT COUNT(DISTINCT U.UserNumber) AS UserCount 
       FROM Team T (NOLOCK) 
       JOIN Equ E (NOLOCK) 
        ON T.Equid = E.Equid 
       JOIN EquReq ER (NOLOCK) 
        ON E.EquReqid = ER.EquReqid 
       LEFT JOIN Information I (NOLOCK) 
        ON T.Equid = I.Equid 
       JOIN User U (NOLOCK) 
        ON ER.Key = U.Key 
       WHERE T.NameID = SM.NameID 
       UNION 
       SELECT COUNT(DISTINCT U.UserNumber) AS UserCount 
       FROM Team T (NOLOCK) 
       JOIN Equ E (NOLOCK) 
        ON T.Equid = E.Equid 
       JOIN EquReq ER (NOLOCK) 
        ON E.EquReqid = ER.EquReqid 
       LEFT JOIN Information I (NOLOCK) 
        ON T.Equid = I.Equid 
       JOIN User U (NOLOCK) 
        ON ER.Key = U.Key 
       WHERE I.NameID = SM.NameID) 

如果没有帮助，请确保您有足够的两个覆盖索引T.NameID & I.NameID以满足查询。

如果您仍然有一个问题，请让我知道你对下面的表什么索引 -

T队 信息我