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];
Could you share the log file from a reload to see what bits are slow.
Please find attached log file,
Doing Resident and grouping again and again could be one cause of it.
Its this part of the code where it runs very slow:-
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];
How can i avoid that then? Sorry
You could consider making sure all the loads from QVD's are Optimized loads.
Avoid this grouping as you are taking Flags like:
Try this:
Concatenate(OrderBookStatus)
Load
[PD Detail Id] as [Join WDD to OBS],
[PD Pickticket Quantity] as Quantity,
'Waved' as [Order Sub Status],
'In Picking/Packing' as [Order Status]
Resident
PickTicket_Detail
where
[PD PH Status] = '20';
Concatenate(OrderBookStatus)
Load
[PD Detail Id] as [Join WDD to OBS],
[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';
Then at front end, you can simply use:
sum({<[Order Sub Status={'Waved'}]>} [PD Pickticket Quantity] )
Hope this will help!!
I would suggest you to store the PickTicket_Detail in qvd file. Then you can use concatenate like below. I'm sure it will be much faster.
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]
FROM
[PickTicket_Detail].qvd (qvd) 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]
FROM
[PickTicket_Detail].qvd (qvd)
where
[PD PH Status] >= '21' and [PD PH Status] <= '34'
group by [PD Detail Id];
Let me know.
Edit: Alter your code wherever you are using resident PickTicket_Detail.
That's the difficulty I'm having optimising the loads.