如何优化执行耗时近2分钟的SQL查询？已建索引仍卡顿

我处理的是大表，执行以下SQL耗时近2分钟，即便已创建索引，速度依旧过慢，恳请各位提供优化建议。

原SQL代码 #

DECLARE @FromDate datetime
DECLARE @ToDate datetime
DECLARE @SSNumber int
SET @FromDate = '2023-03-01'
SET @ToDate = '2023-07-01'
SET @SSNumber = -1

DECLARE @CareDates TABLE(CareDate datetime, DOW varchar(20))
DECLARE @curDate datetime
DECLARE @curDOW varchar(20)

SET @curDate = @FromDate

WHILE DATEDIFF(day, @curDate, @ToDate + 1) > 0
BEGIN
     SET @curDOW = DATENAME(dw, @curDate)
     INSERT INTO @CareDates (CareDate, DOW) VALUES (@curDate, @curDOW)

     SET @curDate = DATEADD(day, 1, @curDate)
     PRINT DATEDIFF(day, @curDate, @ToDate)
END;

WITH personal_care AS
(
     SELECT PersonalCareID, SSNumber, ItemCode, CareSchedule, IsCurrent, StartDate
          , DATEADD(DAY, -1, (ISNULL(LEAD(StartDate,1) OVER (
              PARTITION BY SSNumber, ItemCode
              ORDER BY SSNumber, ItemCode, StartDate), '9999-12-31'))) AS EndDate
     FROM HOMEResidentPersonalCare
)

SELECT PC.PersonalCareID, 
    CD.CareDate, 
    CD.DOW, 
    PC.SSNumber, 
    PC.ItemCode, 
    PC.CareSchedule, 
    PC.IsCurrent, 
    PC.StartDate, 
    PC.EndDate, 
    PCS.CareScheduleID, 
    PCS.[Shift], 
    PCS.EmployeeID,
    HRPCP.CareProvidedID, 
    ISNULL(HRPCP.Status,'I') AS CareStatus
FROM @CareDates CD
CROSS JOIN personal_care PC 
INNER JOIN HomeResidentPersonalCareSchedule PCS ON PCS.PersonalCareID = PC.PersonalCareID AND (PCS.DayOfWeek = CD.DOW OR PCS.DayOfWeek = 'All')
LEFT JOIN HomeResidentPersonalCareProvided HRPCP ON HRPCP.CareTypeID = PC.ItemCode AND HRPCP.SSNumber = PC.SSNumber AND HRPCP.[Shift] = PCS.[Shift] AND HRPCP.CareDate = CD.CareDate
WHERE CD.CareDate BETWEEN PC.StartDate AND PC.EndDate
AND (HRPCP.Status IS NULL OR HRPCP.Status <> 'D')

执行计划核心瓶颈 #

执行计划显示主要性能问题集中在：循环生成日期表的逐行插入IO开销、交叉连接导致的笛卡尔积海量计算、OR条件引发的索引失效，以及大表全表扫描带来的高资源占用。

优化建议 #

• 替换循环生成日期表的方式：用递归CTE生成日期表并存入临时表，比WHILE循环效率提升显著，且临时表能提供更准确的统计信息供优化器生成最优计划：

  -- 替换原WHILE循环的日期生成逻辑
  DECLARE @FromDate datetime = '2023-03-01';
  DECLARE @ToDate datetime = '2023-07-01';
  
  WITH CareDates_CTE AS (
      SELECT @FromDate AS CareDate, DATENAME(dw, @FromDate) AS DOW
      UNION ALL
      SELECT DATEADD(day, 1, CareDate), DATENAME(dw, DATEADD(day, 1, CareDate))
      FROM CareDates_CTE
      WHERE CareDate < @ToDate
  )
  SELECT CareDate, DOW INTO #CareDates FROM CareDates_CTE OPTION (MAXRECURSION 0);
  

• 消除笛卡尔积（CROSS JOIN）：原逻辑先做交叉连接再过滤，会生成海量中间数据，改成先过滤personal_care中与查询日期范围相关的记录，再和日期表做内连接：

  WITH personal_care AS (
       SELECT PersonalCareID, SSNumber, ItemCode, CareSchedule, IsCurrent, StartDate
            , DATEADD(DAY, -1, (ISNULL(LEAD(StartDate,1) OVER (
                PARTITION BY SSNumber, ItemCode
                ORDER BY StartDate), '9999-12-31'))) AS EndDate
       FROM HOMEResidentPersonalCare
       -- 提前过滤掉不在查询日期范围内的记录，减少后续连接基数
       WHERE StartDate <= @ToDate AND EndDate >= @FromDate
  )
  SELECT PC.PersonalCareID, 
      CD.CareDate, 
      CD.DOW, 
      PC.SSNumber, 
      PC.ItemCode, 
      PC.CareSchedule, 
      PC.IsCurrent, 
      PC.StartDate, 
      PC.EndDate, 
      PCS.CareScheduleID, 
      PCS.[Shift], 
      PCS.EmployeeID,
      HRPCP.CareProvidedID, 
      ISNULL(HRPCP.Status,'I') AS CareStatus
  FROM personal_care PC
  INNER JOIN #CareDates CD ON CD.CareDate BETWEEN PC.StartDate AND PC.EndDate
  INNER JOIN HomeResidentPersonalCareSchedule PCS ON PCS.PersonalCareID = PC.PersonalCareID 
      AND (PCS.DayOfWeek = CD.DOW OR PCS.DayOfWeek = 'All')
  LEFT JOIN HomeResidentPersonalCareProvided HRPCP ON HRPCP.CareTypeID = PC.ItemCode 
      AND HRPCP.SSNumber = PC.SSNumber 
      AND HRPCP.[Shift] = PCS.[Shift] 
      AND HRPCP.CareDate = CD.CareDate
  WHERE (HRPCP.Status IS NULL OR HRPCP.Status <> 'D')
  

• 优化索引设计：

  • 给HOMEResidentPersonalCare创建覆盖索引，支持LEAD函数和查询字段，避免回表：

    CREATE NONCLUSTERED INDEX IX_HOMEResidentPersonalCare_SSNumber_ItemCode_StartDate 
    ON HOMEResidentPersonalCare (SSNumber, ItemCode, StartDate) 
    INCLUDE (PersonalCareID, CareSchedule, IsCurrent);
    

  • 给HomeResidentPersonalCareSchedule创建匹配连接条件的索引：

    CREATE NONCLUSTERED INDEX IX_HomeResidentPersonalCareSchedule_PersonalCareID_DayOfWeek 
    ON HomeResidentPersonalCareSchedule (PersonalCareID, DayOfWeek) 
    INCLUDE (CareScheduleID, Shift, EmployeeID);
    

  • 给HomeResidentPersonalCareProvided创建覆盖左连接条件的复合索引：

    CREATE NONCLUSTERED INDEX IX_HomeResidentPersonalCareProvided_SSNumber_CareTypeID_CareDate_Shift 
    ON HomeResidentPersonalCareProvided (SSNumber, CareTypeID, CareDate, Shift) 
    INCLUDE (CareProvidedID, Status);
    

• 拆分OR条件：PCS.DayOfWeek = CD.DOW OR PCS.DayOfWeek = 'All'会导致索引无法被有效利用，拆成两个UNION ALL分支，提升查询效率：

  WITH personal_care AS (
       SELECT PersonalCareID, SSNumber, ItemCode, CareSchedule, IsCurrent, StartDate
            , DATEADD(DAY, -1, (ISNULL(LEAD(StartDate,1) OVER (
                PARTITION BY SSNumber, ItemCode
                ORDER BY StartDate), '9999-12-31'))) AS EndDate
       FROM HOMEResidentPersonalCare
       WHERE StartDate <= @ToDate AND EndDate >= @FromDate
  )
  -- 分支1：匹配具体星期几
  SELECT PC.PersonalCareID, 
      CD.CareDate, 
      CD.DOW, 
      PC.SSNumber, 
      PC.ItemCode, 
      PC.CareSchedule, 
      PC.IsCurrent, 
      PC.StartDate, 
      PC.EndDate, 
      PCS.CareScheduleID, 
      PCS.[Shift], 
      PCS.EmployeeID,
      HRPCP.CareProvidedID, 
      ISNULL(HRPCP.Status,'I') AS CareStatus
  FROM personal_care PC
  INNER JOIN #CareDates CD ON CD.CareDate BETWEEN PC.StartDate AND PC.EndDate
  INNER JOIN HomeResidentPersonalCareSchedule PCS ON PCS.PersonalCareID = PC.PersonalCareID 
      AND PCS.DayOfWeek = CD.DOW
  LEFT JOIN HomeResidentPersonalCareProvided HRPCP ON HRPCP.CareTypeID = PC.ItemCode 
      AND HRPCP.SSNumber = PC.SSNumber 
      AND HRPCP.[Shift] = PCS.[Shift] 
      AND HRPCP.CareDate = CD.CareDate
  WHERE (HRPCP.Status IS NULL OR HRPCP.Status <> 'D')
  
  UNION ALL
  
  -- 分支2：匹配所有星期几
  SELECT PC.PersonalCareID, 
      CD.CareDate, 
      CD.DOW, 
      PC.SSNumber, 
      PC.ItemCode, 
      PC.CareSchedule, 
      PC.IsCurrent, 
      PC.StartDate, 
      PC.EndDate, 
      PCS.CareScheduleID, 
      PCS.[Shift], 
      PCS.EmployeeID,
      HRPCP.CareProvidedID, 
      ISNULL(HRPCP.Status,'I') AS CareStatus
  FROM personal_care PC
  INNER JOIN #CareDates CD ON CD.CareDate BETWEEN PC.StartDate AND PC.EndDate
  INNER JOIN HomeResidentPersonalCareSchedule PCS ON PCS.PersonalCareID = PC.PersonalCareID 
      AND PCS.DayOfWeek = 'All'
  LEFT JOIN HomeResidentPersonalCareProvided HRPCP ON HRPCP.CareTypeID = PC.ItemCode 
      AND HRPCP.SSNumber = PC.SSNumber 
      AND HRPCP.[Shift] = PCS.[Shift] 
      AND HRPCP.CareDate = CD.CareDate
  WHERE (HRPCP.Status IS NULL OR HRPCP.Status <> 'D')
  

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