SQL SERVER中用户定义标量函数（scalar user defined function）的性能问题 - 潇湘隐_H5之家

用户定义函数（UDF）分类

SQL SERVER中的用户定义函数（User Defined Functions 简称UDF）分为标量函数（Scalar-Valued Function）和表值函数(Table-Valued Function)。其中表值函数又分为Inline table-valued functions和Multistatement table-valued functions。

用户定义函数（UDF）在 SQL Server 中发挥重要的作用。用户定义函数可以用于执行复杂的逻辑，可以接受参数并返回数据。很多时候我们需要写复杂的逻辑，不能使用单个查询编写。在这种情况下，用户定义函数（UDF）发挥了重要的作用。关于用户定义函数的优点，可以参考官方文档“用户定义函数”。如下所示：

用户定义函数的优点

在 SQL Server 中使用用户定义函数有以下优点：

允许模块化程序设计。

只需创建一次函数并将其存储在数据库中，以后便可以在程序中调用任意次。用户定义函数可以独立于程序源代码进行修改。

执行速度更快。

与存储过程相似，Transact-SQL 用户定义函数通过缓存计划并在重复执行时重用它来降低 Transact-SQL 代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。

和用于计算任务、字符串操作和业务逻辑的 Transact-SQL 函数相比，CLR 函数具有显著的性能优势。Transact-SQL 函数更适用于数据访问密集型逻辑。

减少网络流量。

基于某种无法用单一标量的表达式表示的复杂约束来过滤数据的操作，可以表示为函数。然后，此函数便可以在 WHERE 子句中调用，以减少发送至客户端的数字或行数。

UDF标量函数（Scalar-Valued Function）影响性能案例

官方文档说用户定义函数（UDF）的执行速度更快，意思是性能非常好，如果你对此深信不疑的话，那么我只能呵呵了，其实关于用户定义函数，尤其是标量函数，需要合理使用。有些场景使用不当，则有可能造成性能问题。关于UDF的标量函数会引起性能的问题，下面我们先看一个我构造的例子吧（AdventureWorks2014），我们需要查询某个产品有多少订单（其实也是优化过程中遇到，然后我在此处构造类似这样的一个案例）

USE AdventureWorks2014;GOCREATE FUNCTION Sales.FetchProductOrderNum( @ProuctID INT) RETURNS INTBEGIN DECLARE @SaleOrderNum INT; SELECT @SaleOrderNum=COUNT(SalesOrderID) FROM Sales.SalesOrderDetail WHERE ProductID=@ProuctID GROUP BY ProductID; RETURN @SaleOrderNum;ENDGO

clipboard

我们知道Sales.SalesOrderDetail表里面ProductID=870的订单数量有4688，而ProductID=897的订单数量只有2条记录。那么执行下面语句时，性能会有什么差异呢？

SET STATISTICS TIME ON; SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetailWHERE ProductID=870 SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetailWHERE ProductID=897 SET STATISTICS TIME OFF;

clipboard[1]

为什么会有这种情况，这是因为SQL语句里面调用用户定义标量函数（UDF Scalar Function），都是逐行调用用户定义函数，这样需要为每行去提取用户定义函数的定义，然后去执行这些定义，从而导致了性能问题；更深层次的原因是因为函数采用了过程式的处理方法，而SQL Server查询数据则是基于数据集合的，这样在采用过程式的逐行处理时，SQL Server性能就会显著降低。

那么我来分析看看这两个SQL的实际执行计划：从下面实际执行计划，我们可以看到第一个SQL语句执行计划从Index Seek 到Compute Scalar的数据流变粗了。这个表示第一个SQL语句的Index Seek返回的数据要多。

clipboard[2]

接下来，我们从Compute Scalar（进行一个标量计算并返回计算值）里面可以看到Actual Number of Rows 的值为4688 和2 。

clipboard[3]

clipboard[4]

而Compute Scalar在此处就是调用标量函数，而标量函数调用总是需要资源开销和时间的，当调用次数从2次变为4688次时，elapsed time 和CPU time当然会翻了好多倍。实际环境中，用户定义标量函数的逻辑比上面简单的案例更复杂，资源开销更大，所以有时候你会看到性能差距非常悬殊的SQL案例，在工作中我就发现过这样的情况，有些开发人员对自定义标量函数使用不当影响性能不甚了解。甚至是完全不知情。他们对此振振有词：你看我SQL语句是一样的，只是参数不同，效率差别这么大。肯定是数据库出现了阻塞或性能问题。要么是服务器的性能问题，反正我SQL是没有问题的，你看这一条语句执行才一秒，换个参数就要一分多钟，这不是你数据库性能问题，那是什么? 这样的一个伪逻辑让我很无语。（习惯性就让我和数据库、服务器背了一个大黑锅）。