I was having a horrible time today trying to get a query to perform the way I would expect. I had to make a slight change to a table valued function that lives in the query yesterday and that change created a huge performance impact on the query. After evaluating the execution plan and looking at statistics IO and Time I found that because I changed the function to return a table variable instead of just a result set it was doing a full scan on one of the tables being queried.
My question is why would having it return the table (TableVariable) instead of just a Select / Result set cause such a big change to the plan?
Stumped....
Returning a Table Variable will make it a multi-statement table valued function and can be bad for performance due to the fact that it's treated like a table except there are no statistics available for SQL Server to base a good execution plan on - so it will estimate the function as returning a very small number of rows. If it returns a larger number of rows, then therefore the plan generated could be a lot less than optimal.
Whereas, returning just a SELECT makes it an inline table valued function - think of it more as a view. In this case, the actual underlying tables get brought into the main query and a better execution plan can be generated based on proper statistics. You'll notice that in this case, the execution plan will NOT have a mention of the function at all as it's basically just merged the function into the main query.
There's a great reference on it on MSDN by CSS SQL Server Engineers including (quote):
But if you use multi-statement TVF,
it’s treated as just like another
table. Because there is no
statistics available, SQL Server has
to make some assumptions and in
general provide low estimate. If your
TVF returns only a few rows, it will
be fine. But if you intend to
populate the TVF with thousands of
rows and if this TVF is joined with
other tables, inefficient plan can
result from low cardinality estimate.
This is because a multi-Statement Table valued UDF cannot be processed inline with the rest of the SQL statememnt it is used in, and therefore cannot be part of the statement cache plan.. That means that it must be compiled separately from the rest of the SQL it is used in, over and over, for every row in the final resultset generated by the query.
An Inline Table valued UDF, otoh, is processed and compiled along with the sql it is used in, and it therefore becomes part of the cache plan and only gets processed and compiled once, no matter how many rows you generate.
Really impossible to answer definitively without more information. However, since I like to take crazy stabs in the dark . . .
Table variables can't be optimized by the engine--the engine always "assumes" that the table variable only has one row in it when it generates an execution plan. That is one reason why you might be seeing strange performance.
On the SQL Server 2014 we were able to solve our issue by inserting table value function data into temp table and then doing join on it. Instead of doing a join directly to table value function.
This improved our execution time from 2 min to 4 secs.
Here is an example that worked for our team:
--SLOW QUERY (2 min):
DECLARE @id INT = 1;
SELECT *
FROM [data].[someTable] T
INNER JOIN [data].[tableValueFunction](@id) TVF ON TVF.id = T.id;
--FAST QUERY (4 sec):
DECLARE @id INT = 1;
SELECT *
INTO #tableValueFunction
FROM [data].[tableValueFunction](@id) TVF
SELECT *
FROM [data].[someTable] T
INNER JOIN #tableValueFunction TVF ON TVF.id = T.id;
When using multi-statement table-valued UDF, that UDF is run to completion before its results can be used by the caller. With an inline table-valued UDF, the SQL Server basically expands the UDF into the calling query just like macro expansion. This has the following implications, among others:
- The calling query’s
WHERE clause can be interpolated directly into an inline table-valued UDF, but not a multi-statement UDF. Thus, if your table-valued UDF generates a lot of rows that would be filtered out by the calling query’s WHERE clause, the query optimizer can apply down the WHERE clause directly into an inline table-valued UDF but not into a multi-statement UDF.
- An inline table-valued UDF behaves like a parameterized
VIEW would if SQL Server had such a concept whereas a multi-statement table-valued UDF would behave like you populated and then used a table variable in your query.
If your UDF returns many rows and is backed by a table, I imagine this could be where the table scan is coming from. Either add more parameters to your UDF to enable the caller to constrain its result size or try to reformulate it as an inline table-valued UDF with the help of friends such as UNION et al. I would avoid multi-statement table-valued UDFs at all costs unless if the result size is known to only be a few rows and it is hard to produce the required results with set-based logic.
