Re: replace subquery with left join

What version of DB2 are you on?

Regards
Paul Vernon
Business Intelligence, IBM Global Services

"Hans Sieghart" <Hans.Sieghart_at_frischeis.at.nospam> wrote in message news:9bhksu8mfeo8iegdn8juev79hhum6veg8f_at_4ax.com...
> Moritz Franckenstein <maf-soft_at_gmx.net> wrote:
> >Hans Sieghart <Hans.Sieghart_at_frischeis.at.nospam> wrote:
> > > I've tested all solutions.
> >
> > did you really try my solution? was it wrong?
>
> Yes, I did try. I rewrote it to:
>
> SELECT I.ItemNo, P.Price
> FROM items AS I
> LEFT JOIN (
> SELECT ItemNo, Price
> FROM prices AS P1
> WHERE HrKn = '0101'
> AND PrList = ' 01'
> AND Cur = 'EUR'
> AND PrLine = '01'
> AND Scale = 0
> AND ValDate = (
> SELECT MAX(ValDate)
> FROM prices
> WHERE HrKn = P1.HrKn
> AND PrList = P1.PrList
> AND ValDate <= (DAYS(CURDATE())-693594)
> AND Cur = P1.Cur
> AND ItemNo = P1.ItemNo
> AND PrLine = P1.PrLine
> AND Scale = P1.Scale
> )
> ) AS P
> ON I.ItemNo = P.ItemNo
>
> It isn't wrong, but also lasts about 50 mins.
>
> > > It's awfully slow:
> > > There are about 24000 records in the items-table and about
> > > 170000 records in the prices-table and a query lasts circa
> > > 50 min!
> >
> > of course, you need good indices (or indexes?)... this amount
> > of data should normally be no problem.
>
> Well, I take it as it is. I'm just querying.
>
> -Hans
Received on Thu Nov 07 2002 - 17:15:04 CET