SQL Server多层CTE表值函数执行缓慢优化问题咨询

1. 函数中使用临时表的替代方案 #

SQL Server 中用户定义函数（UDF）确实不支持创建、引用临时表，你可以选择以下替代方案：

• 改用表变量做中间存储：函数支持声明和使用表变量，可用来暂存中间计算结果，替代临时表的暂存需求。
• 替换为存储过程：如果调用场景允许，将当前逻辑迁移到存储过程中实现，存储过程支持临时表、并行执行，优化空间远大于函数，是性能优先场景的首选方案。
• 升级到SQL Server 2019及以上版本：该版本引入了多语句表值函数内联特性，符合条件的多语句TVF会被优化器自动展开，大幅提升执行效率。

2. CTE逻辑优化方案 #

首先需要明确：你当前的多层CTE不属于递归CTE（递归CTE需要包含UNION ALL并引用CTE自身），性能瓶颈主要来自多层链式CTE导致的优化器行数估算错误、嵌套的自定义函数调用，可从以下方向优化：

• 优先优化嵌套自定义函数 #

  你代码中用到的dbo.GetObject、dbo.fnPrices两个表值函数，以及dbo.GetAmountCustomFunction、dbo.FormatMessage两个标量函数如果是多语句实现的，会是最大的性能开销点：
  • 表值函数优先改为内联表值函数实现
  • 标量函数如果使用SQL Server 2017及以上版本，可开启标量UDF内联特性，或直接将函数逻辑展开到主查询中

• 简化CTE层级 #

  你当前的5层CTE均为简单的字段计算逻辑，无需拆分多层，可直接合并为1-2层CTE，避免优化器因多层CTE出现行数估算偏差，生成低效的执行计划。

• 提前过滤数据 #

  将WHERE 1 = SomeValue过滤条件下推到最底层的数据源中，也就是在dbo.GetObject、dbo.fnPrices返回结果后就直接过滤，减少后续窗口函数、计算逻辑处理的数据量。

• 修正基础语法错误 #

  当前代码存在多处语法问题，需要先修正再做性能调优：
  • CTE2中CASE WHEN methodID = 2 THEN END缺少返回值，且ROUND函数的参数、括号匹配错误
  • 参数声明为@XMLBlock但调用dbo.GetObject时用了未声明的@ParameterBlock
  • 调用dbo.fnPrices时用到的@CurrencyID未声明

• 改造为内联表值函数 #

  你当前使用的是多语句表值函数，逻辑非常简单，可直接改造为内联表值函数，示例改造结构如下：

  CREATE FUNCTION [dbo].[GetDataset_Test]
  (@XMLBlock XML, @Id INT, @CurrencyID INT)
  RETURNS TABLE 
  AS
  RETURN
  (
      WITH CTE_ALL AS 
      (
          -- 把原5层CTE的计算逻辑合并到这里，避免多层嵌套
          SELECT   
              BC.Col1,
              BC.con,
              BC.DiscountLine,
              BC.tempNumber,
              BC.ItemCostExVAT,
              CASE WHEN BC.con = 0 THEN BC.Quantity ELSE 1 END AS Quantity,
              BC.methodID,
              BC.VATAmount,
              BC.SubTypeID,
              BC.Line,
              BC.VATMultiplier,
              ROUND(dbo.GetAmountCustomFunction(COALESCE(BC.Id, BC.AnotherId),  COALESCE(BC.Price, BC.PriceValue)), 2) As [Amount],
              MIN(CASE WHEN BC.DiscountLine=1 THEN 1 ELSE 20 END) over (PARTITION by BC.tempNumber) As StockControlled,
              SUM(BC.ItemCostExVAT * CASE WHEN BC.con = 0 THEN BC.Quantity ELSE 1 END) OVER ( PARTITION BY BC.tempNumber ) AS tempCost,
              -- 修正原来CASE语法错误后写在这里
              ROUND(CASE WHEN BC.methodID = 8 THEN BC.DiscValue WHEN BC.methodID = 2 THEN 你需要补充的返回值 END, 2) AS AmountExVAT,
              dbo.FormatMessage('name1', 'name2') AS SavingMessage
          FROM dbo.GetObject(@Id, 2, @XMLBlock) AS BC
          INNER JOIN dbo.fnPrices(@Id, @CurrencyID) BPD ON BPD.Id = BC.productid
          WHERE 1 = BC.SomeValue
      )
      SELECT methodID, [Id], SavingMessage FROM CTE_ALL
  )
  

  内联表值函数会被SQL Server优化器直接展开到调用它的父查询中，执行效率远高于多语句表值函数。

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