自连接查询性能优化求助：含指定条件时查询耗时过长

核心问题分析 #

原查询中的关联子查询会对每一条符合外层条件的记录重复执行一次MAX(B.AAD_DATE)计算，这是导致查询耗时剧增的根本原因。此外，对AAD_DATE使用MONTH()和YEAR()函数会阻止索引的有效使用，进一步降低性能。

优化方案 #

方案1：将关联子查询改为独立子查询（一次计算最大日期） #

先单独计算目标月份的最大日期，再用该值过滤数据，避免重复计算：

SELECT '00-30 #66ff66', SUM(A.AAD_00TO30)
FROM MST_AR_AS_ON_DATE A
WHERE A.AAD_RESP_NOW = 4
  AND A.AAD_DATE = (
      SELECT MAX(AAD_DATE)
      FROM MST_AR_AS_ON_DATE
      WHERE AAD_DATE BETWEEN 
          DATEFROMPARTS(YEAR(DATEADD(MM,-1,GETDATE())), MONTH(DATEADD(MM,-1,GETDATE())), 1)
          AND EOMONTH(DATEADD(MM,-1,GETDATE()))
  )

方案2：使用CTE预计算日期范围和最大日期 #

通过公共表表达式（CTE）提前计算目标月份的范围和最大日期，逻辑更清晰：

WITH MonthDateInfo AS (
    SELECT 
        DATEFROMPARTS(YEAR(DATEADD(MM,-1,GETDATE())), MONTH(DATEADD(MM,-1,GETDATE())), 1) AS MonthStart,
        EOMONTH(DATEADD(MM,-1,GETDATE())) AS MonthEnd
),
MonthMaxDate AS (
    SELECT MAX(AAD_DATE) AS MaxDate
    FROM MST_AR_AS_ON_DATE
    CROSS JOIN MonthDateInfo
    WHERE AAD_DATE BETWEEN MonthStart AND MonthEnd
)
SELECT '00-30 #66ff66', SUM(A.AAD_00TO30)
FROM MST_AR_AS_ON_DATE A
JOIN MonthMaxDate M ON A.AAD_DATE = M.MaxDate
WHERE A.AAD_RESP_NOW = 4

方案3：添加复合索引提升查询效率 #

如果表数据量较大，建议创建包含查询所需字段的复合索引，让数据库直接通过索引获取数据，避免回表查询：

CREATE NONCLUSTERED INDEX IX_MST_AR_AS_ON_DATE_Date_Resp_00TO30
ON MST_AR_AS_ON_DATE (AAD_DATE, AAD_RESP_NOW)
INCLUDE (AAD_00TO30);

优化说明 #

1. 消除关联子查询：将原查询中每行都执行的子查询改为仅执行一次的独立查询，大幅减少计算量。
2. 改用日期范围查询：用DATEFROMPARTS和EOMONTH生成目标月份的起止日期，替代MONTH()和YEAR()函数，确保AAD_DATE上的索引可以被有效使用。
3. 复合索引优化：覆盖查询所需的过滤字段和聚合字段，提升查询的IO效率。

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