Share
ReportDoes anyone have issues with the follwing script running at HQ SP4. This runs when one works on a PO. Each time i access an item on the PO, there is a serious lag in having the data populate in the new fields introduced in FP1.
SELECT
ItemID,
PurchaseOrderEntry.StoreID,
SUM(CASE PoType
WHEN 0 THEN QuantityOrdered - QuantityReceivedToDate
WHEN 1 THEN QuantityOrdered - QuantityReceivedToDate
WHEN 2 THEN QuantityOrdered - QuantityReceivedToDate
WHEN 3 THEN -QuantityOrdered + QuantityReceivedToDate
WHEN 4 THEN QuantityOrdered - QuantityReceivedToDate
WHEN 5 THEN -QuantityOrdered + QuantityReceivedToDate
END) As OnOrder
INTO #TempQuantity
FROM PurchaseOrderEntry
INNER JOIN PurchaseOrder ON PurchaseOrder.ID = PurchaseOrderEntry.PurchaseOrderID and PurchaseOrderEntry.ItemID = 78302
AND Purchaseorder.StoreID = PurchaseOrderEntry.StoreID AND (InventoryLocation <> 1)
GROUP BY ItemID, PurchaseOrderEntry.StoreID
UPDATE #TempOrder1
SET QuantityOrdered =
CASE WHEN ItemDynamic.Quantity - ItemDynamic.QuantityCommitted <= ItemDynamic.ReorderPoint THEN
ISNULL(ItemDynamic.RestockLevel - (ItemDynamic.Quantity - ItemDynamic.QuantityCommitted + ISNULL(#TempQuantity.OnOrder, 0)), 0)
ELSE 0
END
FROM ItemDynamic
INNER JOIN #TempOrder1 ON #TempOrder1.ItemID = ItemDynamic.ItemID
AND #TempOrder1.StoreID = ItemDynamic.StoreID
LEFT OUTER JOIN #TempQuantity ON #TempQuantity.ItemID = ItemDynamic.ItemID
AND #TempQuantity.StoreID = ItemDynamic.StoreID
SELECT @@ROWCOUNT AS RecordsAffected
We have increased the SQL tempdb file from 4GB to 8GB but there is still a serious lag in the PO. Anyone have ideas?
*This post is locked for comments
All responses (
Answers (
291,431
