You need to enable JavaScript to run this app. SQL Server 2017 含递归CTE的视图LEFT OUTER JOIN性能问题
优惠活动
大模型
产品
解决方案
定价
更多

SQL Server 2017 含递归CTE的视图LEFT OUTER JOIN性能问题

性能问题核心根因
  • 带递归CTE的视图存在固有计算特性:递归结果是流式生成的,SQL Server优化器无法将关联谓词(来自TABLE_2的PLAN_NR、PERIOD_NR匹配条件)下推到递归计算流程中
  • 当查询仅匹配关联字段、不读取START_DATE值时,优化器可通过元数据比对快速返回结果,因此速度尚可;一旦需要读取START_DATE字段值,优化器会选择嵌套循环执行计划,针对TABLE_2的每一行都独立触发一次递归CTE的全量计算,4000行数据就会触发4000次重复递归,这就是性能暴跌的核心原因
  • 提前把视图结果写入临时表相当于手动把全量递归结果物化一次,避免了重复计算,因此能达到2秒的处理速度,但因为视图本身的递归特性,优化器不会自动做这个物化动作

优化方案(无需落地持久化业务表/临时表)

方案1:将视图改造为内联表值函数,关联时传递参数过滤范围

内联表值函数(ITVF)和视图的差异是支持传参,优化器会将函数逻辑直接内联到查询中,且能识别参数过滤范围,避免全量递归:

-- 替换原视图为ITVF
CREATE OR ALTER FUNCTION dbo.fn_GET_ALL_PERIODS (@PLAN_NR decimal(28,6))
RETURNS TABLE
AS RETURN
(
    WITH rec_cte AS 
    (
        SELECT 
            PLAN_NR, START_DATE,
            1 period_nr, DATEADD(day, 7, START_DATE) next_date
        FROM 
            TABLE_1
        WHERE PLAN_NR = @PLAN_NR -- 仅计算传入的方案编号对应的周期序列
        UNION ALL
        SELECT 
            PLAN_NR, next_date,
            period_nr + 1, DATEADD(day, 7, next_date)
        FROM 
            rec_cte       
        WHERE 
            period_nr < 100       
    ),
    cte1 AS 
    (
        SELECT 
            PLAN_NR, period_nr, START_DATE
        FROM 
            rec_cte
        UNION ALL
        SELECT 
            PLAN_NR, period_nr, DATEADD(DAY, 1, EOMONTH(next_date, -1)) 
        FROM 
            rec_cte
        WHERE 
            MONTH(START_DATE) <> MONTH(next_date)
    ),
    cte2 AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY PLAN_NR ORDER BY START_DATE) rn
        FROM cte1
    )
    SELECT PLAN_NR, rn PERIOD_NR, START_DATE 
    FROM cte2
    WHERE rn <= 100
);

关联查询调整为通过OUTER APPLY调用函数,把TABLE_2的PLAN_NR作为参数传入:

SELECT 
    t2.PLAN_NR,
    t2.PERIOD_NR,
    a_p.START_DATE 
FROM TABLE_2 as t2 
OUTER APPLY dbo.fn_GET_ALL_PERIODS(t2.PLAN_NR) a_p 
WHERE t2.PERIOD_NR = a_p.PERIOD_NR

这个方案的执行效率和手动生成临时表的效果完全一致,且不需要修改原有业务的计算逻辑,无额外持久化成本。


方案2:在关联查询中强制指定哈希关联,避免嵌套循环触发重复递归

如果不想改造原有视图结构,可以在关联语句末尾加查询提示,强制优化器先全量计算一次视图结果,再通过哈希表匹配TABLE_2,避免每行触发一次递归计算:

SELECT 
    t2.PLAN_NR,
    t2.PERIOD_NR,
    a_p.START_DATE 
FROM TABLE_2 as t2 
LEFT OUTER JOIN ALL_PERIODS a_p 
    ON t2.PERIOD_NR = a_p.PERIOD_NR 
    AND t2.PLAN_NR = a_p.PLAN_NR
OPTION (HASH JOIN, MAXRECURSION 100)

适用场景:方案总数较少的情况,全量计算视图结果的开销可控。如果TABLE_1的方案数量超过上千个,这个方案的全量计算开销会明显上升。


方案3:彻底替换递归CTE为静态数字序列计算,根除递归性能问题

原递归CTE的核心作用是生成最多100个按7天间隔的周期序列,这类定长序列完全不需要递归——可以直接引用SQL Server内置的系统数字表(master..spt_values)生成1-100的固定序列,通过笛卡尔关联直接计算每个周期的日期,从根源上去掉递归逻辑:

CREATE OR ALTER VIEW ALL_PERIODS
AS
WITH num_seq AS ( -- 静态生成1-100的序列,无递归开销
    SELECT DISTINCT number -1 AS seq 
    FROM master..spt_values 
    WHERE type='P' AND number BETWEEN 1 AND 100
),
week_calc AS ( -- 基于静态序列直接计算每个周的基准日期
    SELECT
        t1.PLAN_NR,
        DATEADD(WEEK, seq, t1.START_DATE) AS base_week_start,
        DATEADD(WEEK, seq + 1, t1.START_DATE) AS base_week_next
    FROM TABLE_1 t1
    CROSS JOIN num_seq
),
period_segments AS (
    -- 未跨自然月的周期,直接取周起始日期为分段开始时间
    SELECT
        PLAN_NR,
        base_week_start AS START_DATE
    FROM week_calc
    WHERE MONTH(base_week_start) = MONTH(base_week_next)
    UNION ALL
    -- 跨自然月的周期,拆成两个分段:周起始日、下月初日期
    SELECT
        PLAN_NR,
        base_week_start AS START_DATE
    FROM week_calc
    WHERE MONTH(base_week_start) <> MONTH(base_week_next)
    UNION ALL
    SELECT
        PLAN_NR,
        DATEADD(DAY, 1, EOMONTH(base_week_start, 0)) AS START_DATE
    FROM week_calc
    WHERE MONTH(base_week_start) <> MONTH(base_week_next)
),
period_rn AS (
    SELECT 
        *, 
        ROW_NUMBER() OVER (PARTITION BY PLAN_NR ORDER BY START_DATE) AS rn
    FROM period_segments
)
SELECT PLAN_NR, rn AS PERIOD_NR, START_DATE
FROM period_rn
WHERE rn <= 100

改造后的视图没有递归逻辑,优化器可以像处理普通业务表一样做谓词下推、索引匹配、哈希关联,不会出现重复计算的问题,是长期维护最稳定的方案。


内容的提问来源于stack exchange,提问作者Peter S

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.10 20:45:05