基于两列范围重复行：现有SQL查询的效率优化咨询

嘿，你的这个物料号范围拆分需求很常见，先给你点个赞——已经写出了能得到预期结果的递归CTE！不过针对生产环境7万行拆分到12万行的量级，确实得琢磨下效率优化的事儿，我来给你梳理几个靠谱的方向和替代方案：

一、先聊聊原递归CTE的潜在瓶颈 #

递归CTE的思路没问题，但SQL Server里的递归是迭代式处理的，当单条数据的差值接近150时，递归层数就有150层，7万行累加起来的迭代次数会很可观，这可能会成为性能瓶颈。而且原查询里的多个CROSS APPLY虽然逻辑清晰，但也增加了执行计划里的操作步骤，咱们可以先简化这部分。

二、优化方向1：把递归CTE换成Tally Table（数字表） #

这是处理“按范围生成行”场景的经典高效方案——用一个预先生成的连续数字表，通过集合关联的方式生成所有需要的物料号，完全替代递归的迭代逻辑，性能提升会很明显。

具体实现步骤： #

1. 生成Tally Table：先创建一个包含足够连续数字的临时表（覆盖最大差值150的情况，比如生成1到200的数字）：

DROP TABLE IF EXISTS #Tally;
-- 利用系统表快速生成连续数字，TOP 200足够覆盖差值150的需求
SELECT TOP 200 
    n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
INTO #Tally 
FROM sys.all_columns ac1 
CROSS JOIN sys.all_columns ac2;

2. 改写主查询：先一次性拆分物料号的前缀、起始数字、结束数字，再关联Tally Table生成所有行：

WITH MaterialParts AS (
    SELECT 
        Materialno_start,
        Materialno_end,
        name,
        mtype,
        noofstock,
        -- 提取物料号的前缀部分（连字符之前的内容）
        base = SUBSTRING(Materialno_start, 1, CHARINDEX('-', Materialno_start) - 1),
        -- 提取起始数字并转成INT
        start_num = CONVERT(INT, SUBSTRING(Materialno_start, CHARINDEX('-', Materialno_start) + 1, LEN(Materialno_start))),
        -- 提取结束数字（如果Materialno_end为空则用起始数字）并转成INT
        end_num = CONVERT(INT, SUBSTRING(COALESCE(Materialno_end, Materialno_start), CHARINDEX('-', Materialno_start) + 1, LEN(Materialno_start))),
        -- 计算需要生成的行数（差值+1，因为包含起始和结束）
        row_count = CONVERT(INT, SUBSTRING(COALESCE(Materialno_end, Materialno_start), CHARINDEX('-', Materialno_start) + 1, LEN(Materialno_start))) 
                   - CONVERT(INT, SUBSTRING(Materialno_start, CHARINDEX('-', Materialno_start) + 1, LEN(Materialno_start))) + 1
    FROM data
)
SELECT 
    mp.Materialno_start,
    mp.Materialno_end,
    -- 拼接前缀和增量后的数字得到完整物料号
    MaterialNo = mp.base + CONVERT(VARCHAR(30), mp.start_num + t.n - 1),
    mp.name,
    mp.mtype,
    mp.noofstock
FROM MaterialParts mp
-- 只关联需要的数字行数
JOIN #Tally t ON t.n <= mp.row_count
ORDER BY mp.Materialno_start, t.n;

这个方案的优势是集合式操作，避免了递归的迭代开销，尤其是当数据量和差值较大时，性能会比递归CTE好很多。

三、优化方向2：简化原查询的CROSS APPLY逻辑 #

如果暂时不想换方案，也可以先合并原查询里的多个CROSS APPLY，减少不必要的重复计算：

WITH cte AS (
 SELECT 
    Materialno_start,Materialno_end,name,mtype,noofstock,
    starts.st AS ns, ends.ed AS nd, diff.s AS d, i = 1,
    n = convert(VARCHAR(30), starts.st),
    bs = SUBSTRING(Materialno_start, 1, s.hyp - 1)
 FROM data
 -- 合并多个CROSS APPLY，一次性计算关键参数
 CROSS APPLY (
    SELECT 
        hyp = CHARINDEX('-', Materialno_start),
        mn = LEN(Materialno_start)
 ) s
 CROSS APPLY (
    SELECT 
        st = CONVERT(INT, SUBSTRING(Materialno_start, s.hyp + 1, s.mn)),
        ed = CONVERT(INT, SUBSTRING(COALESCE(Materialno_end, Materialno_start), s.hyp + 1, s.mn))
 ) parts
 CROSS APPLY (VALUES (parts.ed - parts.st)) diff(s)
 UNION ALL
 SELECT 
    Materialno_start,Materialno_end,name,mtype,noofstock,
    ns,nd,d,i = i + 1,
    n = convert(VARCHAR(30), CONVERT(INT, n) + 1),
    bs
 FROM cte
 WHERE i <= d
 )
 SELECT 
    Materialno_start,
    Materialno_end,
    bs + n AS MaterialNo,
    Name,
    mtype,
    noofstock
 FROM cte
 ORDER BY 1;

这里把原查询中分散的leng、s、n、starts、ends等CROSS APPLY合并成了两个，减少了执行计划中的操作步骤，能小幅提升效率。

四、优化方向3：给原表加覆盖索引 #

如果原表data上没有合适的索引，可以创建一个覆盖索引，让查询直接从索引获取所有需要的数据，避免回表操作：

CREATE NONCLUSTERED INDEX IX_Data_MaterialRange 
ON data (Materialno_start, Materialno_end) 
INCLUDE (name, mtype, noofstock);

这个索引会包含查询需要的所有列，能显著减少IO开销，提升查询速度。

五、测试建议 #

因为没法访问生产环境，你可以模拟7万行测试数据（其中包含一些差值为150的行），然后开启统计信息对比两种方案的性能：

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- 运行原递归CTE查询
-- 运行Tally Table方案查询

对比两者的CPU时间、逻辑读次数，就能直观看到性能差异。

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