rmechaber (4/6/2011)
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
Heh, I don't want to skip what Gus mentioned, but just from the query plan I see here, that's... ugly. Your nested loop join is just not behaving itself, and it's abusing that tablespool. It's not restricting TempCalendarID in srOffer before it does the monster join. #tc is your prize here. That worktable is killing you and no, I don't know exactly why it thinks it's necessary.
It's not an uncommon result that a divide and conquer can help with execution plans. There's just a certain point where the optimizer gets confused. Think of it like an idiot savant. Oddly enough the XML shows this is a FULL optimization Level. I'd kind of expected a timeout. No missing index complaints. Interesting. Bad statistics?
Have you updated statistics lately and/or rebuilt the indexes?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA