Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Table Valued Function Killing My Query Performance

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

like image 717
scarpacci Avatar asked Nov 05 '10 18:11

scarpacci


People also ask

What can cause a slow performance of an SQL query?

Slow running queries can be a result of missing indexes, poor execution plans, bad application and schema design, etc.

Can we use CTE in table valued function?

You can only use a CTE within the context of DML language (SELECT, INSERT, UPDATE, DELETE, MERGE).


2 Answers

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.

like image 161
AdaTheDev Avatar answered Sep 21 '22 03:09

AdaTheDev


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.

like image 44
Charles Bretana Avatar answered Sep 20 '22 03:09

Charles Bretana