SQL Server如何用纯查询替代函数实现编码列相似度对比

原方案核心问题 #

1. 标量自定义函数执行开销极高：400万次调用中，每次都要重复拆分两个字符串、创建表变量、执行关联查询，完全没有复用拆分结果，运算量指数级放大。
2. 结果正确性无法保证：SQL Server 内置的STRING_SPLIT函数不承诺返回值的顺序和原字符串一致，你基于自增ID匹配位置的逻辑本身就是错误的，计算出的相似度不可靠。
3. TempDB暴涨占满C盘：大量的表变量创建、临时关联运算以及插入前的排序操作都会消耗TempDB资源，默认配置下TempDB存放在C盘，运算量过大就会直接占满系统盘。

最优改造方案 #

第一步：预先拆分Codes为结构化存储，仅执行一次

把逗号分隔的Code串一次性拆分为按位置存储的结构化数据，避免每次对比重复拆分：

-- 1. 先创建位置辅助表，一次性生成1-1000的连续数字（覆盖你最大的Code长度即可）
WITH Recur_Num AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Recur_Num WHERE n < 1000
)
SELECT n INTO Num_Pos FROM Recur_Num OPTION (MAXRECURSION 1000)

-- 2. 创建拆分后的结构化存储表
CREATE TABLE TargetsComp_Split (
    RowCnt INT,
    Lvl INT,
    TargetID INT,
    Pos INT,
    Val TINYINT,
    PRIMARY KEY (RowCnt, Lvl, TargetID, Pos)
)

-- 3. 一次性拆分所有Code存入结构化表，保证位置顺序正确
INSERT INTO TargetsComp_Split (RowCnt, Lvl, TargetID, Pos, Val)
SELECT
    t.RowCnt,
    t.Lvl,
    t.TargetID,
    n.n AS Pos,
    CAST(SUBSTRING(',' + t.Codes + ',', n.n + 1, CHARINDEX(',', ',' + t.Codes + ',', n.n + 1) - n.n - 1) AS TINYINT) AS Val
FROM TargetsComp t
INNER JOIN Num_Pos n
    ON n.n < LEN(',' + t.Codes + ',')
    AND SUBSTRING(',' + t.Codes + ',', n.n, 1) = ','

第二步：批量计算所有组合的相似度，完全去掉自定义函数

直接用结构化表关联计算，一次批量完成所有对比，开销比原方案低两个数量级：

INSERT INTO TargetFilled (RowCnt, Lvl, a_TargetID, b_TargetID, a_codes, b_codes, sim)
SELECT
    a.RowCnt,
    a.Lvl,
    a.TargetID AS a_TargetID,
    b.TargetID AS b_TargetID,
    ta.Codes AS a_codes,
    tb.Codes AS b_codes,
    1.0 * SUM(CASE WHEN a.Val = b.Val THEN 1 ELSE 0 END) / COUNT(*) AS sim
FROM TargetsComp_Split a
INNER JOIN TargetsComp_Split b
    ON a.RowCnt = b.RowCnt
    AND a.Lvl = b.Lvl -- 原逻辑漏了同层级匹配，确认你的业务需要就保留，不需要可以删除
    AND a.TargetID < b.TargetID
    AND a.Pos = b.Pos
-- 如果你不需要在TargetFilled里存原始Code串，下面两个关联可以直接去掉，速度再提升一倍
INNER JOIN TargetsComp ta ON a.RowCnt = ta.RowCnt AND a.Lvl = ta.Lvl AND a.TargetID = ta.TargetID
INNER JOIN TargetsComp tb ON b.RowCnt = tb.RowCnt AND b.Lvl = tb.Lvl AND b.TargetID = tb.TargetID
GROUP BY a.RowCnt, a.Lvl, a.TargetID, b.TargetID, ta.Codes, tb.Codes
-- 如果插入不需要排序，直接去掉下面的ORDER BY，能大幅减少TempDB占用
ORDER BY a.RowCnt, a.Lvl, sim DESC

可选极致优化方案 #

如果可以修改原表的存储结构，速度还能再提升一个数量级：

1. 把逗号分隔的Code串改为连续的01字符串，再转为BINARY类型存储，328位仅需要41字节的存储空间，远小于原来的字符串。
2. 对比时直接用位异或运算XOR，统计异或结果中1的个数就是差异位数，相似度计算可以直接在内存中完成，不需要关联任何表。

附加建议 #

将数据库的TempDB迁移到非系统盘，并且预分配至少10G的初始空间，避免自动增长带来的性能损耗和系统盘占满问题。

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