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-02 : 01:35:37

This query is take time to load data. I need to optimise this query.

CREATE VIEW dbo.vw_EnquiryExtrusionMain
AS
SELECT EE.BranchID, EE.PartId, EE.WarehouseID, EE.Length, EE.InventoryTypeID, EE.FinishID, EE.StockTypeID, EE.BranchPartID, EE.StockQty,
EE.ReservedQty, EE.ExtCode, EE.FinishCode, EE.ExtDesc, EE.MinReOrderQty, EE.CriticalStockLevel, EE.BranchFinishType, EE.StockType,
EE.Obsolete, EE.FinishDescription, EE.BranchFinishID, EE.BranchFinishTypeID, EE.UnAllocatedQty, EE.WarehouseLocationID, EE.BranchOwnPart,
EE.Status, ISNULL(POQ.PurchaseOrderQty - POQ.PurchaseReceivedQty, 0) AS OnOrderQty,

CASE WHEN isnull(UQ.ReqQty, 0) > 0 THEN
Case when isnull(UQ.UnReservedQty, 0)>0 then
(isnull(UQ.ReqQty, 0)+ isnull(UQ.UnReservedQty, 0)) - isnull(EE.ReservedQty,0)
Else
isnull(UQ.ReqQty, 0) -isnull(EE.ReservedQty,0)
End
ELSE isnull(UQ.UnReservedQty, 0)- isnull(EE.ReservedQty,0) END AS UnReservedQty,

ISNULL(MRR.ManualReservedQty, 0) AS ManualReservedQty,
ISNULL(PREQ.NoofLengthsRequired, 0) AS PreOptimiseQty,
CASE WHEN EE.WarehouseLocationID = - 2 THEN 'MULTI' ELSE WLoc.WarehouseLocation END AS WarehouseLocation,

CASE WHEN isnull(UQ.ReqQty, 0) > 0 THEN
Case when isnull(UQ.UnReservedQty, 0)>0 then
isnull(UQ.ReqQty, 0)+ isnull(UQ.UnReservedQty, 0)
Else
isnull(UQ.ReqQty, 0)
End
ELSE isnull(UQ.UnReservedQty, 0) END AS ReqQty

FROM dbo.vw_EnquiryExtrusion EE LEFT OUTER JOIN
dbo.vw_UnreservedQty UQ ON EE.BranchID = UQ.BranchID AND EE.PartId = UQ.TIMSPartID AND EE.FinishID = UQ.TIMSFinishID AND
EE.Length = UQ.TIMSPartLength AND EE.BranchOwnPart = UQ.BranchOwnPart AND EE.InventoryTypeID = UQ.TIMSInventoryTypeID LEFT OUTER JOIN
dbo.WarehouseLocation WLoc ON ISNULL(EE.WarehouseLocationID, 0) = ISNULL(WLoc.WarehouseLocationID, 0) AND
EE.BranchID = WLoc.BranchID AND ISNULL(EE.WarehouseID, 0) = ISNULL(WLoc.WarehouseID, 0) LEFT OUTER JOIN
dbo.vw_ManualReservedQty MRR ON EE.PartId = MRR.partID AND EE.BranchID = MRR.BranchID AND EE.FinishID = MRR.FinishID AND
EE.Length = MRR.length AND EE.BranchOwnPart = MRR.BranchOwn AND EE.InventoryTypeID = MRR.InventoryTypeID LEFT OUTER JOIN
dbo.vw_GetPreOptimisedQty PREQ ON EE.PartId = PREQ.TIMSPartID AND EE.BranchID = PREQ.BranchID AND EE.Length = PREQ.TIMSPartLength AND
EE.FinishID = PREQ.TIMSFinishID AND EE.InventoryTypeID = PREQ.TIMSInventoryTypeID LEFT OUTER JOIN
dbo.vw_CalculatePurchaseQtyWithOutPOldNo POQ ON EE.FinishID = POQ.TIMSFinishID AND EE.Length = POQ.TIMSPartLength AND
EE.BranchOwnPart = POQ.BranchOwn AND EE.InventoryTypeID = POQ.TIMSInventoryTypeID AND EE.PartId = POQ.TIMSPartID AND
EE.BranchID = POQ.BranchID

-------------------------------------------------------------------

CREATE VIEW dbo.vw_EnquiryExtrusion
AS
SELECT PartId, BranchID, isnull(WarehouseID,0) WarehouseID, Length, InventoryTypeID, FinishID, StockTypeID, BranchPartID, isnull(SUM(StockQty),0) AS StockQty, isnull(SUM(ReservedQty) ,0)
AS ReservedQty, ExtCode, FinishCode, ExtDesc, MinReOrderQty, CriticalStockLevel, BranchFinishType, isnull(StockType,0) StockType, Obsolete, FinishDescription,
BranchFinishID, BranchFinishTypeID, isnull(SUM(StockQty) - SUM(ReservedQty),0) AS UnAllocatedQty, isnull(WarehouseLocationID,0) WarehouseLocationID, BranchOwnPart,
CASE WHEN (SUM(StockQty) - CriticalStockLevel) > 0 THEN 1 ELSE 1 END AS Status
FROM (SELECT BP.PartId, BP.BranchID, WS.WarehouseID, BE.Length, BP.InventoryTypeID, BP.FinishID, ISNULL(WS.StockTypeID, 1) AS StockTypeID,
BP.BranchPartID, SUM(WS.StockQty) AS StockQty, SUM(WS.ReservedQty) AS ReservedQty, TP.PartCode AS ExtCode, TF.FinishCode,
TP.PartDescription AS ExtDesc, BE.MinReOrderQty, BE.CriticalStockLevel, BFT.BranchFinishType, ST.StockType, BE.Obsolete,
TF.FinishDescription, BF.BranchFinishID, BFT.BranchFinishTypeID, CASE WHEN
(SELECT COUNT(*) AS TCOUNT
FROM vw_WarehouseStockASGroupBy
WHERE BranchID = BP.BranchID AND StockID = BP.PartID AND FinishID = BP.FinishID AND
InventoryTYpeID = BP.InventoryTypeID AND BranchOwnStock = BP.BranchOwnPart AND Length = BE.Length)
> 1 THEN - 2 ELSE WS.WarehouseLocationID END AS WarehouseLocationID, BP.BranchOwnPart
FROM dbo.BranchPart BP LEFT OUTER JOIN
dbo.BranchExtrusion BE ON BP.BranchID = BE.BranchID AND BP.BranchPartID = BE.BranchPartID AND BP.FinishID = BE.FinishID AND
BP.PartId = BE.PartID LEFT OUTER JOIN
dbo.WarehouseStock WS ON BP.BranchID = WS.BranchID AND BP.PartId = WS.StockID AND BP.BranchPartID = WS.BranchStockID AND
BP.FinishID = WS.FinishID AND BP.InventoryTypeID = WS.InventoryTypeID AND BP.BranchOwnPart = WS.BranchOwnStock AND
BE.Length = WS.Length LEFT OUTER JOIN
dbo.BranchFinish BF ON BP.BranchID = BF.BranchID AND BP.FinishID = BF.FinishID LEFT OUTER JOIN
dbo.BranchFinishType BFT ON BF.BranchFinishTypeID = BFT.BranchFinishTypeID LEFT OUTER JOIN
dbo.StockType ST ON WS.StockTypeID = ST.StockTypeID LEFT OUTER JOIN
dbo.TrendPart TP ON BP.PartId = TP.PartID AND BP.InventoryTypeID = TP.InventoryTypeID LEFT OUTER JOIN
dbo.TrendFinish TF ON TF.FinishID = BP.FinishID
WHERE (BP.InventoryTypeID = 1)
GROUP BY BP.PartId, BP.BranchID, WS.WarehouseID, BE.Length, BP.InventoryTypeID, BP.FinishID, WS.StockTypeID, BP.BranchPartID, TP.PartCode,
TF.FinishCode, TP.PartDescription, BE.MinReOrderQty, BE.CriticalStockLevel, BFT.BranchFinishType, ST.StockType, BE.Obsolete,
BP.BranchPartID, TF.FinishDescription, BF.BranchFinishID, BFT.BranchFinishTypeID, BP.BranchOwnPart, BF.BranchFinishTypeID,
WS.WarehouseLocationID) DERIVEDTBL
GROUP BY PartId, BranchID, WarehouseID, Length, InventoryTypeID, FinishID, StockTypeID, BranchPartID, ExtCode, FinishCode, ExtDesc, MinReOrderQty,
CriticalStockLevel, BranchFinishType, StockType, Obsolete, BranchPartID, FinishDescription, BranchFinishID, BranchFinishTypeID, BranchOwnPart,
BranchFinishTypeID, WarehouseLocationID

----------------------------------------------------------------

CREATE VIEW dbo.vw_UnreservedQty
AS
SELECT BranchID, TIMSInventoryTypeID, BranchOwnPart, TIMSPartID, Sum(UnreservedQty) UnreservedQty, SUM(ReqQty) AS ReqQty,
TIMSFinishID, TIMSPartLength, TIMSFinishTypeID FROM
(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 UnreservedQty,0 As ReqQty, MOB.TIMSFinishID, MOB.TIMSFinishTypeID, MOB.MACSOrderNo
FROM dbo.MACSOrderlineBom MOB Left JOIN dbo.MACSOrderHead MOH ON
MOB.BranchID = MOH.BranchID AND MOB.MACSOrderNo = MOH.MACSOrderNo
WHERE Mob.IsPreOrdered=0 And (MOB.TIMSInventoryTypeID IN (1, 2, 3)) AND (MOB.TIMSPartID <> 0)
AND (MOH.ReservationRun = 1) AND (MOB.TIMSOrderStatus < 6) AND (ISNULL(MOB.IsOffCut, 0) = 0)
--AND (MOB.TIMSFinishTypeID = 1)
GROUP BY MOB.BranchID, MOB.TIMSInventoryTypeID, MOB.BranchOwnPart, MOB.TIMSPartID, MOB.TIMSFinishID, MOB.TIMSPartLength,
MOB.TIMSFinishTypeID, MOB.MACSOrderNo
Union All
SELECT MOB.BranchID, MOB.TIMSInventoryTypeID, MOB.BranchOwnPart, MOB.TIMSPartID, MOB.TIMSPartLength, 0 As UnReservedQty,
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, '' As MACSOrderNo
FROM dbo.MACSOrderlineBom MOB Left JOIN dbo.MACSOrderHead MOH ON
MOB.BranchID = MOH.BranchID AND MOB.MACSOrderNo = MOH.MACSOrderNo
WHERE Mob.IsPreOrdered=1 And (MOB.TIMSInventoryTypeID IN (1, 2, 3)) AND (MOB.TIMSPartID <> 0)
AND (MOH.ReservationRun = 1) AND (ISNULL(MOB.IsOffCut, 0) = 0) AND (MOB.TIMSFinishTypeID = 2)
AND (MOB.TIMSOrderStatus < 6)
GROUP BY MOB.BranchID, MOB.TIMSInventoryTypeID, MOB.BranchOwnPart, MOB.TIMSPartID, MOB.TIMSFinishID, MOB.TIMSPartLength,
MOB.TIMSFinishTypeID
) As Table1
GROUP BY BranchID, TIMSInventoryTypeID, BranchOwnPart, TIMSPartID, TIMSFinishID, TIMSPartLength, TIMSFinishTypeID

-------------------------------------------------------------

CREATE VIEW dbo.vw_ManualReservedQty
AS
SELECT BranchID, InventoryTypeID, partID, FinishID, length, SUM(ReservedQty) AS ManualReservedQty, BranchOwn
FROM dbo.ManualResourceReservation MRR
GROUP BY BranchID, InventoryTypeID, BranchOwn, partID, FinishID, length

---------------------------------------------------------------

CREATE VIEW dbo.vw_GetPreOptimisedQty
AS
SELECT SUM(dbo.PreOptimiseOutput.NoOfLengthsRequired) AS NoofLengthsRequired, dbo.PreOptimiseOutput.BranchID,
dbo.PreOptimiseOutput.TIMSInventoryTypeID, dbo.PreOptimiseOutput.TIMSPartID, dbo.PreOptimiseOutput.TIMSPartLength,
dbo.PreOptimiseOutput.TIMSFinishID
FROM dbo.PreOptimiseOutput INNER JOIN
dbo.MACSOrderHead ON dbo.PreOptimiseOutput.BranchID = dbo.MACSOrderHead.BranchID AND
dbo.PreOptimiseOutput.MACSOrderNo = dbo.MACSOrderHead.MACSOrderNo
WHERE (dbo.MACSOrderHead.TIMSOrderStatus < 6)
GROUP BY dbo.PreOptimiseOutput.BranchID, dbo.PreOptimiseOutput.TIMSInventoryTypeID, dbo.PreOptimiseOutput.TIMSPartID,
dbo.PreOptimiseOutput.TIMSPartLength, dbo.PreOptimiseOutput.TIMSFinishID

-------------------------------------------------------------

CREATE VIEW dbo.vw_CalculatePurchaseQtyWithOutPOldNo
AS
SELECT BranchID, TIMSInventoryTypeID, TIMSPartID, TIMSPartLength, TIMSFinishID, SUM(PurchaseOrderQty) AS PurchaseOrderQty, BranchOwn,
TIMSPurchaseOrderStatus, SUM(PurchaseReceivedQty) AS PurchaseReceivedQty
FROM (SELECT pol.BranchID, pol.PurchaseOrderNo, pol.PurchaseOrderLineNo, pol.MACSOrderNo, pol.CustomerCode, pol.TIMSInventoryTypeID,
pol.TIMSPartID, pol.TIMSPartLength, pol.TIMSFinishID, pol.PurchaseOrderQty, pol.TIMSFinishTypeID, poh.SupplierID, poh.BranchOwn,
poh.InventoryTypeID, poh.TIMSPurchaseOrderStatus, poh.PurchaseOrderDate, poh.OptimiseRefNo, pol.PurchaseReceivedQty
FROM dbo.PurchaseOrderHead poh INNER JOIN
dbo.PurchaseOrderLine pol ON poh.BranchID = pol.BranchID AND poh.PurchaseOrderNo = pol.PurchaseOrderNo
WHERE (poh.TIMSPurchaseOrderStatus = 1) AND (pol.PurchaseOrderQty - pol.PurchaseReceivedQty) > 0) DERIVEDTBL
GROUP BY BranchID, TIMSInventoryTypeID, TIMSPartID, TIMSPartLength, TIMSFinishID, BranchOwn, TIMSPurchaseOrderStatus

----------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WarehouseLocation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WarehouseLocation]
GO

CREATE TABLE [dbo].[WarehouseLocation] (
[WarehouseLocationID] [int] NOT NULL ,
[WarehouseID] [int] NOT NULL ,
[BranchID] [int] NOT NULL ,
[WarehouseLocation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Obsolete] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WarehouseLocation] WITH NOCHECK ADD
CONSTRAINT [PK_WarehouseLocation] PRIMARY KEY CLUSTERED
(
[WarehouseLocationID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[WarehouseLocation] ADD
CONSTRAINT [DF_WarehouseLocation_Obsolete] DEFAULT (0) FOR [Obsolete]
GO

CREATE UNIQUE INDEX [Idx_WarehouseLocation] ON [dbo].[WarehouseLocation]([WarehouseLocationID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

Tamalendu Nath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources