SQL字符串替换优化：将游标循环改为集合式实现提升性能

问题描述 #

现有表AcctStrings，包含IL和CL两个字符串列，需求是逐字符对比两列，将CL列中所有?替换为IL列相同位置的字符。示例如下：

原实现采用游标嵌套WHILE循环逐字符拼接更新，处理10万+条记录时性能极差，需改为集合式实现提升效率。原代码如下：

declare CharC insensitive cursor for 
  select ID, IL, CL
  from AcctStrings
  where CL like '%?%' 

open CharC
fetch next from CharC into @ID, @IL, @CL
while @@fetch_status = 0 begin

    set @NewCL = ''
    set @i = 1 

    while @i <= 25 begin
      set @TestChar = substring(@CL,@i,1)
      set @OtherChar = substring(@IL,@i,1)

      if (@TestChar = '?') begin
        set @NewCL = @NewCL + @OtherChar 
      end 
      else 
        set @NewCL = @NewCL + @TestChar

      set @i = @i + 1
    end

    update AcctStrings
      set CL = @NewCL
    where ID = @ID
  end

  fetch next from CharC into @ID, @IL, @CL
end
deallocate CharC

集合式解决方案 #

方法1：SQL Server 2017+ 版本（使用STRING_AGG） #

利用递归CTE生成1到25的字符位置序列，通过集合查询逐字符判断并拼接结果，批量更新目标列：

WITH Nums AS (
    SELECT 1 AS Pos
    UNION ALL
    SELECT Pos + 1 FROM Nums WHERE Pos < 25
)
UPDATE a
SET CL = (
    SELECT STRING_AGG(
        CASE 
            WHEN SUBSTRING(a.CL, n.Pos, 1) = '?' THEN SUBSTRING(a.IL, n.Pos, 1) 
            ELSE SUBSTRING(a.CL, n.Pos, 1) 
        END,
        ''
    ) WITHIN GROUP (ORDER BY n.Pos)
    FROM Nums n
)
FROM AcctStrings a
WHERE a.CL LIKE '%?%';

方法2：兼容SQL Server 2008及以上版本（使用FOR XML PATH） #

如果使用较低版本SQL Server，可通过FOR XML PATH实现字符串拼接：

WITH Nums AS (
    SELECT 1 AS Pos
    UNION ALL
    SELECT Pos + 1 FROM Nums WHERE Pos < 25
)
UPDATE a
SET CL = (
    SELECT 
        CASE 
            WHEN SUBSTRING(a.CL, n.Pos, 1) = '?' THEN SUBSTRING(a.IL, n.Pos, 1) 
            ELSE SUBSTRING(a.CL, n.Pos, 1) 
        END
    FROM Nums n
    ORDER BY n.Pos
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(25)')
FROM AcctStrings a
WHERE a.CL LIKE '%?%';

性能优化说明 #

• 原游标方案的问题：逐行循环+逐字符拼接字符串，每次字符串拼接都会生成新对象，加上单条更新的IO开销，处理大量数据时性能极低。
• 集合式方案优势：通过批量处理替代逐行循环，利用数据库引擎的集合优化能力（如并行执行、索引利用），大幅减少IO和CPU开销，10万+数据的处理效率会有数量级的提升。

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