SQL Server 2016含PayerName数组的JSON生成查询性能优化求助

嗨，针对你遇到的这个JSON数组字段拖慢查询的问题，我在SQL Server 2016上处理过类似场景，给你几个具体的优化方向：

• 先排查执行计划，定位瓶颈
  先打开SSMS的「显示估计执行计划」（快捷键Ctrl+L），或者执行SET SHOWPLAN_XML ON;运行你的查询，重点看生成PayerName字段的子查询部分：

  • 是不是对每个商家记录都重复扫描了Payer表？如果看到Table Scan或者Key Lookup的开销占比极高，那就是关联查询的效率问题。
  • 有没有出现笛卡尔积或者意外的高行数？比如子查询返回的行数远超预期，这会直接拉慢整个查询。

• 用SQL Server原生FOR JSON替代手动字符串拼接
  SQL Server 2016原生支持JSON生成，比手动用STUFF+FOR XML PATH拼接JSON数组高效得多。假设你之前是手动拼接数组，比如：

  SELECT 
      MerchantId,
      MerchantName,
      PayerName = (
          SELECT '[' + STUFF((
              SELECT ',"' + PayerName + '"' 
              FROM Payer p 
              WHERE p.MerchantId = m.MerchantId
              FOR XML PATH('')
          ), 1, 1, '') + ']'
      )
  FROM VW_Merchant m
  

  改成原生FOR JSON写法：

  SELECT 
      m.MerchantId,
      m.MerchantName,
      (
          SELECT PayerName 
          FROM Payer p 
          WHERE p.MerchantId = m.MerchantId
          FOR JSON PATH
      ) AS PayerName
  FROM VW_Merchant m
  FOR JSON PATH
  

  原生JSON引擎是底层优化过的，避免了大量字符串拼接的CPU开销，能显著提升速度。

• 给关联字段建覆盖索引
  如果Payer表是通过MerchantId和主表关联，一定要给这个字段建覆盖索引，包含PayerName字段，这样子查询不需要回表读取数据：

  CREATE NONCLUSTERED INDEX IX_Payer_MerchantId ON Payer(MerchantId) INCLUDE (PayerName);
  

  这个索引能让SQL Server直接从索引中获取生成JSON数组需要的所有数据，不用扫描整个Payer表，是提升关联查询速度的关键。

• 优化视图VW_Merchant的执行效率
  虽然单独查视图很快，但嵌套查询时视图可能被多次执行或者展开后出现冗余逻辑：

  • 如果视图包含复杂的多表关联、聚合，可以考虑把视图的逻辑直接展开到主查询中，避免视图嵌套带来的额外开销。
  • 如果视图是确定性的（没有非确定性函数比如GETDATE()），可以尝试创建索引视图，让SQL Server直接从索引中读取视图数据，减少重复计算。

• 避免不必要的JSON格式化操作
  如果你的最终结果不需要额外的引号转义或者自定义格式，尽量依赖FOR JSON的默认行为，不要手动在结果上做字符串替换、拼接操作——这些操作在18k条数据量级下会消耗大量CPU资源。

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