Changing two INNER JOINs to LEFT JOINs improves exec time from 7 min to 2 secs
I'll attach one of the good plans…
[link text][1]
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:
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.