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

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