如何将表传入地址字符串比较的表值函数以提升性能？

云原生阿岚

2026-7-20

批量地址对比函数改造与性能优化

问题背景

现有AddressCompare表值函数仅支持单条地址对比，需传入两组地址字段（各5个）及ID参数，返回单条匹配结果。处理10万条记录时需调用函数10万次，性能极低。需求将其改造为接收整表作为输入的批量处理函数，支持任意符合结构的输入表（包含两组地址字段：Street1/Street2/City/State/Zip，以及唯一ID字段），一次性输出所有记录的匹配状态。

现有实现的性能瓶颈

逐行调用开销：每条记录单独触发函数调用，累计10万次的上下文切换开销极大
低效的字符串处理：使用WHILE循环去除空格、替换街道后缀，属于逐行迭代操作，无法利用SQL的集合处理优势
多语句表值函数（MS TVF）：现有函数是多语句表值函数，SQL优化器无法对其进行查询计划优化，性能远低于内联表值函数（Inline TVF）

改造方案

1. 创建用户定义表类型（UDTT）

先定义符合输入结构的表类型，作为函数的参数：

CREATE TYPE dbo.AddressComparisonInput AS TABLE (
    Street1_Old VARCHAR(64),
    Street2_Old VARCHAR(64),
    City_Old VARCHAR(64),
    State_Old VARCHAR(32),
    Zip_Old VARCHAR(16),
    Street1_New VARCHAR(64),
    Street2_New VARCHAR(64),
    City_New VARCHAR(64),
    State_New VARCHAR(32),
    Zip_New VARCHAR(16),
    Join_Id INT PRIMARY KEY -- 主键提升批量处理性能
);

2. 重写为内联表值函数（Inline TVF）

内联表值函数会被SQL优化器当作视图展开，能充分利用索引和集合处理能力，同时重构所有字符串处理逻辑为集合式操作：

CREATE FUNCTION dbo.AddressCompare_Batch
(
    @InputData dbo.AddressComparisonInput READONLY
)
RETURNS TABLE
AS
RETURN
WITH StandardizedAddresses AS (
    -- 第一步：移除标点、统一大写、初步去空格
    SELECT
        Join_Id,
        -- 标准化旧地址
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Street1_Old, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS Street1_Old_Standard,
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Street2_Old, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS Street2_Old_Standard,
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(City_Old, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS City_Old_Standard,
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(State_Old, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS State_Old_Standard,
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Zip_Old, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS Zip_Old_Standard,
        -- 标准化新地址
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Street1_New, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS Street1_New_Standard,
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Street2_New, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS Street2_New_Standard,
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(City_New, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS City_New_Standard,
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(State_New, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS State_New_Standard,
        TRIM(UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Zip_New, '.', ''), '#', ''), '-', ' '), ',', ''), '''', ''), '  ', ' '))) AS Zip_New_Standard
    FROM @InputData
),
RemoveExtraSpaces AS (
    -- 第二步：彻底去除多余空格（用递归CTE替代WHILE循环）
    SELECT
        Join_Id,
        Street1_Old_Standard,
        Street2_Old_Standard,
        City_Old_Standard,
        State_Old_Standard,
        Zip_Old_Standard,
        Street1_New_Standard,
        Street2_New_Standard,
        City_New_Standard,
        State_New_Standard,
        Zip_New_Standard
    FROM StandardizedAddresses
    UNION ALL
    SELECT
        Join_Id,
        REPLACE(Street1_Old_Standard, '  ', ' '),
        REPLACE(Street2_Old_Standard, '  ', ' '),
        REPLACE(City_Old_Standard, '  ', ' '),
        State_Old_Standard,
        Zip_Old_Standard,
        REPLACE(Street1_New_Standard, '  ', ' '),
        REPLACE(Street2_New_Standard, '  ', ' '),
        REPLACE(City_New_Standard, '  ', ' '),
        State_New_Standard,
        Zip_New_Standard
    FROM RemoveExtraSpaces
    WHERE 
        LEN(Street1_Old_Standard) - LEN(REPLACE(Street1_Old_Standard, '  ', ' ')) > 0
        OR LEN(Street2_Old_Standard) - LEN(REPLACE(Street2_Old_Standard, '  ', ' ')) > 0
        OR LEN(City_Old_Standard) - LEN(REPLACE(City_Old_Standard, '  ', ' ')) > 0
        OR LEN(Street1_New_Standard) - LEN(REPLACE(Street1_New_Standard, '  ', ' ')) > 0
        OR LEN(Street2_New_Standard) - LEN(REPLACE(Street2_New_Standard, '  ', ' ')) > 0
        OR LEN(City_New_Standard) - LEN(REPLACE(City_New_Standard, '  ', ' ')) > 0
),
FinalStandardized AS (
    -- 取最终去空格后的结果（递归到无多余空格为止）
    SELECT
        Join_Id,
        TRIM(Street1_Old_Standard) AS Street1_Old_Final,
        TRIM(Street2_Old_Standard) AS Street2_Old_Final,
        TRIM(City_Old_Standard) AS City_Old_Final,
        State_Old_Standard AS State_Old_Final,
        Zip_Old_Standard AS Zip_Old_Final,
        TRIM(Street1_New_Standard) AS Street1_New_Final,
        TRIM(Street2_New_Standard) AS Street2_New_Final,
        TRIM(City_New_Standard) AS City_New_Final,
        State_New_Standard AS State_New_Final,
        Zip_New_Standard AS Zip_New_Final
    FROM RemoveExtraSpaces
    WHERE 
        LEN(Street1_Old_Standard) = LEN(REPLACE(Street1_Old_Standard, '  ', ' '))
        AND LEN(Street2_Old_Standard) = LEN(REPLACE(Street2_Old_Standard, '  ', ' '))
        AND LEN(City_Old_Standard) = LEN(REPLACE(City_Old_Standard, '  ', ' '))
        AND LEN(Street1_New_Standard) = LEN(REPLACE(Street1_New_Standard, '  ', ' '))
        AND LEN(Street2_New_Standard) = LEN(REPLACE(Street2_New_Standard, '  ', ' '))
        AND LEN(City_New_Standard) = LEN(REPLACE(City_New_Standard, '  ', ' '))
),
ExpandedSuffixes AS (
    -- 第三步：替换街道后缀缩写（用CROSS APPLY集合式替换替代WHILE循环）
    SELECT
        fs.Join_Id,
        -- 替换旧地址后缀
        TRIM(REPLACE(' ' + fs.Street1_Old_Final + ' ', ' ' + UPPER(s.ctw_shortdescr) + ' ', ' ' + UPPER(s.ctw_description) + ' ')) AS Street1_Old_Expanded,
        CASE WHEN fs.Street2_Old_Final <> '' THEN TRIM(REPLACE(' ' + fs.Street2_Old_Final + ' ', ' ' + UPPER(s.ctw_shortdescr) + ' ', ' ' + UPPER(s.ctw_description) + ' ')) ELSE fs.Street2_Old_Final END AS Street2_Old_Expanded,
        TRIM(REPLACE(' ' + fs.City_Old_Final + ' ', ' ' + UPPER(s.ctw_shortdescr) + ' ', ' ' + UPPER(s.ctw_description) + ' ')) AS City_Old_Expanded,
        fs.State_Old_Final,
        fs.Zip_Old_Final,
        -- 替换新地址后缀
        TRIM(REPLACE(' ' + fs.Street1_New_Final + ' ', ' ' + UPPER(s.ctw_shortdescr) + ' ', ' ' + UPPER(s.ctw_description) + ' ')) AS Street1_New_Expanded,
        CASE WHEN fs.Street2_New_Final <> '' THEN TRIM(REPLACE(' ' + fs.Street2_New_Final + ' ', ' ' + UPPER(s.ctw_shortdescr) + ' ', ' ' + UPPER(s.ctw_description) + ' ')) ELSE fs.Street2_New_Final END AS Street2_New_Expanded,
        TRIM(REPLACE(' ' + fs.City_New_Final + ' ', ' ' + UPPER(s.ctw_shortdescr) + ' ', ' ' + UPPER(s.ctw_description) + ' ')) AS City_New_Expanded,
        fs.State_New_Final,
        fs.Zip_New_Final
    FROM FinalStandardized fs
    CROSS JOIN (
        SELECT ctw_shortdescr, ctw_description
        FROM Codes
        WHERE CTW_Type = 'Street Suffix'
          AND UPPER(CTW_Reference) = 'ADDRESSCOMPARE'
    ) s
),
FinalAddresses AS (
    -- 取最终后缀替换后的结果（去重）
    SELECT DISTINCT
        Join_Id,
        Street1_Old_Expanded,
        Street2_Old_Expanded,
        City_Old_Expanded,
        State_Old_Final AS State_Old_Expanded,
        Zip_Old_Final AS Zip_Old_Expanded,
        Street1_New_Expanded,
        Street2_New_Expanded,
        City_New_Expanded,
        State_New_Final AS State_New_Expanded,
        Zip_New_Final AS Zip_New_Expanded
    FROM ExpandedSuffixes
)
-- 第四步：匹配逻辑
SELECT
    CAST(IIF(fa.Street1_Old_Expanded = fa.Street1_New_Expanded, 1, 0) AS BIT) AS Street1_Match,
    CAST(IIF(fa.Street2_Old_Expanded = fa.Street2_New_Expanded, 1, 0) AS BIT) AS Street2_Match,
    CAST(IIF(fa.City_Old_Expanded = fa.City_New_Expanded, 1, 0) AS BIT) AS City_Match,
    CAST(IIF(fa.State_Old_Expanded = fa.State_New_Expanded, 1, 0) AS BIT) AS State_Match,
    CAST(IIF(fa.Zip_Old_Expanded = fa.Zip_New_Expanded, 1, 0) AS BIT) AS Zip_Match,
    CAST(IIF(
        fa.Street1_Old_Expanded = fa.Street1_New_Expanded
        AND fa.Street2_Old_Expanded = fa.Street2_New_Expanded
        AND fa.City_Old_Expanded = fa.City_New_Expanded
        AND fa.State_Old_Expanded = fa.State_New_Expanded
        AND fa.Zip_Old_Expanded = fa.Zip_New_Expanded,
        1, 0
    ) AS BIT) AS Total_Match,
    fa.Join_Id
FROM FinalAddresses fa;

3. 调用示例

-- 将#bkas数据传入批量函数，生成匹配结果
SELECT * 
INTO #address_match_data
FROM dbo.AddressCompare_Batch(
    (SELECT 
        BKA_Street1 AS Street1_Old,
        BKA_Street2 AS Street2_Old,
        BKA_City AS City_Old,
        BKA_State AS State_Old,
        BKA_Zip AS Zip_Old,
        BKA_Street1_Current AS Street1_New,
        BKA_Street2_Current AS Street2_New,
        BKA_City_Current AS City_New,
        BKA_State_Current AS State_New,
        BKA_Zip_Current AS Zip_New,
        BKA_Id AS Join_Id
     FROM #bkas)
);

-- 原UPDATE逻辑改造为批量更新
UPDATE b
SET Address_Match_Flag = 1
FROM #bkas b
JOIN #address_match_data amd ON b.BKA_Id = amd.Join_Id
WHERE amd.Total_Match = 1;

额外性能优化建议

缓存街道后缀数据：将Codes表中用于后缀替换的数据持久化为索引视图或单独的小表，避免每次函数调用都查询大表
字符串处理优化：若SQL Server版本支持（2017+），可使用TRANSLATE函数简化标点移除逻辑，替代多层REPLACE
CLR函数加速：复杂字符串处理（如多次空格移除、多后缀替换）可考虑用CLR函数实现，性能远优于T-SQL
输入表索引：确保传入的表参数（临时表或用户定义表类型）的Join_Id字段有主键或索引，提升关联性能
避免递归CTE：若SQL Server版本支持2022+，可使用STRING_AGG结合拆分函数实现更高效的去空格操作，替代递归CTE

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

如何将表传入地址字符串比较的表值函数以提升性能？

问题背景 #

现有实现的性能瓶颈 #

改造方案 #

1. 创建用户定义表类型（UDTT） #

2. 重写为内联表值函数（Inline TVF） #

3. 调用示例 #

额外性能优化建议 #