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?