Database Reference
In-Depth Information
There is the very important difference between the two implementations, however. The multi-statement
function will not run the second select that generates the XML when the first query does not return any rows. There
is no reason for it to do that: location does not belong to any POI. Alternatively, inline implementation would
always run the two queries. It could even degrade performance when the location does not belong to a POI, and the
underlying query against the POI table is expensive. It would be better to split the function into two separate ones:
GetPOINameInline and GetPOIIDListInline , and refactor the outer queries in the manner shown in Listing 10-18.
Listing 10-18. Converting multi-statement to inline functions: Refactoring of the outer query
from
dbo.Locations l
outer apply dbo.GetPOINameInline(l.Latitude, l.Longitude) pn
outer apply
(
select
case
when @ReturnList=1 and pn.POIID is not null
then (
select IDList
from dbo.GetPOIIDListInline(l.latitude,l.longitude)
)
else null
end
) pids
A CASE statement in the second OUTER APPLY operator guarantees that the second function will be executed only
when the dbo.GetPOINameInline function returns the data ( pn.POIID is not null ); that is, there is at least one POI
for the location.
Summary
While encapsulation and code reuse are great processes that can simplify and reduce the cost of development,
they are not always well suited for T-SQL code. Generalization of the implementation in order to support multiple
use-cases within a single method can lead to suboptimal execution plans in some cases. This is especially true for
the multi-statement functions, both scalar and table-valued. There is large overhead associated with their calls, which
in turn introduces serious performance issues when functions are called for a large number of rows. Moreover, SQL
Server does not expand them to the referenced queries, and it always estimates that table-valued functions return a
single row.
Predicates that include multi-statement functions are always non-SARGable, regardless of the indexes defined
on the table. This can lead to suboptimal execution plans of the queries and extra CPU load due to the function calls.
You need to keep all of these factors in mind when creating multi-statement functions.
On the other hand, inline table-valued functions are expanded to the outer queries similar to regular views. They
do not have the same overhead as multi-statement functions and are optimized as part of the queries. You should
refactor multi-statement functions to inline table-valued functions whenever possible.
 
Search WWH ::




Custom Search