Re: replace subquery with left join

From: Igor Korolyov <k1i2v3_at_km.ru>
Date: Thu, 7 Nov 2002 00:05:13 +0200
Message-ID: <uGBA2mehCHA.1368_at_tkmsftngp09>

Hi, Hans!
You wrote on Wed, 06 Nov 2002 17:14:27 +0100:

Such kind of queries MUST be slow - your server must execute subquery for each record in main query. So in your case - server must execute subquery 24000 times. Of course it IS slowly. If you can create cursor (or maybe temporary table) in remote side (on SQL server) you can improve performance very much. Just select appropriate records from pricelist table on first stage, and then make left join with items table. something like this.

1. SELECT ItemId, MAX(ValDate) AS dLastDate From PriceList WHERE (ValDate limit condition and other PriceList limit conditions) GROUP BY ItemId INTO CURSOR tmp1
2. SELECT Item.ItemId, Item.OtherData, PriceList.Price FROM Item LEFT JOIN tmp1 ON Item.ItemId = tmp1.ItemId LEFT JOIN PriceList ON PriceList.ItemId = tmp1.ItemId AND PriceList.ValDate = tmp1.dLastDate WHERE (Item limit condition if have such)

 HS> I've tested all solutions. Isaac's solution doesn't work on DB2  HS> (maybe the reference to P is not allowed in the subquery).

 HS> The performance is still very poor. Maybe it's because the
 HS> prices-table is too big and complicated. (But I can't change it:
 HS> it's from our ERP). Let me explain:

 HS> The relevant fields are:

 HS> hrkn      CHAR(10)  hierachyknot (=branch)
 HS> prlist    CHAR(8)   pricelist valdate   DEC(5,0)  valid date cur
 HS> CHAR(4)   currency itemno    CHAR(20)  itemnumber prline    CHAR(2)
 HS> priceline scale     DEC(9,3)  scale

 HS> So each branch can have several pricelists and pricelines in several  HS> currencies for many quantities.

 HS> I rewrote Mike's solution to:

 HS> SELECT I.ItemNo, P.Price
 HS>  FROM items AS I
 HS>    LEFT JOIN prices AS P
 HS>      ON  '0101'     = P.HrKn
 HS>      AND '      01' = P.PrList
 HS>      AND 'EUR'      = P.Cur
 HS>      AND I.ItemNo   = P.ItemNo
 HS>      AND '01'       = P.PrLine
 HS>      AND 0          = P.Scale
 HS>  WHERE P.ValDate = (SELECT MAX(ValDate)
 HS>                      FROM prices
 HS>                      WHERE HrKn    =  P.HrKn
 HS>                        AND PrList  =  P.PrList
 HS>                        AND ValDate <= (DAYS(CURDATE())-693594)
 HS>                        AND Cur     =  P.Cur
 HS>                        AND ItemNo  =  P.ItemNo
 HS>                        AND PrLine  =  P.PrLine
 HS>                        AND Scale   =  P.Scale)

 HS> (At the moment I don't care about the NULLs ;-) and I don't think

 HS> it's necassary to group by date like Anders suggested, because the  HS> ERP-System prohibits inputs for the same date)

 HS> It's awfully slow:
 HS> There are about 24000 records in the items-table and about 170000
 HS> records in the prices-table and a query lasts circa 50 min!

 HS> -Hans

WBR, Igor Received on Wed Nov 06 2002 - 23:05:13 CET