Analysis of performance problems caused by join of table-valued function _mssql

Table-Valued functions

SQL Server provides functions similar to other programming languages, and the nature of a function is usually encapsulated by a piece of code and returns a value. In SQL Server, a function can return a collection, in addition to a simple data type (Int, varchar, and so on), and return a table.
The table-valued function is divided into an inline user-defined table-valued function and a user-defined table-valued function (hereinafter referred to as a table-valued function, omitting the "user-defined" four words), depending on whether the collection is returned directly or the definition is returned.

Inline table-valued function
Inline table-valued functions are no different from normal functions, except that the return result is a collection (table) rather than a simple data type, and a simple inline table-valued function, as shown in Listing 1 (excerpt from MSDN).

CREATE FUNCTION sales.ufn_customernamesinregion
(@Region nvarchar)
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

Code Listing 1. A simple table-valued function

User-defined table-valued functions
and user-defined table-valued functions. You need to define the returned table structure at the start of the function, and then write any code to manipulate the data, insert it into the defined table structure, and return it, a slightly responsible user-defined table-valued function, as shown in Listing 2 (excerpt from MSDN).

CREATE FUNCTION dbo.ufngetcontactinformation (@ContactID int) RETURNS @retContactInformation TABLE (--Columns Returne D by the function ContactID int PRIMARY KEY NOT null, FirstName nvarchar (m) null, LastName nvarchar (m) NULL, JOBTITL e nvarchar (x) null, ContactType nvarchar () null) as--Returns the "name, last name, job title, and contacts T 
Ype for the specified contacts. 
BEGIN DECLARE @FirstName nvarchar (m), @LastName nvarchar (m), @JobTitle nvarchar (50), @ContactType nvarchar; 
--Get common contact information SELECT @ContactID = BusinessEntityID, @FirstName = FirstName, @LastName = LastName 
From Person.person WHERE BusinessEntityID = @ContactID; --Get Contacts job title SELECT @JobTitle = case-Check for employee when EXISTS (SELECT * from Person.person as P W Here P.businessentityid = @ContactID and P.persontype = ' EM ') THEN (SELECT jobtitle from HumanResources.Employee as e W Here E.businessentityid = @ContactID)--Check for vendor 
When EXISTS (SELECT * from Person.person as P WHERE P.businessentityid = @ContactID and P.persontype = ' VC ') THEN (Sele CT 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 = @Co Ntactid 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 contacts 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 contacts '--Check for St Ore when EXISTS (SeleCT * from Person.person as P WHERE P.businessentityid = @ContactID and P.persontype = ' SC ') THEN ' Store contacts '--Che CK for individual consumer when EXISTS (SELECT * from Person.person as P WHERE P.businessentityid = @ContactID and P.pers Ontype = ' in ') THEN ' Consumer '--Check for general contacts when EXISTS (SELECT * from Person.person as P WHERE p.busin 
Essentityid = @ContactID and P.persontype = ' GC ') THEN ' general contacts ' end; --Return of the information to the caller IF @ContactID was not NULL BEGIN INSERT @retContactInformation SELECT @ContactI 
D, @FirstName, @LastName, @JobTitle, @ContactType; 
End; 
return; 
End; Go

Code Order 2. Table-valued function

Why should I use table-valued functions
It seems that the table-valued function does not differ from the stored procedure, but in fact it is different. is because table-valued functions can be used to write to other queries, and stored procedures are not. In addition, table-valued functions combined with the Apply operator can greatly simplify the connection operation.

Consider overriding a table-valued function if the stored procedure meets one of the following criteria.

• Stored procedure logic is very simple, just a SELECT statement, without the view of the reason only because of the need for parameters.
• There is no update operation in the stored procedure.
• There is no dynamic SQL in the stored procedure.
• Only one result set is returned in the stored procedure.
• The primary purpose of the stored procedure is to produce a temporary result set and to save the result set to a temporary table for other queries to invoke.

Problems with user-defined table-valued functions

Table-valued functions are different from inline table-valued functions, and inline table-valued functions are more like a view during processing. This means that in the query optimization phase, inline table-valued functions can participate in the optimization of the query optimizer, such as pushing the filter condition (Where) to the bottom of the algebra tree, which means that you can join it first, This enables the use of index lookups to reduce IO and thereby improve performance.
Let's take a look at a simple example. The following code example is a simple example of a join with a table-valued function:
First we create table-valued functions, which are inline table-valued functions and table-valued functions, as shown in code Listing 3.

--Create a table-valued number of rows creating 
FUNCTION tvf_multi_test () 
RETURNS @SaleDetail table (ProductId INT) as 
BEGIN 
INSERT into @SaleDetail 
SELECT ProductID 
sales.salesorderheader soh 
INNER JOIN Sales.SalesOrderDetail sod on Soh. SalesOrderID = Sod. SalesOrderID 
return End 
--Creates an inline table-valued function tvf_inline_test create function 
() 
RETURNS table 
As 
return 
SELECT ProductID from 
sales.salesorderheader Soh 

Code Listing 3. To create two different functions

Now, we use the same query to join the two table-valued functions, as shown in Listing 4 of the code.

--table-valued functions do join 
SELECT C.personid, 
prod.name, 
COUNT (*) ' Numer ' 
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 
 
--inline table-valued functions do 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

Code Listing 4. Table-valued functions and inline table-valued functions do join

The cost of execution is shown in Figure 1.

Figure 1. Cost of two ways

From the IO point of view, it is obvious that the suboptimal execution plan was selected, and Businessentitycontact selected 121,317 lookups instead of one. The inline table function correctly knows that the cost of scanning once is much lower than the first lookup.

The root of that problem is the inline table-valued function, which, for SQL Server, is the same as the view, which means that inline table-valued functions can participate in the algebraic operations of a logical execution plan (or an algebraic tree optimization), which means that the inward table can be further split (as shown in Figure 1, the second inline table query, The execution plan specifically knows that the SalesOrderHeader table and the SalesOrderDetail table are in the introverted table, and because the query selects only one column, the plan optimization is performed until the SalesOrderHeader table can be scanned without scanning, and for the inline table-valued function, The execution plan is fully aware of the indexes on the tables involved and the metadata such as related statistics.
On the other hand, table-valued functions, as shown in the first part of Figure 1, are a black box for the entire execution plan, with neither statistics nor indexes. The table-valued function is not known in the execution plan (the temporary table in Figure 1 is #ae4e5168). Rather than a concrete indication), the result set SQL Server assumes a very small result for the entire execution plan, and when the table-valued function returns more results (as shown in this example), Will result in a worse execution plan.
So to sum up, when a table-valued function returns minimal results, it may have no effect on performance, but if the return result is slightly more, it will certainly affect the quality of the execution plan.

How to Handle
first, in SQL Server, we'll find the existing and table-valued functions to join the statement, through the mining execution plan, we can find the class of statements, using the code as shown in Listing 5.

With XmlNamespaces (' Http://schemas.microsoft.com/sqlserver/2004/07/showplan ' as P) 
SELECT St.text, 
Qp.query_plan from 
( 
SELECT the 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

Code Listing 5. Find the query that joins the table-valued function from the execution plan cache

The result is shown in Figure 2.

Figure 2 Query that already exists in the execution plan cache and that joins the table-valued function

Summary
This article describes the concept of table-valued functions, why table-valued functions affect performance and find queries that join the table-valued function in the execution plan cache. A query that has a very small number of rows returned by apply or table-valued functions may not be affected. However, a join to a table-valued function that returns more results can cause performance problems, so if possible, overriding the table-valued function as an inline table-valued function or depositing the results of a table-valued function in a temporary table can improve performance.

Resources:

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