Thread: Inline and Multi-Statement Table Valued Function Performance Comparison

Inline and Multi-Statement Table Valued Function Performance Comparison

In summary Inline is preferred over multi-valued functions when dealing with large tables. Recently I was working on a similar case on a project. Changing the multi-statement function to inline gave me a big performance boost; reduced the execution time from 30 second to less then a second for the original stored procedure.

This is a great comment explaining the internals of the performance comparison (stackoverflow):

Internally SQL Server treats an inline table valued function more like it would do for 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.

When the performance becomes an issue
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.

hmm.. nice info! thanks for sharing!