Notice (8): file_put_contents(): Write of 270 bytes failed with errno=28 No space left on device [CORE/src/Log/Engine/FileLog.php, line 140]

Notice: file_put_contents() [function.file-put-contents]: Write of 1108 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 5131 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 3151 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 284 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 2558 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): unserialize() [<a href='https://secure.php.net/function.unserialize'>function.unserialize</a>]: Error at offset 32069 of 32757 bytes [APP/Controller/NewsController.php, line 5571]

Notice: file_put_contents() [function.file-put-contents]: Write of 2396 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): unserialize() [<a href='https://secure.php.net/function.unserialize'>function.unserialize</a>]: Error at offset 94197 of 94197 bytes [APP/Controller/NewsController.php, line 5571]

Notice: file_put_contents() [function.file-put-contents]: Write of 2489 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): unserialize() [<a href='https://secure.php.net/function.unserialize'>function.unserialize</a>]: Error at offset 85980 of 86005 bytes [APP/Controller/NewsController.php, line 5571]

Notice: file_put_contents() [function.file-put-contents]: Write of 2489 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
和表值函数连接引发的性能问题分析 - 站长搜索
娱乐休闲

娱乐休闲

工商与经济

电脑与网络

公司与企业

教育与培训

文学

艺术

艺术

体育与健身

新闻与媒体

卫生与健康

科学/文化

生活与服务

论坛/聊天室

论坛/聊天室

网络与通讯

出版发行

会展活动

数理化学

极限运动

地方新闻媒体

地方新闻媒体

成人用品

报纸

书法/篆刻

气象学

外语/英文/日语/法语

首页 > 资讯列表 > 编程/数据库 >> 数据库操作教程

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

数据库操作教程 2022-09-23 17:33:25 转载来源: 网络整理/侵权必删

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

表值函数

    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

标签: 表值 函数 连接 引发 性能 问题 分析


声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！

上一篇: SQLServer2016的数据库范围内的配置详解
上一篇: 如何使用VisualStudio2010在数据库中生成随机测试数据

猜您喜欢

热门文章

热点

标签列表

站长搜索

http://www.adminso.com

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索

会员注册

会员登陆

在线投稿

提交网站

站长资讯

网站列表

域名库

关键词库

帮助中心

免责声明

隐私保护

域名查询

广告赞助

网站地图

打开手机扫描上面的二维码打开手机版


使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

站长搜索目录系统技术支持