Table Valued Function Killing My Query Performance

Question

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....

Answer 1

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.

Answer 2

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.