Cost of Inline Table Valued function in SQL Server

Question

Is there an inherent cost to using inline-table-valued functions in SQL Server 2008 that is not incurred if the SQL is inlined directly? Our application makes very heavy use of inline-table-valued functions to reuse common queries, but recently, we've found that queries run much faster if we don't use them.

Consider this:

CREATE FUNCTION dbo.fn_InnerQuery (@asOfDate DATETIME)
RETURNS TABLE
AS
RETURN
(
   SELECT ... -- common, complicated query here
)

Now, when I do this:

SELECT TOP 10 Amount FROM dbo.fn_InnerQuery(dbo.Date(2009,1,1)) ORDER BY Amount DESC

The query returns with results in about 15 seconds.

However, when I do this:

SELECT TOP 10 Amount FROM 
(
   SELECT ... -- inline the common, complicated query here
) inline
ORDER BY Amount DESC

The query returns in less than 1 second.

I'm a little baffled by the overhead of using the table valued function in this case. I did not expect that. We have a ton of table valued functions in our application, so I'm wondering if there is something I'm missing here.

Answer 1

One thing that can slow functions down is omitting dbo. from table references inside the function. That causes SQL Server to do a security check for every call, which can be expensive.

Answer 2

In this case, the UDF should be unnested/expanded like a view and it should be transparent.

Obviously, it's not...

In this case, my guess is that the column is smalldatetime and is cast to datetime because of the udf parameter but the constant is correctly evaluated (to match colum datatype) when inline.

datetime has a higher precedence that smalldatetime, so the column would be cast

What do the query plans say? The UDF would show a scan, the inline a seek most likely (not 100%, just based on what I've seen before)

Edit: Blog post by Adam Machanic

Answer 3

Try running the table valued function independently to see, how fast/slow it executes?

Also, I am not sure how to clear the execution cache(?) which SQL Server might retain from the execution of the UDF. I mean - if you run the UDF first, it could be the case where SQL Server has the actual query with it & it could cache the plan/result. So, if you run the complicated query separately - it could be running it from cache.

Answer 4

In your second example the Table Valued function has to return the entire data set before the query can apply the filter. Hopping across the TF boundary is not something that the optimiser can always do.

In the third example the query optimiser can work out that the user only wants the top few 'amounts'. If this isn't an aggregate value the optimiser can push that processing right to the start of the query and not bother with any other data. If it is an aggregate amount then the slowdown is for a different reason.

If you compare the query plans of the two queries you should see that they are different.

Answer 5

I suspect inefficient query plans with the TVF.

"Multistatement table-valued user-defined functions (also known as TVFs) have no statistics. The optimizer must guess the size of their results." msdn