5

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?

  • 1
    the overall shape is quite different -- please be more specific here. – GSerg Dec 21 '12 at 19:43
  • Are there different row estimates for the number of rows matching where f.ToDate >= @ToDate? – Martin Smith Dec 21 '12 at 19:46
  • @GSerg: That statement was more to say that I actually did look at the actual (not estimated) execution plans for both statements and was expecting the query plans to be the same. I don't know what descriptors I'd be able to use here that would be meaningful to anyone. – Ben Thul Dec 21 '12 at 19:47
  • Are you able to post the actual query? You stated that the example above is greatly simplified, implying that your actual query is much more complex. Something in that complexity must be contributing to the query optimizer's decision to create a different plan. – Johann Dec 21 '12 at 19:59
  • 2
    If the plans are different, it is convincing prima facie evidence that there IS a difference. Please post the execution plans of the two queries. Have you looked into whether statistics are updated correctly for the tables & columns in question? – ErikE Dec 21 '12 at 20:29
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.