如何优化SQL Server的Remove函数以提升大数据量处理效率？

我在student表的comment列中为加密数据添加了随机干扰字符，查询时需要移除每第4个字符来清理数据。目前已实现标量值函数Remove，功能正常但处理100万条数据耗时约40秒。尝试改用内联表值函数时遇到报错：

语句已终止。在语句完成前已耗尽最大递归级别 100。

添加OPTION(MAXRECURSION 0);后耗时4分钟仍无结果返回。

查询数据示例 #

"!,T%OMAZTAA#:R$Tt$TNM)CRJEI6ZN/=#8AoABE1NAYABE-PI!0VAAj#G)sV)C]IZN/`)V

现有标量值函数代码 #

CREATE FUNCTION [dbo].[Remove] (@encrypt VARCHAR(255))
    RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @word VARCHAR(200) = '';

    WITH Numbers AS (
        SELECT TOP (LEN(@encrypt)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
        FROM master.dbo.spt_values
    )

    SELECT @word += SUBSTRING(@encrypt, N, 1)
    FROM Numbers
    WHERE (N - 1) % 4 <> 0;

    RETURN @word;
END;

标量函数查询语句 #

SELECT Id, COMMENT 
FROM student 
WHERE dbo.Remove(COMMENT) like '%N/#8AABEN`AABEPI!VAA#G)V)CIZN`)V';

内联表值函数代码（存在性能问题） #

CREATE FUNCTION [dbo].[Remove] (@StringData VARCHAR(255))
RETURNS TABLE
AS RETURN
(
    WITH Numbers AS (
        SELECT 1 AS N
        UNION ALL
        SELECT N + 1
        FROM Numbers
        WHERE N < LEN(@StringData)
    )
    SELECT
        word = STRING_AGG(SUBSTRING(@StringData, Numbers.N, 1), '') WITHIN GROUP (ORDER BY Numbers.N)
    FROM Numbers
    WHERE (Numbers.N - 1) % 4 <> 0
);

内联表值函数查询语句 #

SELECT Id, COMMENT
FROM student
CROSS APPLY dbo.[Remove](COMMENT) AS r
WHERE r.word LIKE '%N/#8AABENAABEPI!VAA#G)V)CIZN)V';

使用环境 #

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 2019年9月24日 13:48:23 版权所有 (C) 2019 Microsoft Corporation 开发者版(64位) 运行于 Windows 10 家庭版 10.0 (内部版本 22621: )

1. 替换递归CTE为非递归数字序列生成 #

内联表值函数中的递归CTE生成数字序列效率极低，尤其是处理较长字符串时。改用非递归方式生成数字，利用系统表即可满足需求：

CREATE FUNCTION [dbo].[Remove] (@StringData VARCHAR(255))
RETURNS TABLE
AS RETURN
(
    WITH Numbers AS (
        SELECT TOP (LEN(@StringData)) 
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
        FROM master.dbo.spt_values t1
        CROSS JOIN master.dbo.spt_values t2 -- 确保生成足够多的数字，远超255的上限
    )
    SELECT
        word = STRING_AGG(SUBSTRING(@StringData, N, 1), '') WITHIN GROUP (ORDER BY N)
    FROM Numbers
    WHERE (N - 1) % 4 <> 0
);

2. 基于块处理的高效标量函数实现 #

针对每4个字符移除1个的固定规则，可以直接按块截取拼接，避免逐字符遍历，性能提升更显著：

CREATE FUNCTION [dbo].[Remove] (@encrypt VARCHAR(255))
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @result VARCHAR(200) = '',
            @len INT = LEN(@encrypt),
            @i INT = 1;

    -- 每4个字符为一块，截取前3个字符拼接
    WHILE @i <= @len
    BEGIN
        SET @result += SUBSTRING(@encrypt, @i, CASE WHEN @i + 2 > @len THEN @len - @i + 1 ELSE 3 END);
        SET @i += 4;
    END

    RETURN @result;
END;

3. 查询层面优化建议 #

• 如果这类查询频率较高，建议预先计算并存储清理后的数据：新增一列存储处理后的结果，为该列创建索引，彻底避免查询时实时执行函数。
• 标量函数在WHERE子句中使用会导致全表扫描，改用CROSS APPLY结合优化后的内联表值函数，可配合合适的索引提升性能；优先考虑预计算方案，这是性能最优的选择。

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