Changing two INNER JOINs to LEFT JOINs improves exec time from 7 min to 2 secs

BrianE 2013-04-09 19:44:29

This plan takes over 7 min to execute the query. Changing two joins from INNER to LEFT allows it to run in 2 seconds.

/storage/attachments/381-BadBadPlan.queryanalysis
Jonathan Kehayias 2013-04-09 19:50:25
Can you post the other plan as well?
BrianE 2013-04-09 20:04:32
For some reason, this plan appears to be doing a bunch of table scans on [4CGCalendar]. Then, it's doing a join to the fact table named [4CGHoursFees]. I actually have three different examples of query plans that execute in 2 seconds. In all three cases, they look at [4CGHoursFees] two separate times, then join the [4CGCalendar] to those two separate streams, then join the two streams together.

I'll attach one of the good plans…

[link text][1]

/storage/attachments/385-goodgoodplan.queryanalysis
BrianE 2013-04-10 12:37:44
There is a sort operation after a table scan that required 41% of the entire 7:30 to complete. However, the major difference between estimate and actual didn't occur until much later in the plan (where it was getting millions of records instead of a few thousand), and those operations all executed quickly! The only solution I found was changing the joins to my two derived tables to LEFT instead of INNER, and like magic the query executed in 0:02. Whyyyyy???? (There was no change in the result set whatsoever.)
Dan Holmes 2013-04-11 13:01:27
i posted something similar a couple days ago. I still don't know why the LEFTs performed better.
BrianE 2013-04-16 14:52:19
It's not the first time I've encountered this, either. The reason I changed the INNER to LEFT was because I remember this same thing happening about two years ago.
BrianE 2013-04-16 14:52:51
…and then POOF, it worked.
SQLkiwi 2013-04-19 10:35:40
Changing from an inner to an outer join does change the logical semantics of the query, even if it does not actually change the result in a particular test. The point is that the query optimizer has much less freedom to reorder outer joins, so you will generally get a different plan shape.

It just so happens that this query with inner joins ends up in a form that turns out to be desperately wrong at execution time. As usual, the cause is incorrect cardinality estimates. The key operator to look at is the sort:

Sort fragment

Workspace memory for the sort is reserved based on the estimated number of rows (3,357). The plan shows the actual memory grant for the plan is just under 17MB. This is hardly likely to be enough for a full sort of the 40,682,240 rows actually encountered. On SQL Server 2012, the execution plan would have a warning triangle showing how many times sort runs had to be spilled to physical tempdb disk because of the lack of memory grant. On your version of SQL Server, you would need to track and correlate Sort Warnings events using Profiler.

With the outer joins, the final plan shape happens to avoid such a large sort, and performance is much better. This is pure chance, of course. When the optimizer has the wrong numbers to base its decisions on, whether you get a plan that runs quickly or not is pure luck.

BrianE 2013-04-19 13:06:23
But that step looked like it was only 4% of the total cost. There was a table scan and sort earlier on that took 18 + 41 = 59%!
SQLkiwi 2013-04-19 13:27:03
Estimated costs (and percentages) in execution plans are exactly that: estimates. They never reflect actual costs, just the estimated costs according to the optimizer's model. If the row counts are wrong, so are the costs and percentages. Even so, the optimizer's model is an abstraction that happens to produce reasonable plans for most systems worldwide – it does not use specific CPU or I/O cost modelling for your particular hardware. Never use estimated costs and percentages as a primary tuning metric – the information is indicative in some cases, but only to a skilled analyst that understands exactly what the numbers mean and in what context they should be read.
April 9, 2013
No Comments