LEFT vs INNER join performance change – why?
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.
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.