Optimise Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Optimise Query

Author

Topic

Tamalendu
Starting Member

2 Posts

Posted - 2009-07-16 : 03:51:18

SELECT MOB.BranchID, MOB.TIMSInventoryTypeID, MOB.BranchOwnPart, MOB.TIMSPartID, MOB.TIMSPartLength, CASE WHEN (IsNull(MOB.TIMSPartLength,
0)) > 0 THEN CEILING(CAST(SUM(MOB.cut_length * MOB.quantity) AS Decimal(18, 6)) / CAST(MOB.TIMSPartLength AS Decimal(18, 6)))
ELSE 0 END AS ReqQty, MOB.TIMSFinishID, MOB.TIMSFinishTypeID, MOH.MACSOrderNo, ISNULL(r.ReservedQty, 0) AS ReservedQty,
ISNULL(WHS.StockQty, 0) AS StockQty, ISNULL(OOQ.PurchaseOrderQty, 0) - ISNULL(OOQ.PurchaseReceivedQty, 0) AS OnOrderQty
FROM dbo.MACSOrderlineBom MOB LEFT OUTER JOIN
dbo.MACSOrderHead MOH ON MOB.MACSOrderNo = MOH.MACSOrderNo AND MOB.BranchID = MOH.BranchID LEFT OUTER JOIN
dbo.vw_CalculatePurchaseQtyWithOutPOldNo OOQ ON MOB.BranchID = OOQ.BranchID AND
MOB.TIMSInventoryTypeID = OOQ.TIMSInventoryTypeID AND MOB.TIMSPartID = OOQ.TIMSPartID AND
MOB.TIMSPartLength = OOQ.TIMSPartLength AND MOB.TIMSFinishID = OOQ.TIMSFinishID AND MOB.BranchOwnPart = OOQ.BranchOwn AND
ISNULL(OOQ.PurchaseOrderQty, 0) - ISNULL(OOQ.PurchaseReceivedQty, 0) > 0 LEFT OUTER JOIN
dbo.vw_WarehouseStockASGroupBy WHS ON MOB.BranchID = WHS.BranchID AND MOB.TIMSPartID = WHS.StockID AND
MOB.TIMSPartLength = WHS.Length AND MOB.TIMSFinishID = WHS.FinishID AND MOB.TIMSInventoryTypeID = WHS.InventoryTypeID AND
MOB.BranchOwnPart = WHS.BranchOwnStock LEFT OUTER JOIN
dbo.vw_ReservationConsolidated r ON MOB.TIMSInventoryTypeID = r.TIMSInventoryTypeID AND MOB.BranchID = r.BranchID AND
MOB.MACSOrderNo = r.MACSOrderNo AND MOB.TIMSPartID = r.TIMSPartID AND ISNULL(MOB.TIMSFinishID, 0) = ISNULL(r.TIMSFinishID, 0) AND
MOB.TIMSPartLength = r.Length AND MOB.BranchOwnPart = r.BranchOwn AND r.IsOffcut = 0
WHERE (MOB.TIMSPartID <> 0) AND (MOB.TIMSOrderStatus < 6) AND (MOB.TIMSInventoryTypeID IN (1, 2, 3)) AND (MOH.ReservationRun = 1) AND
(ISNULL(MOB.IsOffCut, 0) = 0) AND (ISNULL(MOB.TIMSFinishTypeID, 0) IN (0, 1))
GROUP BY MOB.BranchID, MOB.TIMSInventoryTypeID, MOB.BranchOwnPart, MOB.TIMSPartID, MOB.TIMSFinishID, MOB.TIMSFinishTypeID,
MOB.TIMSPartLength, MOB.TIMSFinishTypeID, MOH.MACSOrderNo, r.ReservedQty, WHS.StockQty, OOQ.PurchaseOrderQty,
OOQ.PurchaseReceivedQty

Tamalendu Nath

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 04:01:01

You cannot hope that anybody helps with that non readable post!
I have beautyfied it a little

SELECT   mob.branchid, 
         mob.timsinventorytypeid, 
         mob.branchownpart, 
         mob.timspartid, 
         mob.timspartlength, 
         CASE 
           WHEN (Isnull(mob.timspartlength,0)) > 0 
           THEN Ceiling(Cast(Sum(mob.cut_length * mob.quantity) AS DECIMAL(18,6)) / Cast(mob.timspartlength AS DECIMAL(18,6))) 
           ELSE 0 
         END AS reqqty, 
         mob.timsfinishid, 
         mob.timsfinishtypeid, 
         moh.macsorderno, 
         Isnull(r.reservedqty,0)                                            AS reservedqty, 
         Isnull(whs.stockqty,0)                                             AS stockqty, 
         Isnull(ooq.purchaseorderqty,0) - Isnull(ooq.purchasereceivedqty,0) AS onorderqty 
FROM     dbo.macsorderlinebom mob 
         LEFT OUTER JOIN dbo.macsorderhead moh 
           ON mob.macsorderno = moh.macsorderno 
              AND mob.branchid = moh.branchid 
         LEFT OUTER JOIN dbo.vw_calculatepurchaseqtywithoutpoldno ooq 
           ON mob.branchid = ooq.branchid 
              AND mob.timsinventorytypeid = ooq.timsinventorytypeid 
              AND mob.timspartid = ooq.timspartid 
              AND mob.timspartlength = ooq.timspartlength 
              AND mob.timsfinishid = ooq.timsfinishid 
              AND mob.branchownpart = ooq.branchown 
              AND Isnull(ooq.purchaseorderqty,0) - Isnull(ooq.purchasereceivedqty,0) > 0 
         LEFT OUTER JOIN dbo.vw_warehousestockasgroupby whs 
           ON mob.branchid = whs.branchid 
              AND mob.timspartid = whs.stockid 
              AND mob.timspartlength = whs.length 
              AND mob.timsfinishid = whs.finishid 
              AND mob.timsinventorytypeid = whs.inventorytypeid 
              AND mob.branchownpart = whs.branchownstock 
         LEFT OUTER JOIN dbo.vw_reservationconsolidated r 
           ON mob.timsinventorytypeid = r.timsinventorytypeid 
              AND mob.branchid = r.branchid 
              AND mob.macsorderno = r.macsorderno 
              AND mob.timspartid = r.timspartid 
              AND Isnull(mob.timsfinishid,0) = Isnull(r.timsfinishid,0) 
              AND mob.timspartlength = r.length 
              AND mob.branchownpart = r.branchown 
              AND r.isoffcut = 0 
WHERE    (mob.timspartid <> 0) 
         AND (mob.timsorderstatus < 6) 
         AND (mob.timsinventorytypeid IN (1,2,3)) 
         AND (moh.reservationrun = 1) 
         AND (Isnull(mob.isoffcut,0) = 0) 
         AND (Isnull(mob.timsfinishtypeid,0) IN (0,1)) 
GROUP BY mob.branchid, 
         mob.timsinventorytypeid, 
         mob.branchownpart, 
         mob.timspartid, 
         mob.timsfinishid, 
         mob.timsfinishtypeid, 
         mob.timspartlength, 
         mob.timsfinishtypeid, 
         moh.macsorderno, 
         r.reservedqty, 
         whs.stockqty, 
         ooq.purchaseorderqty, 
         ooq.purchasereceivedqty

No, you're never too old to Yak'n'Roll if you're too young to die.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-16 : 04:13:11

Hi

Query contains lot of Left Outer Join. So, better use temp table.

Avoid using functions in the joins (For Ex. AND ISNULL(OOQ.PurchaseOrderQty, 0) - ISNULL(OOQ.PurchaseReceivedQty, 0)).

We should Limit Using Outer Joins

I explain here. The Query Processor OUTER JOINs are treated differently from INNER JOINs in that the optimizer does not try to rearrange the join order of OUTER JOIN tables as it does to INNER JOIN tables. The outer table (the left table in LEFT OUTER JOIN and the right table in RIGHT OUTER JOIN) is accessed first, followed by the inner table. This fixed join order could lead to execution plans that are less than optimal.

-------------------------
R..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources