Reload taking very long time due to joining/keeping.

ramya_grandhi — Wed, 11 May 2022 11:07:35 GMT

Hi All,

Below script is taking long time to load. Log file is showing as joining/keeping which is not expected. Could you please help what is the issue here

Below is the script and log. Please help as this is critical.

Serial_Number_Temp:
Load TRANSACTION_ID_Backlog as SN_TRANSACTION_ID
,Key
,Key as SN_Key
.

.Resident Final_Serial_Number
where Payment_ID_Count=1 and WW_SL_Valid_Quantity=1;

left join
Load Key,
RangeMin(SN_THRESHOLD_QTY,Total_Valid_Qty) as Min_Paid_Qty
Resident Final_Serial_Number;

left Join
Load Key,
if(SN_WW_SL_Valid_Quantity=1,1,0) as Ult_Paid_Qty,
if(SN_WW_SL_Valid_Quantity=1,SN_PAID_AMOUNT/Min_Paid_Qty,0) as Ult_Paid_Amount
resident Serial_Number_Temp;

//At this point log is showing as joining/keeping and running for long
Concatenate(Serial_Number_Temp)
Load TRANSACTION_ID_Backlog as SN_TRANSACTION_ID
,Key
,Key as SN_Key
.

.
Resident Final_Serial_Number
where Payment_ID_Count=1 and WW_SL_Valid_Quantity=0;

Log:

20220511T013713.198-0500 0543 Serial_Number_Temp:
20220511T013713.198-0500 0544 Load TRANSACTION_ID_Backlog as SN_TRANSACTION_ID
20220511T013713.198-0500 0545 ,SERIAL_NUMBER as SN_SERIAL_NUMBER
20220511T013713.198-0500 0546 ,WW_SL_Sold_Quantity as SN_WW_SL_Sold_Quantity
20220511T013713.198-0500 0547 ,WW_SL_Valid_Quantity as SN_WW_SL_Valid_Quantity
20220511T013713.198-0500 0548 ,Total_Valid_Qty as SN_Total_Valid_Qty
20220511T013713.198-0500 0549 ,Key
20220511T013713.198-0500 0550 ,Key as SN_Key
20220511T013713.198-0500 0551
20220511T013713.198-0500 0552 ,SL_PAID_AMOUNT as SN_PAID_AMOUNT
20220511T013713.198-0500 0553 ,[SN_SNOP] as SL_SNOP
20220511T013713.198-0500 0554 ,[SN_DERIVED_PRICE_POINT] as SL_DERIVED_PRICE_POINT
20220511T013713.198-0500 0555 ,[SN_REBATE_DEAL_1] as SL_REBATE_DEAL_1
20220511T013713.198-0500 0556 ,[SN_REBATE_DEAL_2] as SL_REBATE_DEAL_2
20220511T013713.198-0500 0557 ,[SN_REBATE_DEAL_3] as SL_REBATE_DEAL_3
20220511T013713.198-0500 0558 ,[SN_REBATE_DEAL_4] as SL_REBATE_DEAL_4
20220511T013713.198-0500 0559 ,[SN_REBATE_DEAL_5] as SL_REBATE_DEAL_5
20220511T013713.198-0500 0560 ,Flag as SL_Flag
20220511T013713.198-0500 0561 Resident Final_Serial_Number
20220511T013713.198-0500 0562 where Payment_ID_Count=1 and WW_SL_Valid_Quantity=1
20220511T013713.198-0500 16 fields found: SN_TRANSACTION_ID, SN_SERIAL_NUMBER, SN_WW_SL_Sold_Quantity, SN_WW_SL_Valid_Quantity, SN_Total_Valid_Qty, Key, SN_Key, SN_PAID_AMOUNT, SL_SNOP, SL_DERIVED_PRICE_POINT, SL_REBATE_DEAL_1, SL_REBATE_DEAL_2, SL_REBATE_DEAL_3, SL_REBATE_DEAL_4, SL_REBATE_DEAL_5, SL_Flag,
20220511T013721.651-0500 684,373 lines fetched
20220511T013721.700-0500 0564 left join
20220511T013721.700-0500 0565 Load Key,
20220511T013721.700-0500 0566 RangeMin(SN_THRESHOLD_QTY,Total_Valid_Qty) as Min_Paid_Qty
20220511T013721.700-0500 0567 Resident Final_Serial_Number
20220511T013721.700-0500 2 fields found: Key, Min_Paid_Qty,
20220511T013723.442-0500 9,936,951 lines fetched
20220511T013723.442-0500 Joining/Keeping
20220511T013727.470-0500 0570 left Join
20220511T013727.470-0500 0571 Load Key,
20220511T013727.470-0500 0572 if(SN_WW_SL_Valid_Quantity=1,1,0) as Ult_Paid_Qty,
20220511T013727.470-0500 0573 if(SN_WW_SL_Valid_Quantity=1,SN_PAID_AMOUNT/Min_Paid_Qty,0) as Ult_Paid_Amount
20220511T013727.470-0500 0574 resident Serial_Number_Temp
20220511T013727.471-0500 3 fields found: Key, Ult_Paid_Qty, Ult_Paid_Amount,
20220511T013732.671-0500 42,622,668 lines fetched
20220511T013732.671-0500 Joining/Keeping

It is there at this point from 4 hours and still running. So we aborted the reload. Please help to fix the issue.

Thank you

@jonathandienst @sasiparupudi1

topic Reload taking very long time due to joining/keeping. in App Development

Reload taking very long time due to joining/keeping.