如何优化SQL自定义Remove函数以提升百万级数据查询效率？

当前场景：执行以下SQL查询时，在100万条数据上耗时约40秒，需要优化自定义的Remove函数或提供等效实现以提升性能。

原查询语句 #

SELECT Id, COMMENT 
FROM student 
WHERE dbo.Remove(COMMENT) like '%N8AABENAABEPIVAA#GVCIZNV';

原Remove函数定义 #

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

    WITH Numbers AS 
    (
        SELECT TOP (LEN(@StringData )) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
        FROM master.dbo.spt_values
    )
    SELECT @word += SUBSTRING(@StringData, N, 1)
    FROM Numbers
    WHERE (N - 1) % 4 <> 0;

    RETURN @word;
END

函数逻辑说明 #

该函数的作用是移除字符串中第1、5、9、13...（即位置为4k+1，k≥0）的字符，保留其余所有字符。

优化方案 #

方案一：改用内联表值函数（iTVF） #

标量函数在逐行处理时会产生较大的性能开销，内联表值函数能被查询优化器更好地优化，减少函数调用的额外消耗。

优化后的函数

CREATE FUNCTION [dbo].[Remove_ITVF]
    (@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
)
SELECT STRING_AGG(SUBSTRING(@StringData, N, 1), '') AS ProcessedString
FROM Numbers
WHERE (N - 1) % 4 <> 0;

对应的查询语句

SELECT s.Id, s.COMMENT
FROM student s
CROSS APPLY dbo.Remove_ITVF(s.COMMENT) r
WHERE r.ProcessedString LIKE '%N8AABENAABEPIVAA#GVCIZNV';

方案二：批量处理的标量函数 #

原函数通过逐字符拼接生成结果，效率低下。改用批量处理每4个字符的方式，大幅减少循环/迭代次数。

优化后的函数

CREATE FUNCTION [dbo].[Remove_Fast]
    (@StringData VARCHAR(255))
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @Result VARCHAR(200) = '';
    DECLARE @Len INT = LEN(@StringData);
    DECLARE @Pos INT = 1;

    -- 批量处理每4个字符：跳过第1个，取后面3个
    WHILE @Pos <= @Len
    BEGIN
        SET @Result += SUBSTRING(@StringData, @Pos + 1, 3);
        SET @Pos += 4;
    END

    -- 处理末尾不足4个字符的情况：跳过第一个，取剩余部分
    IF @Len > @Pos - 4
    BEGIN
        SET @Result += SUBSTRING(@StringData, @Pos, @Len - @Pos + 1);
    END

    RETURN @Result;
END

对应的查询语句

SELECT Id, COMMENT 
FROM student 
WHERE dbo.Remove_Fast(COMMENT) like '%N8AABENAABEPIVAA#GVCIZNV';

方案三：持久化计算列+索引（最优解） #

如果该查询是高频执行的场景，预先计算处理后的字段并创建索引，彻底避免查询时的函数计算开销。

步骤1：添加持久化计算列

ALTER TABLE student
ADD ProcessedComment AS dbo.Remove_Fast(COMMENT) PERSISTED;

步骤2：创建非聚集索引

CREATE NONCLUSTERED INDEX IX_Student_ProcessedComment
ON student(ProcessedComment)
INCLUDE (Id, COMMENT);

对应的查询语句

SELECT Id, COMMENT
FROM student
WHERE ProcessedComment LIKE '%N8AABENAABEPIVAA#GVCIZNV';

方案对比 #

• 内联表值函数比原标量函数性能提升明显，适合临时优化场景；
• 批量处理的标量函数比原函数效率高，逻辑简单易维护；
• 持久化计算列+索引是最优方案，查询性能最优，适合高频查询的业务场景。

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