Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following script which runs really slow, does anyone have any tips on making the load quicker?
OrderBookStatusTMP:
Load OE_Order_Lines_All.LINE_ID as [OBST Line Id],
[Order Quantity]
Resident Order_Line
where [Booked Flag] = 'Y'
and Status = 'AWAITING_SHIPPING';
Inner Join(OrderBookStatusTMP)
Load WSH_Delivery_Details.SOURCE_LINE_ID as [OBST Line Id],
WSH_Delivery_Details.DELIVERY_DETAIL_ID,
WSH_Delivery_Details.REQUESTED_QUANTITY
Resident WSH_DeliveryDetails
Where ApplyMap('HasAssignmentRecordMap',[Join WDA to WDD],'N') = 'N';
OrderBookStatus:
Load
WSH_Delivery_Details.DELIVERY_DETAIL_ID as [Join WDD to OBS],
//[Join OBS to Order Line],
//sum(OE_Order_Lines_All.PRICING_QUANTITY - OE_Order_Lines_All.CANCELLED_QUANTITY) as Quantity,
Sum(WSH_Delivery_Details.REQUESTED_QUANTITY) as Quantity,
'Order Units Booked' as [Order Sub Status],
'Booked' as [Order Status]
Resident
OrderBookStatusTMP
group by WSH_Delivery_Details.DELIVERY_DETAIL_ID;
drop table OrderBookStatusTMP;
Concatenate(OrderBookStatus)
Load
[PD Detail Id] as [Join WDD to OBS],
Sum([PD Pickticket Quantity]) as Quantity,
'Released to Warehouse' as [Order Sub Status],
'Released to Warehouse' as [Order Status]
Resident
PickTicket_Detail
where
[PD PH Status] = '10'
group by [PD Detail Id];
Concatenate(OrderBookStatus)
Load
[PD Detail Id] as [Join WDD to OBS],
Sum([PD Pickticket Quantity]) as Quantity,
'Waved' as [Order Sub Status],
'In Picking/Packing' as [Order Status]
Resident
PickTicket_Detail
where
[PD PH Status] = '20'
group by [PD Detail Id];
Concatenate(OrderBookStatus)
Load
[PD Detail Id] as [Join WDD to OBS],
Sum([PD Pickticket Quantity]) as Quantity,
'Picked' as [Order Sub Status],
'In Picking/Packing' as [Order Status]
Resident
PickTicket_Detail
where
[PD PH Status] >= '21' and [PD PH Status] <= '34'
group by [PD Detail Id];
Concatenate(OrderBookStatus)
Load
[PD Detail Id] as [Join WDD to OBS],
Sum([PD Pickticket Quantity]) as Quantity,
'In Packing' as [Order Sub Status],
'In Picking/Packing' as [Order Status]
Resident
PickTicket_Detail
where
[PD PH Status] >= '35' and [PD PH Status] <= '40'
group by [PD Detail Id];
Concatenate(OrderBookStatus)
Load
[PD Detail Id] as [Join WDD to OBS],
// If(Sum([PD Shipped Quantity]) = 0,
// If(sum([PD Packed Quantity]) = 0, sum([PD Pickticket Quantity]),sum([PD Packed Quantity]))
// ,sum([PD Shipped Quantity])) as Quantity,
Sum([PD State Quantity]) as Quantity,
'Loaded on Trailer' as [Order Sub Status],
'Loaded on Trailer' as [Order Status]
Resident
PickTicket_Detail
where
[PD PH Status] = '70'
group by [PD Detail Id];
You have mentioned part of the code is causing the problem. Why not to store the table alone in QVD and use it Check my reply above.
isn't storing the qvd the same as using the resident table?
Agree with Tamil as well...
Compare to Resident, loading from QVD would be faster...
This change using the stored qvd is running the same time, no change, may have to use a different strategy. Only knocked 4mins off
see log attached
Then try to avoid Grouping as I suggested earlier.
And do at front end.
Already shared with you.
It will surely help you to improve performance.