SQL Server按小时间隔采样大表数据的性能优化方案求助

现有SQL Server 2016环境下的一张超大表data_readings，包含数百万行数据，数据来自多个可变数据源（Source值不固定，随时可能新增），时间跨度多年且记录间隔不规则，表结构无法修改。

表的典型数据如下：

该表的聚集主键为PK_DATA_READINGS，列顺序为[Source] ASC、[Date] ASC。

需求：指定日期范围和小时间隔X，每个数据源每X小时仅返回一条记录（例如上述数据中Source=1的第2条记录因与第1条间隔不足X小时，不返回）。

尝试过的SQL语句（性能不佳） #

以下SQL执行速度极慢，耗时常超5分钟：

DECLARE @Start_Date DATETIME = '2023/01/01 00:00:00',
        @End_Date DATETIME = '2023/02/01 00:00:00',
        @Interval_Hours INT = 4

;WITH HOURLY_DATA AS (
     SELECT d.Source, 
            d.Date, 
            d.Reading,
            ROW_NUMBER() OVER (PARTITION BY d.Source, DATEDIFF(HOUR, @Start_Date, d.DATE) / @Interval_Hours ORDER BY d.SOURCE, d.DATE) AS SOURCE_HOUR_ROW
     FROM data_readings d
     WHERE d.DATE BETWEEN @Start_Date AND @End_Date
)
SELECT h.Source,
       h.Date,
       h.Reading
FROM HOURLY_DATA h
WHERE h.SOURCE_HOUR_ROW = 1

优化方案 #

方案1：利用聚集主键有序性的递归CTE #

原SQL中DATEDIFF(HOUR, @Start_Date, d.DATE) / @Interval_Hours作为分区键，会导致SQL Server无法高效利用聚集索引的有序性，需逐行计算该值，消耗大量CPU。以下方案基于聚集索引的Source+Date排序特性，通过递归CTE直接定位每个间隔段的第一条记录：

DECLARE @Start_Date DATETIME = '2023/01/01 00:00:00',
        @End_Date DATETIME = '2023/02/01 00:00:00',
        @Interval_Hours INT = 4;

WITH RECURSIVE_CTE AS (
    -- 锚点：每个Source在起始日期后的第一条记录
    SELECT 
        d.Source,
        d.Date,
        d.Reading,
        DATEADD(HOUR, @Interval_Hours, d.Date) AS Next_Interval_Start
    FROM data_readings d
    WHERE d.Date >= @Start_Date
      AND NOT EXISTS (
          SELECT 1 
          FROM data_readings d2
          WHERE d2.Source = d.Source
            AND d2.Date >= @Start_Date
            AND d2.Date < d.Date
      )
    UNION ALL
    -- 递归：找到每个Source下一个间隔段的第一条记录
    SELECT 
        d.Source,
        d.Date,
        d.Reading,
        DATEADD(HOUR, @Interval_Hours, d.Date) AS Next_Interval_Start
    FROM data_readings d
    JOIN RECURSIVE_CTE r ON d.Source = r.Source
    WHERE d.Date >= r.Next_Interval_Start
      AND d.Date <= @End_Date
      AND NOT EXISTS (
          SELECT 1 
          FROM data_readings d2
          WHERE d2.Source = d.Source
            AND d2.Date >= r.Next_Interval_Start
            AND d2.Date < d.Date
      )
)
SELECT Source, Date, Reading
FROM RECURSIVE_CTE
WHERE Date <= @End_Date
ORDER BY Source, Date
OPTION (MAXRECURSION 0);

该方案避免全表扫描和大量分区键计算，每个递归步骤仅查找当前Source下一个间隔起始点后的第一条记录，充分利用聚集索引的有序性。

方案2：优化分区键计算逻辑 #

将分区键改为基于固定起始点计算的间隔时间，减少依赖变量的动态计算，让SQL Server更好地利用聚集索引：

DECLARE @Start_Date DATETIME = '2023/01/01 00:00:00',
        @End_Date DATETIME = '2023/02/01 00:00:00',
        @Interval_Hours INT = 4;

SELECT 
    Source,
    Date,
    Reading
FROM (
    SELECT 
        d.Source,
        d.Date,
        d.Reading,
        ROW_NUMBER() OVER (
            PARTITION BY 
                d.Source, 
                DATEADD(HOUR, 
                        DATEDIFF(HOUR, '19000101', d.Date) / @Interval_Hours * @Interval_Hours, 
                        '19000101')
            ORDER BY d.Date
        ) AS RowNum
    FROM data_readings d
    WHERE d.Date BETWEEN @Start_Date AND @End_Date
) t
WHERE RowNum = 1;

这里用固定起始点'19000101'计算间隔起始时间，替代原SQL中依赖@Start_Date的动态计算，降低CPU开销，同时适配现有聚集索引的排序逻辑。

方案3：使用APPLY按Source单独处理 #

先提取日期范围内的所有唯一Source，再对每个Source单独执行间隔筛选，减少全局计算的开销：

DECLARE @Start_Date DATETIME = '2023/01/01 00:00:00',
        @End_Date DATETIME = '2023/02/01 00:00:00',
        @Interval_Hours INT = 4;

SELECT 
    s.Source,
    d.Date,
    d.Reading
FROM (
    -- 获取日期范围内的唯一Source
    SELECT DISTINCT Source
    FROM data_readings
    WHERE Date BETWEEN @Start_Date AND @End_Date
) s
CROSS APPLY (
    -- 对每个Source按间隔取第一条记录
    SELECT TOP (1) WITH TIES 
        Date,
        Reading
    FROM data_readings d
    WHERE d.Source = s.Source
      AND d.Date BETWEEN @Start_Date AND @End_Date
    ORDER BY 
        ROW_NUMBER() OVER (
            PARTITION BY DATEDIFF(HOUR, @Start_Date, d.Date) / @Interval_Hours 
            ORDER BY d.Date
        )
) d;

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