基于游标与集合方案对比：慢存储过程优化及字段匹配需求

嘿，这个场景我之前优化过类似的，游标逐行比对在主表50万条的量级下确实会慢得让人头疼，换成集合式方案能把性能提上去一大截。给你几个实用的思路，你可以根据自己的数据库环境和数据分布选最合适的：

这是最直观的方法，通过逐个字段比对并累加匹配次数，最后筛选出匹配数≥6的记录。代码写起来直接，数据库优化器也容易处理：

SELECT 
    t.TransientID,
    m.MasterID,
    -- 计算20个字段的匹配总数
    (CASE WHEN t.Col1 = m.Col1 THEN 1 ELSE 0 END) +
    (CASE WHEN t.Col2 = m.Col2 THEN 1 ELSE 0 END) +
    (CASE WHEN t.Col3 = m.Col3 THEN 1 ELSE 0 END) +
    -- ... 依次写完Col4到Col19
    (CASE WHEN t.Col20 = m.Col20 THEN 1 ELSE 0 END) AS TotalMatches
FROM #Transient t
-- 如果有能提前过滤的字段（比如某个字段选择性很高），优先用JOIN替代CROSS JOIN
JOIN MasterTable m 
    ON t.Col1 = m.Col1 -- 先通过高选择性字段缩小主表范围
WHERE 
    -- 这里重复计算匹配数，或者用子查询/CTE先算好再筛选
    (CASE WHEN t.Col1 = m.Col1 THEN 1 ELSE 0 END) +
    (CASE WHEN t.Col2 = m.Col2 THEN 1 ELSE 0 END) +
    -- ... 同上到Col20
    (CASE WHEN t.Col20 = m.Col20 THEN 1 ELSE 0 END) >= 6

优化点：如果某个字段的匹配率低、选择性高（比如Col1的不同值很多），先用这个字段做JOIN条件，能直接把主表的匹配范围从50万条缩小到几百/几千条，后续计算压力会小很多。

如果20个字段写CASE太繁琐，用UNPIVOT把列转成行，再通过分组计数来统计匹配数，代码更简洁易维护：

WITH TransientFields AS (
    SELECT 
        TransientID,
        FieldName,
        FieldValue
    FROM #Transient
    UNPIVOT (
        FieldValue FOR FieldName IN (Col1, Col2, Col3, ..., Col20)
    ) AS up
),
MasterFields AS (
    SELECT 
        MasterID,
        FieldName,
        FieldValue
    FROM MasterTable
    UNPIVOT (
        FieldValue FOR FieldName IN (Col1, Col2, Col3, ..., Col20)
    ) AS up
)
SELECT 
    tf.TransientID,
    mf.MasterID,
    COUNT(*) AS TotalMatches
FROM TransientFields tf
JOIN MasterFields mf 
    ON tf.FieldName = mf.FieldName 
    AND tf.FieldValue = mf.FieldValue
GROUP BY tf.TransientID, mf.MasterID
HAVING COUNT(*) >= 6

注意：这个方法会把主表转成50万×20=1亿行的中间结果，虽然数据库能处理，但如果你的服务器资源有限，可能不如方案1快。不过胜在字段增减时只需修改UNPIVOT里的字段列表，不用改一堆CASE。

• 临时表索引：给#Transient的常用比对字段（比如那些选择性高的）建非聚集索引，能加快JOIN时的匹配速度。
• 主表索引：如果这个比对是高频操作，可以给主表的几个高选择性字段建组合索引（比如CREATE NONCLUSTERED INDEX IX_Master_Col1_Col2 ON MasterTable(Col1, Col2)），帮助数据库快速定位潜在匹配行。
• 大小写/排序规则：因为是varchar类型，要确保比对时的大小写和排序规则符合预期，必要时用COLLATE子句统一（比如t.Col1 = m.Col1 COLLATE SQL_Latin1_General_CP1_CI_AS）。
• 分批处理：如果临时表300条还是觉得运算量太大，可以把临时表分成几批（比如每50条一批），分批执行比对，避免一次性占用太多资源。

你可以先拿小数据量测试这两个方案的执行计划，看哪个更适合你的数据分布～

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