Posted by Gert-Jan Strik on 01/12/07 21:16

I don't have recommendations, but you shouldn't have any problems
googling them.

Reading query plans is not easy. Besides "gettings used to", it requires
a lot of knowledge of how the query optimizer works, what execution
steps seem reasonable, and which seem wasteful. The part to force a
different join type is actually quite simple. You can look up query
hints in BOL to get an overview of the possibilities. I guess the rest
is 10+ years experience and a particular interest in performance related
issues...

Gert-Jan


Emin wrote:
>
> Dear Gert-Jan,
>
> Thanks for the info. In my real-world problem I am doing a join on
> views which seemed appropriate to simulate using CTEs in my previous
> post. Based on your comments, I tried using OPTION(HASH JOIN) on my
> real example and that fixed the problem.
>
> Out of curiousity, how does one go about learning how to understand and
> optimize the execution plan? For example, while I noticed that the
> execution plans were different in my various cases, I never would have
> known to try OPTION(HASH JOIN). Do you (or any other readers out there)
> have recommendations on books or other sources for this information?
>
> Thanks again,
> -Emin
>
> On Jan 12, 2:59 pm, Gert-Jan Strik <s...@toomuchspamalready.nl> wrote:
> > Does your real situation actually resemble this repro?
> >
> > In the repro, it is the difference between a hash join and loop join.
> > The hash join is fast, the loop join is not. It is not suprising that
> > the loop join is not very fast, because there is quite a lot of
> > calculation needed for the CTE.
> >
> > If this really is your problem, then you can probably add OPTION(HASH
> > JOIN) to eleviate the performance problems.
> >
> > But if you are not actually joining two CTE's with deep recursion, then
> > we probably need a more real life example.
> >
> > Gert-Jan
[snip]

[Back to original message]