和表值函数连接引发的性能问题分析

2020-07-25 12:54:10

字体：大中小

来源：转载

供稿：网友

表值函数

SQL Server中提供了类似其他编程语言的函数，而函数的本质通常是一段代码的封装，并返回值。在SQL Server中，函数除了可以返回简单的数据类型之外（Int、Varchar等），还可以返回一个集合，也就是返回一个表。
而根据是否直接返回集合或是定义后再返回集合，表值函数又分为内联用户定义表值函数和用户定义表值函数（下文统称为表值函数，省去“用户定义”四个字）。

内联表值函数
内联表值函数和普通函数并无不同，唯一的区别是返回结果为集合（表），而不是简单数据类型，一个简单的内联表值函数如代码清单1所示（摘自MSDN）。

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion( @Region nvarchar(50) )RETURNS tableASRETURN (SELECT DISTINCT s.Name AS Store, a.CityFROM Sales.Store AS sINNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = s.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressIDINNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceIDWHERE sp.Name = @Region);GO

代码清单1.一个简单的表值函数

用户定义表值函数
而用户定义表值函数，需要在函数开始时定义返回的表结构，然后可以写任何代码进行数据操作，插入到定义的表结构之后进行返回，一个稍微负责的用户定义表值函数示例如代码清单2所示（摘自MSDN）。

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function ContactID int PRIMARY KEY NOT NULL, FirstName nvarchar(50) NULL, LastName nvarchar(50) NULL, JobTitle nvarchar(50) NULL, ContactType nvarchar(50) NULL ) AS -- Returns the first name, last name, job title, and contact type for the specified contact. BEGIN DECLARE @FirstName nvarchar(50), @LastName nvarchar(50), @JobTitle nvarchar(50), @ContactType nvarchar(50); -- Get common contact information SELECT @ContactID = BusinessEntityID, @FirstName = FirstName, @LastName = LastName FROM Person.Person WHERE BusinessEntityID = @ContactID; -- Get contact job title SELECT @JobTitle = CASE -- Check for employee WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') THEN (SELECT JobTitle FROM HumanResources.Employee AS e WHERE e.BusinessEntityID = @ContactID) -- Check for vendor WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC') THEN (SELECT ct.Name FROM Person.ContactType AS ct INNER JOIN Person.BusinessEntityContact AS bec ON bec.ContactTypeID = ct.ContactTypeID WHERE bec.PersonID = @ContactID)  -- Check for store WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC') THEN (SELECT ct.Name FROM Person.ContactType AS ct INNER JOIN Person.BusinessEntityContact AS bec ON bec.ContactTypeID = ct.ContactTypeID WHERE bec.PersonID = @ContactID) ELSE NULL END; -- Get contact type SET @ContactType = CASE -- Check for employee WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') THEN 'Employee' -- Check for vendor WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC') THEN 'Vendor Contact' -- Check for store WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC') THEN 'Store Contact' -- Check for individual consumer WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN') THEN 'Consumer' -- Check for general contact WHEN EXISTS(SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC') THEN 'General Contact' END; -- Return the information to the caller IF @ContactID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; END; RETURN; END; GO

代码订单2.表值函数

为什么要用表值函数
看起来表值函数所做的事情和存储过程并无不同，但实际上还是有所差别。是因为表值函数可以被用于写入其他查询，而存储过程不行。此外，表值函数和Apply操作符联合使用可以极大的简化连接操作。

如果存储过程符合下述条件的其中一个，可以考虑重写为表值函数。

•存储过程逻辑非常简单，仅仅是一个Select语句，不用视图的原因仅仅是由于需要参数。
•存储过程中没有更新操作。
•存储过程中没有动态SQL。
•存储过程中只返回一个结果集。
•存储过程的主要目的是为了产生临时结果集，并将结果集存入临时表以供其他查询调用。

用户定义表值函数的问题

    表值函数与内联表值函数不同，内联表值函数在处理的过程中更像是一个视图，这意味着在查询优化阶段，内联表值函数可以参与查询优化器的优化，比如将筛选条件（Where）推到代数树的底部，这意味着可以先Where再Join，从而可以利用索引查找降低IO从而提升性能。
    让我们来看一个简单的例子。下面代码示例是一个简单的和表值函数做Join的例子：
    首先我们创建表值函数，分别为内联表值函数方式和表值函数方式，如代码清单3所示。

--创建表值行数 CREATE FUNCTION tvf_multi_Test ( ) RETURNS @SaleDetail TABLE ( ProductId INT ) AS BEGIN INSERT INTO @SaleDetail SELECT ProductID FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID RETURN END --创建内联表值函数 CREATE FUNCTION tvf_inline_Test ( ) RETURNS TABLE AS RETURN SELECT ProductID FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID

代码清单3.创建两种不同的函数

现在，我们使用相同的查询，对这两个表值函数进行Join，代码如代码清单4所示。

--表值函数做Join SELECT c.personid , Prod.Name , COUNT(*) 'numer of unit' FROM Person.BusinessEntityContact c INNER JOIN dbo.tvf_multi_Test() tst ON c.personid = tst.ProductId INNER JOIN Production.Product prod ON tst.ProductId = prod.ProductID GROUP BY c.personid , Prod.Name  --内联表值函数做Join SELECT c.personid , Prod.Name , COUNT(*) 'numer of unit' FROM Person.BusinessEntityContact c INNER JOIN dbo.tvf_inline_Test() tst ON c.personid = tst.ProductId INNER JOIN Production.Product prod ON tst.ProductId = prod.ProductID GROUP BY c.personid , Prod.Name

代码清单4.表值函数和内联表值函数做Join

执行的成本如图1所示。

图1.两种方式的成本

从IO来看，很明显是选择了次优的执行计划，BusinessEntityContact选择了121317次查找，而不是一次扫描。而内联表函数能够正确知道扫描一次的成本远低于一次查找。

那问题的根源是内联表值函数，对于SQL Server来说，和视图是一样的，这意味着内联表值函数可以参与到逻辑执行计划的代数运算（或者是代数树优化）中，这意味着内敛表可以进一步拆分（如图1所示，第二个内联表的查询，执行计划具体知道内敛表中是SalesOrderHeader表和SalesOrderDetail表，由于查询只选择了一列，所以执行计划优化直到可以无需扫描SalesOrderHeader表），对于内联表值函数来说，执行计划可以完整知道所涉及的表上的索引以及相关统计信息等元数据。
另一方面，表值函数，如图1的第一部分所示，表值函数对整个执行计划来说是一个黑箱子，既不知道统计信息，也没有索引。执行计划中不知道表值函数所涉及的表（图1中为#AE4E5168这个临时表，而不是具体的表明），因此对整个执行计划来说该结果集SQL Server会假设返回的结果非常小，当表值函数返回的结果较多时（如本例所示），则会产生比较差的执行计划。
因此综上所述，在表值函数返回结果极小时，对性能可能没有影响，但返回结果如果略多，则一定会影响执行计划的质量。

如何处理
首先，在SQL Server中，我们要找出现存的和表值函数做Join的语句，通过挖掘执行计划，我们可以找出该类语句，使用的代码如代码清单5所示。

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p) SELECT st.text, qp.query_plan FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC ) AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp WHERE qp.query_plan.exist('//p:RelOp[contains(@LogicalOp, "Join")]/*/p:RelOp[(@LogicalOp[.="Table-valued function"])]') = 1

代码清单5.从执行计划缓存中找出和表值函数做Join的查询

结果如图2所示。

图2.执行计划缓存中已经存在的和表值函数做Join的查询

小结
本文阐述了表值函数的概念，表值函数为何会影响性能以及在执行计划缓存中找出和表值函数做Join的查询。对于和表值函数做Apply或表值函数返回的行数非常小的查询，或许并不影响。但对于返回结果较多的表值函数做Join，则可能产生性能问题，因此如果有可能，把表值函数重写为内联表值函数或将表值函数的结果存入临时表再进行Join可提升性能。

参考资料：

http://www.brentozar.com/blitzcache/tvf-join/

http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx?CommentPosted=true#commentmessage

上一篇：SqlServer 2000、2005分页存储过程整理第1/3页

下一篇：在SQL Server中将数据导出为XML和Json的方法