如何优化SQL查询：减少JOIN操作数量的方法

针对你当前查询中存在的多JOIN重复、冗余子查询问题，以下是具体优化方案，无需为每个JOIN单独创建表：

1. 提取重复JOIN逻辑为CTE #

你的两个子查询ASMT和Cut都重复执行了tCustomer → tContract → tContractProgram → tOperationalProgram → tPackage → tJobs的JOIN链，这会导致数据库重复计算。可以用**公共表表达式(CTE)**将这部分逻辑提取出来，复用结果：

WITH BaseJobData AS (
    SELECT 
        J.JobAssessID,
        J.JobID,
        CU.Customer,
        OP.ProgramName
    FROM tCustomer CU
    INNER JOIN tContract CN ON CU.CustomerID = CN.CustomerID
    INNER JOIN tContractProgram CP ON CN.ContractID = CP.ContractID
    INNER JOIN tOperationalProgram OP ON OP.ContractProgramID = CP.ContractProgramID
    INNER JOIN tPackage P ON OP.PackageID = P.PackageID
    INNER JOIN tJobs J ON P.PackageID = J.PackageID
    WHERE CU.Customer = 'TRANSGRID' 
      AND OP.ProgramName = 'Lidar Works 21-22 FY'
)

2. 合并相似子查询减少JOIN #

ASMT和Cut子查询结构高度相似，仅过滤条件不同，可以合并为一个子查询，通过字段标记区分两类数据，避免额外的JOIN操作：

WITH BaseJobData AS (
    -- 上述BaseJobData内容
),
CombinedActivitySOR AS (
    SELECT 
        SOR.JobAssessID,
        SOR.BOMDate,
        SOR.SORItemCode_Short,
        SOR.SORItemCode,
        SOR.LineTotal,
        SOR.SORItemQty,
        ACT.ActivityType,
        ACT.Action,
        ACT.Status,
        -- 标记数据类型
        CASE WHEN ACT.ActivityType IN ('Assessment', 'Update') THEN 'ASMT'
             WHEN ACT.ActivityType = 'Cutting' AND ACT.Status = 'Complete' THEN 'CUT'
             ELSE NULL END AS DataCategory
    FROM BaseJobData BJD
    INNER JOIN tActivity ACT ON ACT.JobAssessID = BJD.JobAssessID
    INNER JOIN AM6_SORITEM SOR ON SOR.SourceID = ACT.ActivityID 
                              AND SOR.JobID = ACT.JobID 
                              AND SOR.JobAssessID = ACT.JobAssessID 
    WHERE SOR.LineTotal <> 0
      AND (
          (ACT.ActivityType IN ('Assessment', 'Update'))
          OR
          (ACT.ActivityType = 'Cutting' AND ACT.Status = 'Complete')
      )
)

3. 简化LOOKUP子查询 #

原查询中LVFN子查询可以直接JOIN原表，或者如果这个LOOKUP逻辑经常使用，创建一个持久化视图替代临时子查询，避免重复执行过滤：

-- 直接JOIN方式替代子查询
LEFT JOIN [Active].[dbo].[LOOKUPVALUE_EVW] LVFN
    ON LVFN.LookupId = (SELECT LookupID FROM [Active].[dbo].[LOOKUP_EVW] WHERE Name = 'List_NoticeMethod')
    AND LVFN.Code = TT.FieldNotification

-- 或者创建持久化视图（如果复用率高）
CREATE VIEW vList_NoticeMethod AS
SELECT LV.Code, LV.[Description]
FROM [Active].[dbo].[LOOKUP_EVW] L
INNER JOIN [Active].[dbo].[LOOKUPVALUE_EVW] LV ON L.LookupID = LV.LookupId
WHERE L.Name = 'List_NoticeMethod';

-- 之后查询直接用视图
LEFT JOIN vList_NoticeMethod LVFN ON LVFN.Code = TT.FieldNotification

4. 最终整合后的查询示例 #

将上述优化点整合，完整查询如下：

WITH BaseJobData AS (
    SELECT 
        J.JobAssessID,
        J.JobID,
        CU.Customer,
        OP.ProgramName
    FROM tCustomer CU
    INNER JOIN tContract CN ON CU.CustomerID = CN.CustomerID
    INNER JOIN tContractProgram CP ON CN.ContractID = CP.ContractID
    INNER JOIN tOperationalProgram OP ON OP.ContractProgramID = CP.ContractProgramID
    INNER JOIN tPackage P ON OP.PackageID = P.PackageID
    INNER JOIN tJobs J ON P.PackageID = J.PackageID
    WHERE CU.Customer = 'TRANSGRID' 
      AND OP.ProgramName = 'Lidar Works 21-22 FY'
),
CombinedActivitySOR AS (
    SELECT 
        SOR.JobAssessID,
        SOR.BOMDate,
        SOR.SORItemCode_Short,
        SOR.SORItemCode,
        SOR.LineTotal,
        SOR.SORItemQty,
        ACT.ActivityType,
        ACT.Action,
        ACT.Status,
        CASE WHEN ACT.ActivityType IN ('Assessment', 'Update') THEN 'ASMT'
             WHEN ACT.ActivityType = 'Cutting' AND ACT.Status = 'Complete' THEN 'CUT'
             ELSE NULL END AS DataCategory
    FROM BaseJobData BJD
    INNER JOIN tActivity ACT ON ACT.JobAssessID = BJD.JobAssessID
    INNER JOIN AM6_SORITEM SOR ON SOR.SourceID = ACT.ActivityID 
                              AND SOR.JobID = ACT.JobID 
                              AND SOR.JobAssessID = ACT.JobAssessID 
    WHERE SOR.LineTotal <> 0
      AND (
          (ACT.ActivityType IN ('Assessment', 'Update'))
          OR
          (ACT.ActivityType = 'Cutting' AND ACT.Status = 'Complete')
      )
)
SELECT 
    -- 这里放入你需要的主查询字段
    BJD.JobAssessID,
    BJD.JobID,
    -- ASMT相关字段
    ASMT.BOMDate AS ASMT_BOMDate,
    ASMT.SORItemCode_Short AS ASMT_SORItemCode_Short,
    -- Cut相关字段
    Cut.BOMDate AS Cut_BOMDate,
    Cut.Status AS Cut_Status,
    -- 其他关联表字段
    t.*,
    TL.*,
    TT.*,
    LVFN.[Description]
FROM BaseJobData BJD
LEFT JOIN AM6_TASK t ON BJD.JobAssessID = t.JobAssessID
LEFT JOIN vTransgrid_LIDAR TL ON TL.JobAssessID = BJD.JobAssessID
LEFT JOIN Active.dbo.EXT_TASK_TRANSGRID_EVW TT ON TT.TaskID = BJD.JobID
LEFT JOIN vList_NoticeMethod LVFN ON LVFN.Code = TT.FieldNotification
LEFT JOIN CombinedActivitySOR ASMT 
    ON BJD.JobAssessID = ASMT.JobAssessID 
    AND ASMT.DataCategory = 'ASMT'
LEFT JOIN CombinedActivitySOR Cut 
    ON BJD.JobAssessID = Cut.JobAssessID 
    AND Cut.DataCategory = 'CUT'
    AND Cut.SORItemCode = ASMT.SORItemCode;

额外优化建议 #

• 索引优化：确保以下字段存在合适的索引：
  • 连接字段：tCustomer.CustomerID, tContract.CustomerID, tContract.ContractID, tContractProgram.ContractID, tOperationalProgram.ContractProgramID, tPackage.PackageID, tJobs.PackageID, tActivity.JobAssessID, AM6_SORITEM.SourceID/JobID/JobAssessID
  • 过滤字段：tCustomer.Customer, tOperationalProgram.ProgramName, tActivity.ActivityType, tActivity.Status, AM6_SORITEM.LineTotal
• 避免不必要的LEFT JOIN：如果某些LEFT JOIN的结果不可能为NULL（比如业务上必然存在关联），可以改为INNER JOIN减少数据库计算量。

内容的提问来源于stack exchange，提问作者Maho Lau