Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 22 '12 at 0:28

This question came from our site for professional and enthusiast programmers.

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
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
1  
Different cardinality estimates are a sure sign that something is materially different. In-line TVFs are expanded into the query before optimization, so the input trees would be identical. This question is essentially unanswerable without more details I'm afraid. –  Paul White Dec 22 '12 at 7:10

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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