Craig Farrell (4/6/2011)
There's nothing in those estimated plans that pop out and bite me... but they're estimates. My guess is there's a significant difference between estimated and actual. Can you yank the actuals?
Craig, many thanks for a quick reply. I'm uploading 4 new SQL plans (3 for temp tables approach, 1 for derived tables) in a single zip file.
You are onto something: the actual plan for the derived table has an over-fed boa constrictor running from the lazy spool operator to the nested loops join (after scanning CrsEvalResponse index), and it has around 468 million actual rows in it (versus an estimated 479 rows). Could that be a problem?:-D
Is this the cost of the DISTINCT operator over the 125,000 rows that the table contains? It's not enough rows for a cross join to itself (that would be 125,000 squared or around 15-16 billion rows).
That doesn't tell me why that's happening though.... Any ideas?
Thanks very much,
Rich