SQL SERVER中用户定义标量函数（scalar user defined function）的性能问题

用户定义函数（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;
GO
CREATE FUNCTION Sales.FetchProductOrderNum
(
    @ProuctID  INT
) RETURNS INT
BEGIN
    DECLARE @SaleOrderNum INT;
    SELECT @SaleOrderNum=COUNT(SalesOrderID)  FROM Sales.SalesOrderDetail 
    WHERE ProductID=@ProuctID
    GROUP BY ProductID;
 
    RETURN @SaleOrderNum;
END
GO

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

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

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

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

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

而Compute
Scalar在此处就是调用标量函数，而标量函数调用总是需要资源开销和时间的，当调用次数从2次变为4688次时，elapsed time 和CPU

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

  
回到正题，上面两个SQL语句的实际执行计划的Cost比值为81%：19%；Compute
Scalar（进行一个标量计算并返回计算值）的Number of Executions都是1次。但是实际的CPU time
&elapsed time的比值比这个大了好多。另外第一个SQL的Compute
Scalar的代价比值居然只有1%。为什么会这样呢？我们是不是很迷惑？

 

关于这个大家疑惑的地方，T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)里面给了我们一个阐述，截取文章中两段在此(翻译如有不当，请参考原文)：

 

英文：

      However, you may not be
aware that the “Actual Execution Plan” is a dirty rotten liar. Or maybe I
should say that the terms “Actual Execution Plan” and “Estimated
Execution Plan” are misleading. There is only one execution plan, it
gets created when the queries are compiled, and then the queries are
executed. The only difference between the “Actual” and the “Estimated”
execution plan is that the estimated plan only tells you the estimates
for how many rows flow between iterators and how often iterators are
executed, and the actual plan adds the actual data for that. But no
“actual operator cost” or “actual subtree cost” is added to the
corresponding estimated values – and since those costs are the values
that the percentages are based on, the percentages displayed in an
actual execution plan are still based only on the estimates.

 

翻译：

   
然而，你可能不知道“实际执行计划”其实是一个肮脏的烂骗子，或者我应该说“实际执行计划”和“估计执行计划”误导你了。当查询语句编译后，只有一个实际
的执行计划。“实际执行计划”与“估计执行计划”的区别就在于“估计执行计划”只告诉你估计了有多少行流向迭代和迭代器执行频率，而实际执行计划将实际数
据应用进来。但是“实际操作成本”或“实际子树成本”并没有添加到“实际执行计划”的估计值里面， 因为这些代价都是基于百分比的值，在实际执行计划中显示的百分比仍然基于只估计数。

 

英文：

    But note that, again, the
execution plan is lying. First, it implies that the UDF is invoked only
once, which is not the case. Second, look at the cost. You may think
that the 0% is the effect of rounding down, since a single execution of
the function costs so little in relation to the cost of accessing and
aggregating 100,000 rows. But if you check the properties of the
iterators of the plan for the function, you’ll see that all operator and
subtree costs are actually estimated to be exactly 0. This lie is maybe
the worst of all – because it’s not just the plan lying to us, it is
SQL Server lying to itself. This cost estimate of 0 is actually used by
the query optimizer, so all plans it produces are based on the
assumption that executing the function is free. As a result, the
optimizer will not even consider optimizations it might use if it knew
how costly calling a scalar UDF actually is.

 

翻译：

但是需要再次注意，执行计划在欺骗你，首先，它意味着只调用了UDF一
次，其实不是这样。其次，从成本（Cost）来看，你可能会认为0%是向下舍入影响，因为单次执行函数的开销如此之小，以至于执行100,000次的成本
也很小。但如果你检查执行计划的功能迭代器的属性，你会发现所有的操作代价和子树代价实际的估计为0，这是一个最糟糕的谎言。
因为它可能不只是为了欺骗我们，而是SQL
SERVER为了欺骗它自己。实际上是查询优化器认为调用函数的成本为0，因此它生成的所有执行计划都是基于调用UDF是免费的。其结果是即使调用标量
UDF的代价非常昂贵，查询优化器也不会考虑优化它。

 

如何优化UDF标量函数（Scalar-Valued Function）


如何优化上面SQL语句呢？从原理上来讲就是不用用户定义函数或减少调用次数。 其实我在实际应用中，减少调用次数一般通过下面方法优化：

1：减少用户定义标量函数调用次数（子查询）

SET STATISTICS TIME ON;
 
SELECT ProductID, Sales.FetchProductOrderNum(ProductID)
FROM(
    SELECT DISTINCT ProductID FROM Sales.SalesOrderDetail
WHERE ProductID=870) T
 
SET STATISTICS TIME OFF;

 

2：减少用户定义标量函数调用次数（临时表）

SET STATISTICS TIME ON;
 
 
SELECT DISTINCT ProductID INTO #SalesOrderDetail FROM Sales.SalesOrderDetail
WHERE ProductID=870;
 
SELECT ProductID, Sales.FetchProductOrderNum(ProductID)
FROM #SalesOrderDetail
 
SET STATISTICS TIME OFF;

 

为什么要用临时表呢？不是子查询就可以解决问题吗？问题是实际应用当中，有些逻辑复杂的地方需要借助临时表解决，有时候子查询反而不是一个好的解决方法。

另外，我们来看看Performance Considerations of User-Defined Functions in SQL Server 2012这篇文章中，测试UDF的性能案例，本想单独翻译这篇文章，不过结合这篇文章，在此实验验证也是个不错的选择。下面案例全部来自这篇博客。我们先准备测试环境:

CREATE FUNCTION dbo.Triple(@Input INT) 
       RETURNS INT 
AS 
BEGIN; 
  DECLARE @Result INT; 
  SET @Result = @Input * 3; 
  RETURN @Result; 
END; 
GO 
 
CREATE TABLE dbo.LargeTable 
  (KeyVal INT NOT NULL PRIMARY KEY, 
   DataVal INT NOT NULL CHECK (DataVal BETWEEN 1 AND 10) 
  );
 
WITH Digits 
AS (SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(d)) 
INSERT INTO dbo.LargeTable (KeyVal, DataVal) 
SELECT 1000000 * sm.d 
     + 100000 * ht.d + 10000 * tt.d + 1000 * st.d 
     + 100 * h.d + 10 * t.d + s.d + 1, 
       10 * RAND(CHECKSUM(NEWID())) + 1 
FROM   Digits AS s,  Digits AS t,  Digits AS h, 
       Digits AS st, Digits AS tt, Digits AS ht, 
       Digits AS sm;
GO
 
CREATE INDEX NCL_LargeTable_DataVal ON dbo.LargeTable (DataVal);
GO
 
SET STATISTICS TIME ON; 
 
SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple 
FROM dbo.LargeTable AS d; 
 
SELECT MAX(3 * DataVal) AS MaxTriple 
FROM dbo.LargeTable AS d; 
 
SET STATISTICS TIME OFF;

 

如上所示，用户定义的标量函数dbo.Triple，测试用的的一个表dbo.LargeTable ，以及构造了1000000行数据。从下面我们可以看到用户定义标量函数性能确实很糟糕。

下面测试4中写法的性能。相信这个简单的脚本，大家都能看懂，在此不做过多描述、说明：

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT ON;
SET STATISTICS TIME ON; 
SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple 
FROM dbo.LargeTable AS d; 
 
SET STATISTICS TIME OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON; 
SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple 
FROM (SELECT DISTINCT DataVal FROM dbo.LargeTable) AS d; 
SET STATISTICS TIME OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
 
SET STATISTICS TIME ON; 
 
SELECT MAX(3 * DataVal) AS MaxTriple 
 
FROM (SELECT DISTINCT DataVal FROM dbo.LargeTable) AS d; 
 
SET STATISTICS TIME OFF;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
 
SET STATISTICS TIME ON; 
 
SELECT MAX(3 * DataVal) AS MaxTriple 
 
FROM dbo.LargeTable AS d; 
 
SET STATISTICS TIME OFF;

Performance Considerations of User-Defined Functions in SQL Server 2012博客里面统计的数据如下所示


T-SQL Syntax
Avg CPU Time in ms
Avg Elapsed Time in ms

Function, no distinct subquery
12925.0
14247.8

Function, subquery
853.0
853.8

Inline calculation, subquery
853.2
850.4

Inline calculation, no distinct subquery
0.0
0.0

这个跟我测试的数据有所出入（可能跟数据库版本、机器配置有一点关系）。但是大体方向是一致的。Avg CPU Time和Avg Elapsed Time 执行时间依然Function,
no distinct subquery  > Function, subquery = Inline calculation,
subquery > Inline calculation, no distinct subquery

那么接下来，我们先进一个表值函数Triple_tbl，对比表值函数和标量函数的性能。如下所示

CREATE FUNCTION dbo.Triple_tbl (@DataVal INT)
RETURNS TABLE 
AS
RETURN
SELECT @DataVal * 3 Triple
GO
 
 
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON; 
 
SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple 
FROM (SELECT DISTINCT DataVal FROM dbo.LargeTable) AS d; 
 
SELECT MAX(3 * DataVal) AS MaxTriple 
FROM dbo.LargeTable AS d; 
 
SELECT MAX(t.Triple) AS MaxTriple
FROM dbo.LargeTable l
CROSS APPLY dbo.Triple_tbl(l.DataVal) t
 
SET STATISTICS TIME OFF;
GO

从下可以看出，表值函数比标量函数性能要好很多，所以用表值函数替换标量函数也是一个可以考虑的优化方案。

  

时间： 2024-02-23 02:06:37

SQL SERVER中用户定义标量函数（scalar user defined function）的性能问题的相关文章

SQL Server如何定位自定义标量函数被那个SQL调用次数最多浅析

前阵子遇到一个很是棘手的问题,监控系统DPA发现某个自定义标量函数被调用的次数非常高,高到一个离谱的程度.然后在Troubleshooting这个问题的时候,确实遇到了一些问题让我很是纠结,下文是解决问题过程的一点思索和尝试,如果你有更好的思路和解决方法,也请多多指教.   DPA可以监控到该函数每小时被调用的次数,如下截图所示:     那么第一个问题来了. DPA如何监控获取这个函数每小时执行多少次呢? 其实这个很简单, sys.dm_exec_query_stats视图里面有个字段exec

SQL Server中CONVERT()日期转换函数的用法

先来看语汇法  代码如下 复制代码 CONVERT(data_type(length),data_to_be_converted,style) 例1  代码如下 复制代码 SELECT CONVERT(varchar(30),getdate(),101) now 结果为: now --------------------------------------- |03/8/2013 例2  代码如下 复制代码 1> select CONVERT(varchar(8),getdate(),112) 

漫谈SQL Server中的标识列(一)

server 漫谈SQL Server中的标识列(一) 一.标识列的定义以及特点 SQL Server中的标识列又称标识符列,习惯上又叫自增列.该种列具有以下三种特点: 1.列的数据类型为不带小数的数值类型2.在进行插入(Insert)操作时,该列的值是由系统按一定规律生成,不允许空值3.列值不重复,具有标识表中每一行的作用,每个表只能有一个标识列. 由于以上特点,使得标识列在数据库的设计中得到广泛的使用. 二.标识列的组成创建一个标识列,通常要指定三个内容:1.类型(type)在SQL Ser

关于SQL Server中的死锁详解介绍(1/3)

 一些基础知识     要理解SQL Server中的死锁,更好的方式是通过类比从更大的面理解死锁.比如说一个经典的例子就是汽车(主体)对于道路(资源)的征用,如图1所示. 对于死锁的直观理解      在图1的例子中,每队汽车都占有一条道路,但都需要另外一队汽车所占有的另一条道路,因此互相阻塞,谁都无法前行,因此造成了死锁.由这个简单的例子可以看出,发生死锁需要四个必要条件,如下: 1)互斥条件:     主体对于资源是独占的,图1中每条汽车道只能跑一队汽车,不能跑第二队.  2)请求和等待条

SQL SERVER中强制类型转换cast和convert的区别详解_MsSql

SQL SERVER中强制类型转换cast和convert的区别 在SQL SERVER中,cast和convert函数都可用于类型转换,其功能是相同的, 只是语法不同. cast一般更容易使用,convert的优点是可以格式化日期和数值. select CAST('123' as int) -- 123 select CONVERT(int, '123') -- 123 select CAST(123.4 as int) -- 123 select CONVERT(int, 123.4) --

SQL SERVER中强制类型转换cast和convert的区别

在SQL SERVER中,cast和convert函数都可用于类型转换,其功能是相同的, 只是语法不同. cast一般更容易使用,convert的优点是可以格式化日期和数值. 代码 select CAST('123' as int)   -- 123select CONVERT(int, '123')  -- 123 select CAST(123.4 as int)   -- 123select CONVERT(int, 123.4)  -- 123  select CAST('123.4' 

SQL Server中多表连接时驱动顺序对性能的影响

原文:SQL Server中多表连接时驱动顺序对性能的影响   本文出处:http://www.cnblogs.com/wy123/p/7106861.html (保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)   最近在SQL Server中多次遇到开发人员提交过来的有性能问题的SQL,其表面的原因是表之间去的驱动顺序造成的性能问题,具体表现在(已排除其他因素影响的情况下),存储过程偶发性的执行时间超出预期,甚至在调

【转】SQL Server 的三种自定义函数（用户定义的函数）

原文链接:http://www.cftea.com/c/2007/08/6HLN4P3VBKA1W2EA.asp   "自定义函数"是我们平常的说法,而"用户定义的函数"是 SQL Server 中书面的说法. SQL Server 2000 允许用户创建自定义函数,自定义函数可以有返回值. 自定义函数分为:标量值函数或表值函数 如果 RETURNS 子句指定一种标量数据类型,则函数为标量值函数.可以使用多条 Transact-SQL 语句定义标量值函数. 如果 R

SQL Server中授予用户查看对象定义的权限

SQL Server中授予用户查看对象定义的权限   在SQL Server中,有时候需要给一些登录名(用户)授予查看所有或部分对象(存储过程.函数.视图.表)的定义权限存.如果是部分存储过程.函数.视图授予查看定义的权限,那么就像下面脚本所示,比较繁琐:     GRANT VIEW DEFINITION ON  YOUR_PROCEDURE TO   USERNAME;   GRANT VIEW DEFINITION ON  YOUR_FUNCTION TO USERNAME;   GRAN