使用collate SQL_Latin1_General_CP1_CI_AS出现性能问题如何解决

问题根因 #

当前查询性能差来自两个叠加的负面影响：

• LIKE '%' + tpd.ShowToken + '%'属于前导通配符的模糊匹配逻辑，无法命中DetailRecord字段上的常规索引，会触发CheckRecords全表扫描
• 逐行匹配时强制执行排序规则转换，额外增加了大量CPU计算开销，进一步放大了全表扫描的耗时

#tmpConverted表示例：

可落地优化方案 #

方案1：统一临时表排序规则，避免运行时转换 #

排序规则冲突的本质是临时表默认继承实例/库的排序规则，和CheckRecords表字段的排序规则不一致。可以在建临时表时直接指定和CheckRecords.DetailRecord一致的排序规则，无需在查询时额外加COLLATE声明：

CREATE TABLE #tmpConverted
(
    PolicyNo NVarchar(10) COLLATE Latin1_General_CS_AS,
    ShowToken NVarChar(16) COLLATE Latin1_General_CS_AS
)

调整后查询可直接移除COLLATE声明，消除运行时的排序规则转换开销：

SELECT *
FROM #tmpConverted tpd
WHERE EXISTS (SELECT 1
              FROM [dbo].CheckRecords cr
              WHERE cr.DetailRecord LIKE '%' + tpd.ShowToken + '%')

方案2：优化模糊匹配逻辑，降低扫描开销 #

如果CheckRecords表数据量较大，仅消除排序规则转换还是无法满足性能要求，可根据业务场景选择对应优化方式：

• 若ShowToken是DetailRecord中的固定格式子串，例如固定在字段起始位置、或用固定分隔符分割，可将匹配逻辑调整为非前导通配符形式，例如LIKE 'xxx%'，即可命中DetailRecord上的普通索引
• 若必须使用全字段模糊匹配，可给CheckRecords表的DetailRecord字段建立全文索引，使用CONTAINS关键字替代LIKE做匹配，性能可提升数倍到数十倍：

-- 全文索引创建完成后查询写法
SELECT *
FROM #tmpConverted tpd
WHERE EXISTS (SELECT 1
              FROM [dbo].CheckRecords cr
              WHERE CONTAINS(cr.DetailRecord, tpd.ShowToken))

方案3：小表驱动大表减少匹配次数 #

如果#tmpConverted表的数据量远小于CheckRecords表，可先将所有ShowToken拼接成统一匹配串，先做一次粗过滤减少后续逐行匹配的次数：

DECLARE @MatchPattern NVARCHAR(MAX)
SELECT @MatchPattern = CONCAT('%', STRING_AGG(ShowToken, '%|%'), '%') FROM #tmpConverted

SELECT *
FROM #tmpConverted tpd
WHERE EXISTS (SELECT 1
              FROM [dbo].CheckRecords cr
              WHERE cr.DetailRecord LIKE @MatchPattern
              AND cr.DetailRecord LIKE '%' + tpd.ShowToken + '%')

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