如何提高多个内连接的查询性能？

I have a query which is taking 1 minute to execute. After spending some time on the query I found there is some part that is actually causing the query to take the time. Please see my comments below for the query mentioned.

我有一个查询，需要1分钟才能执行。在查询上花了一些时间之后，我发现有一些部分实际上导致查询花费时间。有关上述查询，请参阅下面的评论。

Complete query:

完整查询：

SELECT DISTINCT 
    CSU.*, U.txtFirstName, U.txtLastName 
FROM
    tblCRMShallowUsers CSU (NOLOCK) 
INNER JOIN 
    tblUsers U (NOLOCK) ON CSU.PK_autUserID = U.PK_autUserID 
INNER JOIN 
    tblUserGroupLink UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID 
INNER JOIN 
    tblModuleSecurityLinks MSL (NOLOCK) ON FK_lngModuleID = 28 
INNER JOIN 
    tblGroups G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID 
WHERE 
    MSL.lngRights > 0
    AND U.lngStatus > 19
    AND U.ysnAdminFlag = 0
    AND G.lngStatus > 19
    AND G.ysnFrontEndGroup = 0
    AND (UGL.FK_lngGroupID = MSL.FK_lngGroupID
         OR UGL.FK_lngGroupID = 2) 
ORDER BY 
    ysnHasAccess DESC, txtLastName, txtFirstName

Below joins are performing quickly in the above query:

以下连接在上面的查询中快速执行：

INNER JOIN 
    tblUsers U (NOLOCK) ON CSU.PK_autUserID = U.PK_autUserID 
INNER JOIN 
    tblUserGroupLink UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID 
INNER JOIN 
    tblModuleSecurityLinks MSL (NOLOCK) ON FK_lngModuleID = 28 
INNER JOIN 
    tblGroups G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID 

Here's the AND part in the above query that is really slowing it down. When I remove this join it was working very fast but the result set is not the result that was coming earlier.(it returns more data)

这是上面查询中的AND部分，它真的减慢了它的速度。当我删除这个连接时，它工作得非常快，但结果集不是早先的结果。（它返回更多数据）

AND (UGL.FK_lngGroupID = MSL.FK_lngGroupID
     OR UGL.FK_lngGroupID = 2)

I would really appreciate if you can show some direction to optimize the query or some example or other way of writing the same query.

如果您能够显示某些方向来优化查询或某些示例或其他编写相同查询的方式，我将非常感激。

2 个解决方案

#1

Because the optimal execution plan with each of the OR predicates differs, performance is improved by refactoring the single query as separate SELECT queries and a UNION operator. This allows the optimizer to choose the best plan for each query independently of the other. DISTINCT is not needed since UNION removes duplicate rows from the result.

由于每个OR谓词的最佳执行计划不同，因此通过将单个查询重构为单独的SELECT查询和UNION运算符来提高性能。这允许优化器为每个查询选择独立于另一个的最佳计划。由于UNION从结果中删除了重复的行，因此不需要DISTINCT。

SELECT
    CSU.*, U.txtFirstName, U.txtLastName 
FROM
    tblCRMShallowUsers CSU (NOLOCK) 
INNER JOIN 
    tblUsers U (NOLOCK) ON CSU.PK_autUserID = U.PK_autUserID 
INNER JOIN 
    tblUserGroupLink UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID 
INNER JOIN 
    tblModuleSecurityLinks MSL (NOLOCK) ON FK_lngModuleID = 28 
INNER JOIN 
    tblGroups G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID 
WHERE 
    MSL.lngRights > 0
    AND U.lngStatus > 19
    AND U.ysnAdminFlag = 0
    AND G.lngStatus > 19
    AND G.ysnFrontEndGroup = 0
    AND UGL.FK_lngGroupID = MSL.FK_lngGroupID
UNION
SELECT
    CSU.*, U.txtFirstName, U.txtLastName 
FROM
    tblCRMShallowUsers CSU (NOLOCK) 
INNER JOIN 
    tblUsers U (NOLOCK) ON CSU.PK_autUserID = U.PK_autUserID 
INNER JOIN 
    tblUserGroupLink UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID 
INNER JOIN 
    tblModuleSecurityLinks MSL (NOLOCK) ON FK_lngModuleID = 28 
INNER JOIN 
    tblGroups G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID 
WHERE 
    MSL.lngRights > 0
    AND U.lngStatus > 19
    AND U.ysnAdminFlag = 0
    AND G.lngStatus > 19
    AND G.ysnFrontEndGroup = 0
    AND UGL.FK_lngGroupID = 2 
ORDER BY 
    ysnHasAccess DESC, txtLastName, txtFirstName;

On a side note, be aware than NOLOCK and the READ_UNCOMMITTED isolation level may cause rows to be skipped or duplicated during allocation order scans if data are updated while the query is running. Dirty reads should be used only when concurrency is more important than correct results.

另外，请注意，如果在查询运行时更新数据，则在NOLOCK和READ_UNCOMMITTED隔离级别可能会导致在分配顺序扫描期间跳过或重复行。只有在并发比正确结果更重要时才应使用脏读。

#2

@DanGuzman @flaschenpost I was also trying with CTE approach to divide the joins into 2 seaprate things but it wasn't working and taking the same time as it was(1 minute). Could you please take a look at below query that I tried.

@DanGuzman @flaschenpost我也尝试用CTE方法将连接分成两个seaprate的东西，但它没有工作，并采取相同的时间（1分钟）。你能不能看看我试过的下面的查询。

1. With CTE approach: With UsersUserGroupLink AS( Select U.PK_autUserID, U.txtFirstName, U.txtLastName, U.lngStatus, U.ysnAdminFlag, UGL.FK_lngGroupID FROM tblUsers as U (NOLOCK) INNER JOIN tblUserGroupLink as UGL (NOLOCK) ON U.PK_autUserID = UGL.FK_lngUsersID ), GroupsModuleSecurityLinks AS(Select MSL.lngRights, G.lngStatus, G.ysnFrontEndGroup, MSL.FK_lngGroupID From
tblModuleSecurityLinks as MSL (NOLOCK) INNER JOIN tblGroups as G (NOLOCK) ON G.PK_autGroupID = MSL.FK_lngGroupID AND MSL.FK_lngModuleID = 28 ) (SELECT DISTINCT CSU.*, UsersUserGroupLink.txtFirstName, UsersUserGroupLink.txtLastName FROM UsersUserGroupLink Inner join
tblCRMShallowUsers as CSU ON UsersUserGroupLink.PK_autUserID =
CSU.PK_autUserID Inner join GroupsModuleSecurityLinks ON
GroupsModuleSecurityLinks.FK_lngGroupID =
UsersUserGroupLink.FK_lngGroupID or UsersUserGroupLink.FK_lngGroupID = 2 WHERE GroupsModuleSecurityLinks.lngRights > 0 AND UsersUserGroupLink.lngStatus > 19 AND UsersUserGroupLink.ysnAdminFlag = 0 AND GroupsModuleSecurityLinks.lngStatus > 19 AND GroupsModuleSecurityLinks.ysnFrontEndGroup = 0) ORDER BY ysnHasAccess DESC, txtLastName, txtFirstName
2. 使用CTE方法：使用UsersUserGroupLink AS（选择U.PK_autUserID，U.txtFirstName，U.txtLastName，U.lngStatus，U.ysnAdminFlag，UGL.FK_lngGroupID FROM tblUsers as U（NOLOCK）INNER JOIN tblUserGroupLink as UGL（NOLOCK）ON U. PK_autUserID = UGL.FK_lngUsersID），​​GroupsModuleSecurityLinks AS（选择MSL.lngRights，G.lngStatus，G.ysnFrontEndGroup，MSL.FK_lngGroupID从tblModuleSecurityLinks作为MSL（NOLOCK）INNER JOIN tblGroups作为G（NOLOCK）ON G.PK_autGroupID = MSL.FK_lngGroupID AND MSL.FK_lngModuleID = 28）（SELECT DISTINCT CSU。*，UsersUserGroupLink.txtFirstName，UsersUserGroupLink.txtLastName FROM UsersUserGroupLink内连接tblCRMShallowUsers作为CSU ON UsersUserGroupLink.PK_autUserID = CSU.PK_autUserID内连接GroupsModuleSecurityLinks ON GroupsModuleSecurityLinks.FK_lngGroupID = UsersUserGroupLink.FK_lngGroupID或UsersUserGroupLink.FK_lngGroupID = 2 WHERE GroupsModuleSecurityLinks.lngRights> 0 AND UsersUserGroupLink.lngStatus> 19 AND UsersUserGroupLink.ysnAdminFlag = 0 AND Gro upsModuleSecurityLinks.lngStatus> 19 AND GroupsModuleSecurityLinks.ysnFrontEndGroup = 0）ORDER BY ysnHasAccess DESC，txtLastName，txtFirstName