LEFT vs INNER join performance change – why?

Dan Holmes 2013-03-25 13:31:11

I have two questions in this post: first the actual SQL and plans and second and to me the more important one, what method is used for wading through a plan as sizeable as this one to find that reason.

The SQL text for the attached two plans is the same in every respect except the first 3 joins of the CTE named ArrivalLeg. The difference is LEFT vs INNER. The LEFT produces a significantly faster duration and a lower read count. It isn't that different in this text but this is part of a MS-TVF which is called from another MS-TVF (http://answers.sqlsentry.net/questions/616/cancelling-a-query-doesnt-seem-to-really-stop-the.html). I am very interested in the why of LEFT vs. INNER. This plan also as other problems but those aren't my immediate concern unless they also intersect this problem.

Now, more important in my opinion is how you found whatever answer inside this forest. With such a trivial change to the text and given the significant difference in the trees' shape, how did you walk through the woods to find the right tree(s)?

I have plan explorer pro now, so any usage tips would be handy.

thanks for any insight.

link text

SQLkiwi 2013-04-19 11:04:09
Changing inner to outer joins affects the query optimizer's plan-selection process in a number of ways. First, it affects the initial join order selected heuristically. Second, it affects the number of ways the optimizer can reorder the joins while retaining the original semantics of the query (whether intentional or not). Third, the optimizer has relatively few ways to reorder and transform outer joins, compared with inner joins (which have simple semantics and a large number of well-known safe transformations). The outer join plan happens to have better performance characteristics in this case, but that will not always be true, or even be true more often than not. If the intention is to override the optimizer completely, you would use the FORCE ORDER query hint and likely many other hints to force the type of joins used as well. I say this because I cannot believe anyone would expect the optimizer to do much more than guess at something that might be reasonable with a single query as large as this one.

In addition. This design makes extensive use of scalar functions which access data. This pattern is almost always contra-indicated, not least because scalar functions are hopelessly inefficient in the current implementation, since a separate T-SQL context is created for each execution of each function. There are other reasons to avoid T-SQL functions (particularly those that access data) but I want to avoid writing a book in reply to the original question.

I do understand that certain problems naturally lend themselves to a procedural style of programming, but writing queries with huge numbers of nested CTEs (inline views) and scalar functions is so far outside the optimizer's intended usage that essentially all bets are off.

99 times out of 100, the way to analyze a huge plan is to simply accept that it is hopelessly wrong and rewrite it in a form that is easier for humans to maintain and for relational optimizers to reason about. Either that, or force the execution plan shape with extensive hints or plan guides and cross your fingers.