Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 275

the encapsulation aspect of the UDF leads them to sooner or later being used in larger, reporting-type
result sets. When this happens, there is a hidden performance impact. To examine this follow this
example of this UDF being used in an indiscriminate select statement:

SELECT *, dbo.ufnGetProductStandardCost(ProductId, @ORDERDATE)
FROM Production.Product

For each row in the Production.Product table, the UDF is evaluated. If we extracted the contents of the
UDF, it would be the equivalent of applying a forced correlated subquery like this:

SELECT *,
(SELECT pch.[StandardCost]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductCostHistory] pch
ON p.[ProductID] = pch.[ProductID]
AND p.[ProductID] = o.[ProductId]
AND @OrderDate BETWEEN pch.[StartDate]
AND COALESCE(pch.[EndDate], CONVERT(datetime, '99991231', 112))

)
FROM Production.Product o

Both the UDF version and the Correlated subquery versions are simple to understand, but this example
can also be written in a simple Join or a set-based way like this.

SELECT p.*, pch.[StandardCost]
FROM [Production].[Product] p
LEFT OUTER JOIN [Production].[ProductCostHistory] pch
ON p.[ProductID] = pch.[ProductID]
AND @OrderDate BETWEEN pch.[StartDate]
AND COALESCE(pch.[EndDate], CONVERT(datetime, '99991231', 112))

When running and comparing the examples, there are two things to point out. The first is that the cost
component numbers here should not pass your sniff test. The UDF performs the same action as the
correlated subquery so logically should perform the same or worse. The reason it should perform worse
is that the optimizer will not be able to fold or refactor this statement into a join. Therefore, we expect
that the cost would be higher for the UDF over all other methods. Table 9-11 shows the results.

Table 9-11: Comparison of Join, UDF, and Correlated Subquery

Method % Cost CPU Reads Duration

Join 9 30 20 366ms

UDF 5 4526 171097 12542ms

Correlated Subquery 86 60 2035 348ms

Comparatively against the other methods and antithetical to our expectations, the % Cost numbers clearly
call the UDF method the winner. The second thing to notice is that the Compute Scalar Operator has a