小结" />
当前位置： 代码迷 >> Sql Server >> 小结
  详细解决方案

小结

热度：530   发布时间：2016-04-24 09:25:03.0
和表值函数连接引发的性能问题

    最近调优过程中遇到一个问题，就是表值函数作为连接中的一部分时，可能会引起麻烦，本文会简单阐述表值函数是什么，以及为什么使用表值函数进行连接时会引发性能问题。

表值函数

    SQL Server中提供了类似其他编程语言的函数，而函数的本质通常是一段代码的封装，并返回值。在SQL Server中，函数除了可以返回简单的数据类型之外（Int、Varchar等），还可以返回一个集合，也就是返回一个表。

    而根据是否直接返回集合或是定义后再返回集合，表值函数又分为内联用户定义表值函数和用户定义表值函数（下文统称为表值函数，省去“用户定义”四个字）。

 

内联表值函数

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

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT s.Name AS Store, a.City
        FROM Sales.Store AS s
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS sp 
            ON sp.StateProvinceID = a.StateProvinceID
        WHERE 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所示。

image

图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所示。

image

图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

2楼dgdyq
即使是内联函数，应该也由于内联函数返回表没有索引，效率也不高？
Re: CareySon
@dgdyq，那和内联函数没有关系，如果表上没有索引，直接引用表效率也不会高。如果基本表上有索引，查询可以直接引用基本表的索引。
1楼lucky7_2000
记得表变量也有类似的问题, 生成执行计划时, 总是估计表变量中只有一条数据; 而临时表就不会这样.
Re: CareySon
@lucky7_2000，是的。但表值函数影响更大。
  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.