Re: replace subquery with left join

From: Hans Sieghart <Hans.Sieghart_at_frischeis.at.nospam>
Date: Thu, 07 Nov 2002 12:15:55 +0100
Message-ID: <9bhksu8mfeo8iegdn8juev79hhum6veg8f@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 - 05:15:55 CST