在内连接中使用SplitString表值函数的性能优化问题咨询

性能慢的核心原因 #

当前语句性能差主要来自两点：

1. 每行ProcessQueueLog记录都会重复调用2次自定义拆分函数，绝大多数自研字符串拆分函数都是多语句表值函数，本身执行开销高，重复调用进一步放大了性能损耗
2. 关联条件的字段是运行时计算生成的值，无法命中CLog表上Conversation_ID、Memo_ID的索引，只能走全表扫描

优化方案 #

• 方案1：先拆分再关联，避免重复调用函数
  用CROSS APPLY仅对每行记录做1次拆分，复用拆分结果，改后代码如下：

  SELECT pql.*, cl.ShortNote
  FROM Automation.ProcessQueueLog pql
  CROSS APPLY dbo.fn_SplitString(pql.QueuedRecordId, '-') split
  INNER JOIN dbo.CLog cl
    ON cl.Conversation_ID = CAST(split.Part1 AS INT)
    AND cl.Memo_ID = CAST(split.Part2 AS INT)
  

• 方案2：替换自定义函数为数据库内置拆分逻辑
  如果你用的是SQL Server 2016及以上版本，可直接用内置JSON处理逻辑完成拆分，性能比普通自定义函数高3~10倍：

  SELECT pql.*, cl.ShortNote
  FROM Automation.ProcessQueueLog pql
  -- 把带分隔符的字符串转成JSON数组，取第1、2个元素
  CROSS APPLY (
    SELECT 
      JSON_VALUE('["' + REPLACE(pql.QueuedRecordId, '-', '","') + '"]', '$[0]') AS Part1,
      JSON_VALUE('["' + REPLACE(pql.QueuedRecordId, '-', '","') + '"]', '$[1]') AS Part2
  ) split
  INNER JOIN dbo.CLog cl
    ON cl.Conversation_ID = CAST(split.Part1 AS INT)
    AND cl.Memo_ID = CAST(split.Part2 AS INT)
  

• 方案3：改造自定义函数为内联表值函数
  如果必须保留fn_SplitString函数，把原来的多语句实现改成内联实现，优化器可以直接展开函数逻辑，不会产生额外调用开销，示例实现参考：

  ALTER FUNCTION dbo.fn_SplitString(@InputStr NVARCHAR(MAX), @Delimiter CHAR(1))
  RETURNS TABLE 
  AS RETURN (
    SELECT 
      LEFT(@InputStr, CHARINDEX(@Delimiter, @InputStr) - 1) AS Part1,
      STUFF(@InputStr, 1, CHARINDEX(@Delimiter, @InputStr), '') AS Part2
    WHERE CHARINDEX(@Delimiter, @InputStr) > 0
  )
  

• 方案4：预计算拆分结果（性能最高）
  业务允许的前提下直接给ProcessQueueLog加两个持久化计算列，插入数据时自动完成拆分，查询时直接关联即可：

  -- 新增持久化计算列
  ALTER TABLE Automation.ProcessQueueLog
  ADD 
    Conversation_ID AS CAST(LEFT(QueuedRecordId, CHARINDEX('-', QueuedRecordId) -1) AS INT) PERSISTED,
    Memo_ID AS CAST(STUFF(QueuedRecordId, 1, CHARINDEX('-', QueuedRecordId), '') AS INT) PERSISTED;
  -- 可选：新增索引进一步优化关联性能
  CREATE NONCLUSTERED INDEX IX_ProcessQueueLog_ConvMemo ON Automation.ProcessQueueLog(Conversation_ID, Memo_ID);
  

  改造后查询语句可以简化为：

  SELECT pql.*, cl.ShortNote
  FROM Automation.ProcessQueueLog pql
  INNER JOIN dbo.CLog cl
    ON cl.Conversation_ID = pql.Conversation_ID
    AND cl.Memo_ID = pql.Memo_ID
  

• 补充索引优化
  给CLog加联合覆盖索引，避免关联时回表查询：

  CREATE NONCLUSTERED INDEX IX_CLog_ConvMemo ON dbo.CLog(Conversation_ID, Memo_ID) INCLUDE (ShortNote);
  

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