Optimizer Chose Merge Join, I hinted Loop join and much better performance. Why?

mike1142 2014-08-11 14:23:50

link textI have attached 2 execution plans to the question. The queries are identical except that in the first (AD1) the optimizer chose a merge join on the second to last join of the last union all select.

Sorry, not sure how to describe it because the plans are so different.

On the second (AD2) I hinted a LOOP on the same join and got this much different execution plan. Maybe it is obvious what happened to the perfomance but I also know that total logical read went from 28,700,855 on AD1 to 552,114 on AD2

Can someone explain why this happened and the best way to fix it? Hinting gets the results needed short term but your stuck with this choice through unforseeable changes.

Thanks

/storage/attachments/981-AD1.queryanalysis
/storage/attachments/982-ad2.pesession
Ranga 2014-08-12 14:39:36
I have optimized few SPs by just removing the join hints like INNER LOOP JOIN. Hints good now will not work later if the underlying amount of data changes…
as rightly said above "write the query in a such a way that the optimizer naturally picks a good plan. This makes maximal use of the optimizer's ability to react to future changes in e.g. the data distribution"
SQLkiwi 2014-08-12 00:18:31
Using a join hint (like INNER LOOP JOIN) does more than just force the join algorithm for the two tables – it also forces all the tables in the query to be joined in the order they are listed in the query text, just as if you had used an OPTION (FORCE ORDER) query hint. In that respect, join hints are quite the sledgehammer (and force order has other effects as well, that may not always be desirable).

My general advice is to provide a schema (indexes, statistics and so on) and write the query in a such a way that the optimizer naturally picks a good plan. This makes maximal use of the optimizer's ability to react to future changes in e.g. the data distribution. It seems at least possible in this case that the database does not have optimal indexing or statistical information to enable the optimizer to make the right choices.

Even so, there are cases where the optimizer requires a nudge in the right direction. Instead of the join hint sledgehammer, I would probably use a FORCESEEK on Table10 to ensure the optimizer considers the benefit of a dynamic seek plan properly (presumably for an OR or IN predicate):

Dynamic seek plan fragment

This lighter hint should correct the main problem, while still allowing the optimizer great freedom in other areas of the plan.

Dynamic Seeks

The FORCESEEK hint

mike1142 2014-08-13 22:19:11
OK well ultimately the solution I chose for the short term is to use the OPTION (FORCE ORDER) hint because I have been reading that this may be a problem with the optimizer not being able to "push down" the predicate. Joe Sack SQLskills.com is my source.
mike1142 2014-08-14 04:51:57
Oh most definitely sorry I try everything people with more experience than I advise. The force seek had no effect at least how I implemented it via the links provided. Mr Sack's description was during a video on execution plans. And I stayed at a Holiday Inn Express. It sure sounded the same. What tells you it might not be?
SQLkiwi 2014-08-14 06:51:49
The predicate pushdown issue usually results in a particular plan shape, often with a Filter 'stuck' at a certain point. I'm not sure how the Holiday Inn Express comes into this (reason for supplying an anonymized plan maybe?)
SQLkiwi 2014-08-14 12:23:37
"No it is a joke from a commercial in the US" < Ah, I'm in New Zealand 🙂
August 11, 2014
No Comments