Possible to optimize my query? 1

[beckwiga]

Hello everyone!

My issue:

My query executes fine but is very slow. I'm trying to understand if there is another way I could structure it to be more efficient. I am using Access 2003.

My query:

SELECT a.InvcDte, a.CustomerId, a.ItemId, b.PkgSalesSupvId, b.DftSalesSupvId, b.PkgSalesRepId, b.DftSalesRepId, b.PkgDriverId, b.DftDriverId, b.MiscSalesRepId, a.DlvryQty, a.UnitPriceAmt, a.TtlDiscAmt, a.PriceLineCde, a.InventoryActionCde, a.OrderQty,
(SELECT TOP 1 up_com_price_01 FROM unitprice c WHERE c.up_item = a.ItemId AND c.up_priceline = a.PriceLineCde AND a.InvcDte >= c.up_eff_dte) AS 1,
(SELECT TOP 1 up_com_price_07 FROM unitprice c WHERE c.up_item = a.ItemId AND c.up_priceline = a.PriceLineCde AND a.InvcDte >= c.up_eff_dte) AS 7,
(SELECT TOP 1 up_com_price_02 FROM unitprice c WHERE c.up_item = a.ItemId AND c.up_priceline = a.PriceLineCde AND a.InvcDte >= c.up_eff_dte) AS 2,
(SELECT TOP 1 up_com_price_03 FROM unitprice c WHERE c.up_item = a.ItemId AND c.up_priceline = a.PriceLineCde AND a.InvcDte >= c.up_eff_dte) AS 3,
(SELECT TOP 1 up_com_price_04 FROM unitprice c WHERE c.up_item = a.ItemId AND c.up_priceline = a.PriceLineCde AND a.InvcDte >= c.up_eff_dte) AS 4,
(SELECT TOP 1 up_com_price_10 FROM unitprice c WHERE c.up_item = a.ItemId AND c.up_priceline = a.PriceLineCde AND a.InvcDte >= c.up_eff_dte) AS 10,
(SELECT TOP 1 up_com_price_11 FROM unitprice c WHERE c.up_item = a.ItemId AND c.up_priceline = a.PriceLineCde AND a.InvcDte >= c.up_eff_dte) AS 11
FROM CURRENT_MTD_DLY_SLS_DTL AS a
LEFT JOIN CUSTOMER_PRODUCT AS b ON a.CustomerId=b.CustomerId
WHERE a.DlvryQty>=0;

My question:

I'm hoping someone can help me understand if it is possible to optimize this query. My query runs fine but take a very long time to execute and I'm assuming this is because I have several Subselects. I thought about LEFT JOINing the unitprice table, which would be fine if I were just joining ON Item and Priceline code, but there are multiple entries in the unitprice table (for items and priceline codes) because effective dates are used (multiple dates). This is why I am using the TOP 1 in the subselect. Maybe I could do it with a correlated subquery but I'm not sure...

Any help would be greatly appreciated.

beckwiga

 

[PWise]

try

SELECT a.InvcDte, a.CustomerId, a.ItemId, b.PkgSalesSupvId, b.DftSalesSupvId, b.PkgSalesRepId,
 b.DftSalesRepId, b.PkgDriverId, b.DftDriverId, b.MiscSalesRepId, a.DlvryQty, a.UnitPriceAmt,
 a.TtlDiscAmt, a.PriceLineCde, a.InventoryActionCde, a.OrderQty, unitprice.*
FROM CURRENT_MTD_DLY_SLS_DTL AS a
inner join 
          (SELECT TOP 1 up_com_price_01,up_com_price_07,up_com_price_02, up_com_price_03,up_com_price_04,up_com_price_10,up_com_price_11
           FROM unitprice c
           ) as unitprice
on unitprice.up_item = a.ItemId 
AND unitprice.up_priceline = a.PriceLineCde 
AND a.InvcDte >= unitprice.up_eff_dte
And a.DlvryQty>=0
LEFT JOIN CUSTOMER_PRODUCT AS b 
ON a.CustomerId=b.CustomerId;

 

[PWise]

sorry s/b

SELECT a.InvcDte, a.CustomerId, a.ItemId, b.PkgSalesSupvId, b.DftSalesSupvId, b.PkgSalesRepId,
 b.DftSalesRepId, b.PkgDriverId, b.DftDriverId, b.MiscSalesRepId, a.DlvryQty, a.UnitPriceAmt,
 a.TtlDiscAmt, a.PriceLineCde, a.InventoryActionCde, a.OrderQty, unitprice.*
FROM CURRENT_MTD_DLY_SLS_DTL AS a
inner join 
          (SELECT TOP 1 up_item,up_priceline,up_eff_dte,up_com_price_01,up_com_price_07,up_com_price_02, up_com_price_03,up_com_price_04,up_com_price_10,up_com_price_11
           FROM unitprice 
           ) as unitprice
on unitprice.up_item = a.ItemId 
AND unitprice.up_priceline = a.PriceLineCde 
AND a.InvcDte >= unitprice.up_eff_dte
And a.DlvryQty>=0
LEFT JOIN CUSTOMER_PRODUCT AS b 
ON a.CustomerId=b.CustomerId;