SQL Server 2008 R2中CTE使用局部变量致查询耗时激增5倍

我之前在SQL Server 2008 R2里也碰到过一模一样的情况——在CTE前声明变量后，查询耗时直接飙到原来的5倍，折腾了好一阵子才找到根源，下面给你拆解问题和解决方案：

问题根源：变量导致的查询优化器预估偏差 #

SQL Server 2008 R2的查询优化器在处理声明式变量时，没办法像直接写常量那样，根据变量的实际值去精准判断数据量、选择最优索引。它会用一套默认的统计信息预估来生成执行计划，这就很容易出现“本来该走索引却走了全表扫描”“CTE的累计求和逻辑被迫低效执行”的情况，直接导致耗时暴增。

你的场景里，变量用来转换本地时间到Unix时间，再作为过滤条件传给CTE的累计求和逻辑，这种带变量的过滤条件正好踩中了2008 R2优化器的这个坑。

可行的解决方案 #

1. 加OPTION (RECOMPILE)提示（改动最小，见效最快） #

直接在查询末尾加上这个选项，强制优化器在执行时根据变量的实际值重新生成最优执行计划，而不是用默认的预估计划。示例代码：

Declare @StartTime bigint 
Declare @EndTime bigint 
-- 这里是你的本地时间转Unix时间的赋值逻辑
SET @StartTime = DATEDIFF(SECOND, '1970-01-01', DATEADD(DAY, -7, GETDATE()));
SET @EndTime = DATEDIFF(SECOND, '1970-01-01', GETDATE());

WITH Accumulation AS (
    -- 你的生产数据累计求和逻辑
    SELECT 
        ProductionID,
        UnixTime,
        ProductionQty,
        SUM(ProductionQty) OVER (ORDER BY UnixTime) AS RunningTotal
    FROM ProductionData
    WHERE UnixTime BETWEEN @StartTime AND @EndTime
)
SELECT * FROM Accumulation
OPTION (RECOMPILE); -- 关键：让优化器根据实际变量值生成计划

2. 改成存储过程用参数代替变量 #

把变量改成存储过程的输入参数，SQL Server对存储过程参数的处理会更智能，能更好地利用统计信息生成计划。示例：

CREATE PROCEDURE GetProductionAccumulation
    @StartTime bigint,
    @EndTime bigint
AS
BEGIN
    SET NOCOUNT ON;
    
    WITH Accumulation AS (
        SELECT 
            ProductionID,
            UnixTime,
            ProductionQty,
            SUM(ProductionQty) OVER (ORDER BY UnixTime) AS RunningTotal
        FROM ProductionData
        WHERE UnixTime BETWEEN @StartTime AND @EndTime
    )
    SELECT * FROM Accumulation;
END;

调用时直接传参数就行：

DECLARE @Start bigint, @End bigint;
SET @Start = DATEDIFF(SECOND, '1970-01-01', DATEADD(DAY, -7, GETDATE()));
SET @End = DATEDIFF(SECOND, '1970-01-01', GETDATE());

EXEC GetProductionAccumulation @StartTime = @Start, @EndTime = @End;

3. 用参数化动态SQL嵌入常量（避免注入风险） #

如果上面两种方法都不适用，可以把变量值作为参数传入动态SQL，既让优化器能精准预估数据量，又能避免SQL注入：

Declare @StartTime bigint 
Declare @EndTime bigint 
Declare @SQL nvarchar(max)

SET @StartTime = DATEDIFF(SECOND, '1970-01-01', DATEADD(DAY, -7, GETDATE()));
SET @EndTime = DATEDIFF(SECOND, '1970-01-01', GETDATE());

-- 参数化动态SQL，避免注入风险
SET @SQL = N'
    WITH Accumulation AS (
        SELECT 
            ProductionID,
            UnixTime,
            ProductionQty,
            SUM(ProductionQty) OVER (ORDER BY UnixTime) AS RunningTotal
        FROM ProductionData
        WHERE UnixTime BETWEEN @Start AND @End
    )
    SELECT * FROM Accumulation;';

EXEC sp_executesql @SQL, 
    N'@Start bigint, @End bigint',
    @Start = @StartTime,
    @End = @EndTime;

4. 检查并优化索引 #

最后别忘了确认你的UnixTime字段有没有合适的覆盖索引——包含CTE里用到的所有列，这样查询时不用回表，效率会大幅提升：

CREATE NONCLUSTERED INDEX IX_ProductionData_UnixTime_Include
ON ProductionData (UnixTime)
INCLUDE (ProductionID, ProductionQty); -- 包含CTE需要的所有列

总结 #

优先试试OPTION (RECOMPILE)，这个改动最小，能快速验证是不是优化器预估的问题。如果还是不行，再考虑存储过程或者索引优化——SQL Server 2008 R2的优化器确实对变量不太友好，这些方法基本能解决你的耗时问题。

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