October 9, 2011 at 11:21 am
If inline TVF's are going to be treated as views and the plan reuse only occurs when the same select statement is re executed that called the function with the same parameters then how is that Inline TVF's are considered better than muti-statement TVF's ?
Any thoughts ???
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
October 9, 2011 at 12:24 pm
Interesting question. I am not completely sure, but I do know that multi-statement TVF's are treated like stored procedures. Like views, inline TVF's are more flexible than stored procedures or multi-statement TVF's. I'll have to do a little research on this.
October 9, 2011 at 12:57 pm
I found a really interesting discussion of the performance differences between inline TVF's and multi-statement TVF's on Stackoverflow.
I'll summarize the discussion here, though.
Bottom line, inline TVF's should be preferred over multi-statement TVF's whenever possible for performance reasons. However, sometimes you have to use a multi-statement TVF, because an inline function won't do what you need.
The biggest difference between the two is that queries against the inline TVF's will use the latest statistics for the underlying tables being queried. This allows the optimizer to generate a more appropriate execution plan. The optimizer treats multi-statement TVF's much like stored procedures: the dataset is loaded into an internal temp table and then queried.
Side Note: Erland Sommerskog published an outstanding article on how the query optimizer compiles and processes stored procedures. I highly recommend it!
http://www.sommarskog.se/query-plan-mysteries.html
Another big difference between inline TVF's and multi-statement TVF's is that the optimizer expands the inline function much as it does for a view and creates a consolidated execution plan against all of the underlying objects in the entire query. This is particularly important when you are using a TVF in a JOIN and that TVF returns a large data set. When you use multi-statement TVF's in a JOIN, the optimizer treats the function as a unit and assumes that it will return one record. This results in less than optimal query plans when large data sets are involved.
The comments of one of the participants in the Stackoverflow discussion is worth quoting in full:
Internally, SQL Server treats an inline table valued function much like it would a view and treats a multi-statement table valued function similar to how it would a stored procedure.
When an inline table-valued function is used as part of an outer query, the query processor expands the UDF definition and generates an execution plan that accesses the underlying objects, using the indexes on these objects.
For a multi-statement table valued function, an execution plan is created for the function itself and stored in the execution plan cache (once the function has been executed the first time). If multi-statement table valued functions are used as part of larger queries then the optimiser does not know what the function returns, and so makes some standard assumptions - in effect it assumes that the function will return a single row, and that the returns of the function will be accessed by using a table scan against a table with a single row.
Where multi-statement table valued functions can perform poorly is when they return a large number of rows and are joined against in outer queries. The performance issues are primarily down to the fact that the optimiser will produce a plan assuming that a single row is returned, which will not necessarily be the most appropriate plan.
As a general rule of thumb we have found that where possible inline table valued functions should be used in preference to multi-statement ones (when the UDF will be used as part of an outer query) due to these potential performance issues.
Another participant said that stored procedures actually work better than multi-statement TVF's when large data sets are returned, but did not explain why.
In summary: you should use inline TVF's if you need a function that you will use as a part of a query; you should use stored procedures when you have a "stand-alone" process that will not be used as part of a larger query, and you should only use multi-statement TVF's if you must.
October 9, 2011 at 6:58 pm
Ok, David... I've seen quite enough and you've asked for it 😉
You write nicely, you cite references, you write code to build test data and then to do the tests, and you're enthusiastic and courteous the whole time... It's time for you to pick a subject and write an article for SSC on it. No excuses... just do it. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply