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
相关产品推荐
相关产品推荐

