Re: replace subquery with left join

Mike Sherrill <MSherrill_at_compuserve.com> wrote:
> On Wed, 06 Nov 2002 17:14:27 +0100, Hans Sieghart
> <Hans.Sieghart_at_frischeis.at.nospam> wrote:
>
> > I've tested all solutions. Isaac's solution doesn't work
> > on DB2 (maybe the reference to P is not allowed in the
> > subquery).
>
> Isaac and I posted essentially the same thing. The reference to P
> should be allowed; P is in an outer scope. I don't think you can
> eliminate the subquery.

I don't know. I don't have the latest DB2-Version. I see that your solutions are almost the same. But it works in WHERE and doesn't work in JOIN...

> > The performance is still very poor. Maybe it's because
> > the prices-table is too big and complicated. (But I
> > can't change it: it's from our ERP). Let me explain:
>
> How long does it take to fetch the current price for a single item?

Just like in your example: For a single item the query lasts < 1 sec.

> For testing, can you replace the expression (DAYS(CURDATE())-693594)
> with a constant? (I'd expect DB2 to evaluate that expression only
> once, but maybe I'm being foolishly optimistic.)

That doesn't help very much: I started it 20 mins ago and it is now at record number 5000...

> If you can create indexes, can you create an index on each column
> used in the WHERE clauses?

I'm not an DB2-expert and I don't want to disturb the ERP-System.

-Hans Received on Thu Nov 07 2002 - 17:51:12 CET