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

表值函数

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

图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

(0)

相关推荐

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

    表值函数 SQL Server中提供了类似其他编程语言的函数,而函数的本质通常是一段代码的封装,并返回值.在SQL Server中,函数除了可以返回简单的数据类型之外(Int.Varchar等),还可以返回一个集合,也就是返回一个表.     而根据是否直接返回集合或是定义后再返回集合,表值函数又分为内联用户定义表值函数和用户定义表值函数(下文统称为表值函数,省去"用户定义"四个字). 内联表值函数     内联表值函数和普通函数并无不同,唯一的区别是返回结果为集合(表),而不是简单数

  • Javascript三种字符串连接方式及性能比较

    第一种:用连接符"+"连接字符串 str="a"; str+="b"; 这种方法相对以下两种,最便捷快速.建议100字符以下的连接使用这种连接方式. 第二种:以数组作为中介,使用jion函数进行连接 var arr=new Array(); arr.push(a); arr.push(b); var str=arr.join(""); 第三种:利用对象属性连接字符串 function stringConnect(){ this

  • Python 使用 PyMysql、DBUtils 创建连接池提升性能

    Python 编程中可以使用 PyMysql 进行数据库的连接及诸如查询/插入/更新等操作,但是每次连接 MySQL 数据库请求时,都是独立的去请求访问,相当浪费资源,而且访问数量达到一定数量时,对 mysql 的性能会产生较大的影响.因此,实际使用中,通常会使用数据库的连接池技术,来访问数据库达到资源复用的目的. 解决方案:DBUtils DBUtils 是一套 Python 数据库连接池包,并允许对非线程安全的数据库接口进行线程安全包装.DBUtils 来自 Webware for Pyth

  • 关于Oracle多表连接,提高效率,性能优化操作

    执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就可能要几十表了. 这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE提交一个SQL语句,ORAC

  • 浅谈JS中的三种字符串连接方式及其性能比较

    工作中经常会碰到要把2个或多个字符串连接成一个字符串的问题,在JS中处理这类问题一般有三种方法,这里将它们一一列出顺便也对它们的性能做个具体的比较. 第一种方法 用连接符"+"把要连接的字符串连起来: str="a"; str+="b"; 毫无疑问,这种方法是最便捷快速的,如果只连接100个以下的字符串建议用这种方法最方便. 第二种方法 以数组作为中介用 join 连接字符串: var arr=new Array(); arr.push(a);

  • JavaScript通过join函数连接数组里所有元素的方法

    本文实例讲述了JavaScript通过join函数连接数组里所有元素的方法.分享给大家供大家参考.具体实现方法如下: <script type="text/javascript"> var days = ["Sunday","Monday","Tuesday","Wednesday", "Thursday","Friday","Saturday&

  • SQL SERVER函数之深入表值函数的处理分析

    有些情况可能用下表值函数,表值函数主要用于数据计算出来返回结果集,可以带参数(和视图的一个大的区别),如果函数中没有过多的逻辑处理,如变量的定义,判断等,表值函数返回结果集可以简单向下面这么写: 复制代码 代码如下: CREATE FUNCTION Fun_GetReportNews(@type varchar(10))RETURNS TABLEAS RETURN(  SELECT TPR_ID,TPR_Title,TPR_Date FROM TP_ReportNews WHERE TPR_Ty

  • python函数运行内存时间等性能检测工具

    目录 基础测试函数 memory_profiler进程 timeit 时间使用情况 line_profiler行代码检测 heartrate可视化检测 python虽然是一门'慢语言',但是也有着比较多的性能检测工具来帮助我们优化程序的运行效率. 这里总结了五个比较好的python性能检测工具,包括内存使用.运行时间.执行次数等方面. 基础测试函数 首先,来编写一个基础的python函数用于在后面的各种性能测试. def base_func():     for n in range(10000

  • php中file_get_contents与curl性能比较分析

    本文实例讲述了php中file_get_contents与curl性能比较分析.分享给大家供大家参考.具体如下: 在php中如果不仔细的去分析性能会发现file_get_contents与curl两个同很多共同点的,他们都可以采集文件打开文件,但是如果仔细一对比会发现很多不同点,下面我们一起来看看file_get_contents与curl区别. PHP中fopen,file_get_contents,curl函数的区别: 1.fopen /file_get_contents 每次请求都会重新做

  • Vue.js 应用性能优化分析+解决方案

    目录 一.介绍 二.为什么我们需要 Vue JS 性能优化? 三.Vue 性能不佳背后的主要原因 1. 生成报告 2. 运行命令和npm 运行生成 四.如何优化 Vue js 应用程序的性能? 1. 在 Vue js 中懒加载 2. 基于路线的代码拆分 3.Vue js预加载组件 4. 优化第三方库 5. 使用浏览器缓存 6. 优化和压缩图像 前言: 假设我们在开发整个 Vue 应用程序方面非常努力.js.但没有优先考虑其性能:现在,我们的应用程序需要一段时间来加载.导航.提交或执行任何用户操作

随机推荐