程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> 和表值函數銜接激發的機能成績剖析

和表值函數銜接激發的機能成績剖析

編輯：MSSQL

和表值函數銜接激發的機能成績剖析。本站提示廣大學習愛好者：（和表值函數銜接激發的機能成績剖析）文章只能為提供參考，不一定能成為您想要的結果。以下是和表值函數銜接激發的機能成績剖析正文

表值函數

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