Re: Tuning query

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3dec8462$0$706$ed9e5944_at_reading.news.pipex.net...
> Learn to use your posting software Niall!
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3deb7b2a$0$711$ed9e5944_at_reading.news.pipex.net...
> > I have two (very) similar queries which execute in radically different
> > timescales.
> <snip detail>
>
> What I should have added was that (unless I can't read) the execution
plans
> are the same, which isn't a huge surprise because the queries are actually
> designed to return the same set of data (there aren't any values in
> AAGDAENQ3 for clients other than 'AC'). What is confusing me a bit is why
> the second query should visit an order of magnitude more blocks (4million
> not 300k).
>

Hi Niall,

There is one little difference between the two plans (which may not be so little).

The "good" plan uses a hash join in line 4 while the "suz" plan uses a nested loop. Although the costs associated with each path look similar, I have seen such differences make quite a difference although there are obviously a lot of if and buts with it all.

As suggested by Billy and Rajxesh, I would take a close look at seeing if you can force the second plan to use the hash join (via hint) and at least get identical plans. If problem goes away, you know that was it.

Cheers

Richard Received on Tue Dec 03 2002 - 07:38:16 CST