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

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

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

--创建表值行数 
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 

--表值函数做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

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