永利皇宫登录网址:SERVER中用户定义标量函数的性能问题

顾客定义函数(UDF)分类 

     SQL SE中华VVE卡宴中的顾客定义函数(User Defined Functions
简单称谓UDF)分为标量函数(Scalar-Valued Function)和表值函数(Table-Valued
FunctionState of Qatar。个中表值函数又分为Inline table-valued
functions和Multistatement table-valued functions。

    客户定义函数(UDF)在 SQL Server
中发挥举足轻重的功力。客户定义函数能够用来实践复杂的逻辑,能够采取参数并回到数据。相当多时候大家供给写复杂的逻辑,不能够利用单个查询编写。在这里种状态下,客商定义函数(UDF)发挥了要害的效果与利益。关于客户定义函数的长处,能够参照官方文书档案“客户定义函数”。如下所示:

客商定义函数的独特之处

在 SQL Server 中行使客商定义函数有以下优点:

  • 允许模块化程序设计。

    只需创造一次函数并将其积累在数据库中,以后便得以在前后相继中调用任意次。客商定义函数能够独立于程序源代码实行退换。

  • 永利皇宫登录网址 ,实践进度更加快。

    与仓储过程相同,Transact-SQL
    客户定义函数通过缓存陈设并在再次实行时选取它来裁减 Transact-SQL
    代码的编写翻译费用。那意味着每一趟使用顾客定义函数时均没有必要另行解析和另行优化,进而降低了实践时间。

    和用于总结职责、字符串操作和业务逻辑的 Transact-SQL 函数相比较,CL奥德赛函数有所生硬的属性优势。Transact-SQL
    函数更适用于数据访谈密集型逻辑。

  • 减少网络流量。

    基于某种不可能用单风流倜傥标量的表明式表示的繁缛约束来过滤数据的操作,能够代表为函数。然后,此函数便能够在
    WHERE 子句中调用,以减小发送至顾客端的数字或行数。

 

 

UDF标量函数(Scalar-Valued Function)影响属性案例

  
官方文书档案说客商定义函数(UDF)的实施进度越来越快,意思是性质极度好,假若您对此低眉顺眼的话,那么本身只得呵呵了,其实关于客商定义函数,特别是标量函数,须要合理利用。有个别场景使用不当,则有非常大恐怕引致品质难题。关于UDF的标量函数会挑起质量的难题,下边大家先看一个本人布局的事例吗(AdventureWorks二〇一四),大家须求查询有个别产物有多少订单(其实也是优化进程中遇到,然后笔者在这间布局相仿那样的一个案例)

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

永利皇宫登录网址 1

咱俩掌握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;

永利皇宫登录网址 2

干什么会有这种场所,那是因为SQL语句里面调用客户定义标量函数(UDF Scalar
Function),都以逐行调用客户定义函数,那样必要为每行去领取顾客定义函数的概念,然后去试行那么些概念,进而招致了性能问题;越来越深层次的缘故是因为函数接纳了进程式的拍卖方法,而SQL
Server查询数据则是依照数据集结的,这样在动用进程式的逐行管理时,SQL
Server品质就能刚强下落。

那正是说小编来分析看看那三个SQL的其实实行安排:从上面实际推行安顿,大家得以见见第多个SQL语句试行安排从Index
Seek 到Compute Scalar的数目流变粗了。这几个代表第一个SQL语句的Index
Seek重返的数码要多。

永利皇宫登录网址 3

接下去,大家从Compute
Scalar(进行二个标量总计并赶回计算值)里面可以见到Actual Number of Rows
的值为4688 和2 。

永利皇宫登录网址 4

永利皇宫登录网址 5

而Compute
Scalar在那地正是调用标量函数,而标量函数调用总是必要财富开荒和岁月的,当调用次数从2次改为4686次时,elaspes
time当然会翻了不菲倍。实际条件中,标量函数的逻辑比上面简单的案例更复杂,财富开拓更加大,所以不经常候你会看出质量差森松尼其悬殊的SQL案例,在专门的学问中作者就意识过如此的情状,有个别开拓人士对自定义标量函数使用不当影响属性不甚精通。甚至是截然不知情。他们对此义正言辞:你看作者SQL语句是同样的,只是参数区别,功效差异如此大。肯定是数据库现身了绿灯或质量难题。要么是服务器的个性难题,反正自身SQL是不曾难题的,你看这一条语句推行才黄金时代秒,换个参数将在一分多钟,这不是您数据库质量难点,那是何许?
那样的贰个伪逻辑让笔者很无助。(习于旧贯性就让小编和数据库、服务器背了三个大受累)。

   回到正题,上面多少个SQL语句的莫过于实施陈设的Cost比值为81%:19%,Compute
Scalar(进行三个标量计算并赶回总括值)的Number of
Executions都以1次。可是实际的CPU time &elapsed
time的比率比那么些大了累累。其它第一个SQL的Compute
Scalar的代价比值居然独有1%。为啥会这么吗?大家是还是不是很吸引。

永利皇宫登录网址 6

有关这么些我们纠结之处,T-SQL User-Defined Functions: the good, the bad,
and the ugly (part
1卡塔尔国里面给了大家三个论述,截取文章中两段在这(翻译如有不当,请参见原版的书文State of Qatar:

英文:

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

  1. 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
SELANDVE智跑为了棍骗它本身。实际上是询问优化器以为调用函数的开支为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;

永利皇宫登录网址 7

 

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;

何以要用有的时候表呢?不是子查询就能够消除难题吧?难题是事实上行使个中,某些逻辑复杂的地点要求依赖有时表消除,有的时候候子查询反并非一个好的缓慢解决措施。

永利皇宫登录网址 8

此外,大家来看看Performance Considerations of User-Defined Functions in
SQL Server
二零一二那篇著作中,测量检验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行数据。从上面大家得以见见客商定义标量函数品质确实非常不佳。

永利皇宫登录网址 9

上面测量试验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;

永利皇宫登录网址 10

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;

永利皇宫登录网址 11

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;

永利皇宫登录网址 12

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

 

SET STATISTICS TIME ON; 

 

SELECT MAX(3 * DataVal) AS MaxTriple 

 

FROM dbo.LargeTable AS d; 

 

SET STATISTICS TIME OFF;

永利皇宫登录网址 13

博客里面计算的数码如下所示

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

从下得以看见,表值函数比标量函数质量要好广大,所以用表值函数替换标量函数也是一个足以设想的优化方案。

永利皇宫登录网址 14

 

参照他事他说加以考察资料:

网站地图xml地图