Inline table-valued function vs inline sql

Question

I'm seeing some odd behavior in trying to tune an inline table-valued function. Specifically, if I take the guts of the function and it with the same parameters as I'd have called the function with, I see about a 3x performance gain. So something like this (greatly simplified):

create function dbo.FooToDate(@ToDate date)
returns table
as
return (

select f.a, f.b, b.c
    from dbo.[Foo] as f
    left join dbo.Bar as b
       on f.fId = b.fId
    where f.ToDate >= @ToDate

)
go
declare @ToDate date = '2012-12-21'

--runs in about 4 seconds
select f.a, f.b, b.c
from dbo.[Foo] as f
left join dbo.Bar as b
   on f.fId = b.fId
where f.ToDate >= @ToDate

--runs in about 12 seconds
select a, b, c from dbo.FooToDate(@ToDate)

When I look at the query plans for my actual situation, the overall shape is quite different. My understanding of inline TVFs was that the opimizer was able to unroll the function, so this behavior is somewhat odd to me. Is there a simple (or not-so-simple) explanation?

Answer 1

In SQL Server 2000 and earlier we all were accustomed to using alternating forms of logically equivalent queries as a means of query tuning.

Since then the optimizer has improved so much that we are surprised if two different but logically equivalent queries result in different execution plans.

However, the search space of all possible execution plans for a given query is of significant size even for only moderately complex queries so that it is impossible for the optimizer to look at all of them. The optimizer uses a mix of heuristics and rules and information about the data to determine where to look next after having found a first suitable plan. If the query is complex enough even a trivial change like this can set the optimizer of on totally different paths that in the end lead to such big differences in run time. (Check out my post on join hints for a more in detail discussion of this: http://sqlity.net/en/1443/a-join-a-day-join-hints/

Often we can remedy a situation like this by making sure that the information the optimizer is basing its decisions on is as accurate as possible. That means, you should make sure that your tables are appropriately indexed and that all statistics are up to date. You might also look into adding additional - maybe filtered - statistics to the tables involved.