大数据集下Fact表关联SQL脚本执行时长优化咨询

需求说明 #

现有两个Fact表：

• Fact1：包含字段 DeviceKey、DateKey、TimeKey、CustomersIn
• Fact2：包含字段 DeviceKey、EventDateKey、EventTimeKey、ErrorKey

需要筛选Fact2中满足以下条件的记录：

1. 事件时间（EventDateKey+EventTimeKey）大于对应DeviceKey在Fact1中的最大时间（DateKey+TimeKey）
2. 该最大时间距离当前时间（2023/03/01 11:58）已超过48小时

原低效SQL #

SELECT f2.*, s.maxdatetimekey
FROM Fact2 f2
INNER JOIN (
  SELECT DeviceKey, MAX(CONCAT(DateKey, FORMAT(TimeKey, '0#####'))) as maxdatetimeKey
  FROM FactTelemetry f1
  GROUP BY DeviceKey
) s ON s.DeviceKey = f2.DeviceKey AND CONCAT(EventDateKey, FORMAT(EventTimeKey, '0#####')) > maxdatetimeKey;
GO

核心问题分析 #

原脚本的性能瓶颈在于：

• 使用CONCAT+FORMAT拼接字符串做时间比较，无法利用索引，且字符串运算CPU开销极大
• 未提前过滤不符合“最大时间超48小时”的设备，导致关联数据量过大

优化方案 #

1. 替换字符串拼接，用真实日期时间类型计算 #

把DateKey/TimeKey、EventDateKey/EventTimeKey转换为datetime类型再做比较，避免字符串运算的低效问题：

WITH DeviceMaxTime AS (
    SELECT 
        DeviceKey,
        MAX(CAST(CAST(DateKey AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(TimeKey AS VARCHAR(6)),6),3,0,':'),6,0,':') AS DATETIME)) AS MaxDeviceTime
    FROM Fact1
    GROUP BY DeviceKey
    -- 提前过滤掉最大时间未超48小时的设备，减少后续关联量
    HAVING MAX(CAST(CAST(DateKey AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(TimeKey AS VARCHAR(6)),6),3,0,':'),6,0,':') AS DATETIME)) < DATEADD(HOUR, -48, '2023-03-01 11:58:00')
)
SELECT 
    f2.*,
    dmt.MaxDeviceTime
FROM Fact2 f2
INNER JOIN DeviceMaxTime dmt 
    ON f2.DeviceKey = dmt.DeviceKey
    AND CAST(CAST(f2.EventDateKey AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(f2.EventTimeKey AS VARCHAR(6)),6),3,0,':'),6,0,':') AS DATETIME) > dmt.MaxDeviceTime;
GO

2. 创建针对性索引 #

给两张表添加覆盖索引，让查询直接走索引无需回表：

-- Fact1的覆盖索引：分组计算最大时间时直接用索引数据
CREATE NONCLUSTERED INDEX IX_Fact1_DeviceKey_DateTime ON Fact1(DeviceKey) INCLUDE(DateKey, TimeKey);

-- Fact2的非聚集索引：快速定位符合条件的设备和事件时间
CREATE NONCLUSTERED INDEX IX_Fact2_DeviceKey_EventDateTime ON Fact2(DeviceKey, EventDateKey, EventTimeKey) INCLUDE(ErrorKey);

3. 添加持久化计算列（长期优化方案） #

如果这类时间查询是高频操作，建议给两张表添加持久化计算列，把时间拼接逻辑固化：

-- 给Fact1添加持久化计算列
ALTER TABLE Fact1 ADD DeviceDateTime AS CAST(CAST(DateKey AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(TimeKey AS VARCHAR(6)),6),3,0,':'),6,0,':') AS DATETIME) PERSISTED;

-- 给Fact2添加持久化计算列
ALTER TABLE Fact2 ADD EventDateTime AS CAST(CAST(EventDateKey AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(EventTimeKey AS VARCHAR(6)),6),3,0,':'),6,0,':') AS DATETIME) PERSISTED;

然后基于计算列创建索引：

CREATE NONCLUSTERED INDEX IX_Fact1_DeviceKey_DeviceDateTime ON Fact1(DeviceKey) INCLUDE(DeviceDateTime);
CREATE NONCLUSTERED INDEX IX_Fact2_DeviceKey_EventDateTime ON Fact2(DeviceKey, EventDateTime) INCLUDE(ErrorKey);

优化后的查询会更简洁高效：

WITH DeviceMaxTime AS (
    SELECT 
        DeviceKey,
        MAX(DeviceDateTime) AS MaxDeviceTime
    FROM Fact1
    GROUP BY DeviceKey
    HAVING MAX(DeviceDateTime) < DATEADD(HOUR, -48, '2023-03-01 11:58:00')
)
SELECT 
    f2.*,
    dmt.MaxDeviceTime
FROM Fact2 f2
INNER JOIN DeviceMaxTime dmt 
    ON f2.DeviceKey = dmt.DeviceKey
    AND f2.EventDateTime > dmt.MaxDeviceTime;
GO

4. 分批处理（超大数据量场景） #

如果数据量大到单批次无法处理，可以按DeviceKey范围分批查询，避免一次性占用过多资源：

DECLARE @StartKey INT = 1;
DECLARE @EndKey INT = 1000;
WHILE @StartKey <= (SELECT MAX(DeviceKey) FROM Fact2)
BEGIN
    WITH DeviceMaxTime AS (
        SELECT 
            DeviceKey,
            MAX(DeviceDateTime) AS MaxDeviceTime
        FROM Fact1
        WHERE DeviceKey BETWEEN @StartKey AND @EndKey
        GROUP BY DeviceKey
        HAVING MAX(DeviceDateTime) < DATEADD(HOUR, -48, '2023-03-01 11:58:00')
    )
    SELECT 
        f2.*,
        dmt.MaxDeviceTime
    INTO #TempResult -- 也可直接插入目标表
    FROM Fact2 f2
    INNER JOIN DeviceMaxTime dmt 
        ON f2.DeviceKey = dmt.DeviceKey
        AND f2.EventDateTime > dmt.MaxDeviceTime
    WHERE f2.DeviceKey BETWEEN @StartKey AND @EndKey;

    SET @StartKey = @EndKey + 1;
    SET @EndKey = @EndKey + 1000;
END
-- 合并结果
SELECT * FROM #TempResult;
DROP TABLE #TempResult;
GO

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