SQL Server多LEFT JOIN同表查询执行过慢，求性能优化方案

针对你当前通过多次LEFT JOIN同一张视图获取5级父节点名称的慢查询问题，可通过以下几种方式优化：

1. 移除冗余子查询，直接关联视图 #

当前SQL中每个AP/AP2/AP3等都用了相同的子查询，导致数据库重复解析和扫描视图。直接关联视图本身即可减少不必要的开销，同时用COALESCE替代嵌套ISNULL，逻辑一致且代码更简洁：

SELECT
    YPartner = ISNULL(AP.AccName, AC.AccName),
    YPartner2 = COALESCE(AP2.AccName, AP.AccName, AC.AccName),
    YPartner3 = COALESCE(AP3.AccName, AP2.AccName, AP.AccName, AC.AccName),
    YPartner4 = COALESCE(AP4.AccName, AP3.AccName, AP2.AccName, AP.AccName, AC.AccName),
    YPartner5 = COALESCE(AP5.AccName, AP4.AccName, AP3.AccName, AP2.AccName, AP.AccName, AC.AccName)
FROM 
    dbo.[AB_ProcessDetail] A WITH(NOLOCK) 
LEFT JOIN 
    AB_AccCard AC WITH(NOLOCK) ON AC.YID = A.YAccCardId AND a.DataBaseName = ac.DataBaseName
LEFT JOIN  
    AB_AccCard AP WITH(NOLOCK) ON AP.YID = ac.YParentID AND AP.DataBaseName = AC.DataBaseName
LEFT JOIN  
    AB_AccCard AP2 WITH(NOLOCK) ON AP2.YID = AP.YParentID AND AP2.DataBaseName = AC.DataBaseName
LEFT JOIN  
    AB_AccCard AP3 WITH(NOLOCK) ON AP3.YID = AP2.YParentID AND AP3.DataBaseName = AC.DataBaseName
LEFT JOIN  
    AB_AccCard AP4 WITH(NOLOCK) ON AP4.YID = AP3.YParentID AND AP4.DataBaseName = AC.DataBaseName
LEFT JOIN  
    AB_AccCard AP5 WITH(NOLOCK) ON AP5.YID = AP4.YParentID AND AP5.DataBaseName = AC.DataBaseName

2. 用递归CTE预生成层级数据（推荐） #

对于固定层级的树形查询，递归CTE比多次JOIN效率更高，可一次性遍历并生成所有节点的5级父节点信息，再与主表关联：

WITH AccCardHierarchy AS (
    -- 基础节点：当前节点+1级父节点
    SELECT
        YID,
        DataBaseName,
        AccName AS Level1Name,
        YParentID AS Level1ParentID,
        ISNULL(AP.AccName, AccName) AS Level2Name,
        ISNULL(AP.YParentID, YParentID) AS Level2ParentID
    FROM AB_AccCard AC WITH(NOLOCK)
    LEFT JOIN AB_AccCard AP WITH(NOLOCK) ON AP.YID = AC.YParentID AND AP.DataBaseName = AC.DataBaseName
    UNION ALL
    -- 递归生成3-5级父节点
    SELECT
        h.YID,
        h.DataBaseName,
        h.Level1Name,
        h.Level1ParentID,
        h.Level2Name,
        h.Level2ParentID,
        COALESCE(AP3.AccName, h.Level2Name) AS Level3Name,
        COALESCE(AP3.YParentID, h.Level2ParentID) AS Level3ParentID,
        COALESCE(AP4.AccName, h.Level3Name) AS Level4Name,
        COALESCE(AP4.YParentID, h.Level3ParentID) AS Level4ParentID,
        COALESCE(AP5.AccName, h.Level4Name) AS Level5Name
    FROM AccCardHierarchy h
    LEFT JOIN AB_AccCard AP3 WITH(NOLOCK) ON AP3.YID = h.Level2ParentID AND AP3.DataBaseName = h.DataBaseName
    LEFT JOIN AB_AccCard AP4 WITH(NOLOCK) ON AP4.YID = AP3.YParentID AND AP4.DataBaseName = h.DataBaseName
    LEFT JOIN AB_AccCard AP5 WITH(NOLOCK) ON AP5.YID = AP4.YParentID AND AP5.DataBaseName = h.DataBaseName
    WHERE h.Level2ParentID IS NOT NULL
)
SELECT
    YPartner = h.Level2Name,
    YPartner2 = h.Level3Name,
    YPartner3 = h.Level4Name,
    YPartner4 = h.Level5Name,
    YPartner5 = h.Level5Name
FROM dbo.[AB_ProcessDetail] A WITH(NOLOCK)
LEFT JOIN AccCardHierarchy h ON h.YID = A.YAccCardId AND h.DataBaseName = A.DataBaseName

递归CTE的核心是一次性遍历树形结构，避免多次JOIN带来的重复扫描，适合大数据量的层级查询场景。

3. 添加针对性索引 #

索引是提升JOIN性能的核心，针对关联字段创建复合索引：

• 针对AB_AccCard创建覆盖索引：

  CREATE NONCLUSTERED INDEX IX_AB_AccCard_YID_DBName ON AB_AccCard(YID, DataBaseName) INCLUDE(YParentID, AccName);
  

  该索引覆盖了JOIN条件和需要返回的字段，避免回表查询。
• 针对AB_ProcessDetail创建关联索引：

  CREATE NONCLUSTERED INDEX IX_AB_ProcessDetail_YAccCardId_DBName ON AB_ProcessDetail(YAccCardId, DataBaseName);
  

  加速主表与视图的关联匹配。

4. 优化视图AB_AccCard的底层逻辑 #

如果AB_AccCard是复杂视图，建议：

• 查看视图定义，优化底层查询的过滤条件和索引；
• 若数据库支持（如SQL Server），可将其转换为索引视图，提前计算并存储数据，减少实时计算开销。

内容的提问来源于stack exchange，提问作者Khaled Nasser