I'm stuck at a bottle neck situation regarding performance of queries where i tried multiple solutions in vain. Need your suggestions...
Objective is to get all the released but not confirmed operations in a workcenter/plant.
This is the most optimal approach I could reach to....maybe the change in approach should be from the func. end; but I'm trying to get everything right from the technical end.
SELECT SINGLE OBJID
WHERE ARBPL = P_WKCNTR
AND WERKS = P_PLANT.
INTO TABLE T_AFKO1
FROM AFKO AS A INNER JOIN AFVC AS B ON A~AUFPL = B~AUFPL
LEFT OUTER JOIN AFRU AS C ON B~RUECK = C~RUECK
WHERE B~ARBID EQ LV_OBJID.
">>>> I tried a for all entries instead of outer join...but the over head was high as i'll have to run a loop after to delete.
DELETE T_AFKO1 WHERE AUERU = 'X'.
IF T_AFKO1 IS NOT INITIAL.
* GET THE STATUS OF AVAILABLE OPERATIONS
SELECT OBJNR INTO CORRESPONDING FIELDS OF TABLE T_STATUS FROM JEST
FOR ALL ENTRIES IN T_AFKO1 WHERE OBJNR = T_AFKO1-OBJNR AND
STAT = C_DELSTATUS_TX1 AND INACT = SPACE.
IF SY-SUBRC = 0.
SORT T_STATUS BY OBJNR.
* DELETE FROM T_AFVC ALL DELETED OPERATIONS
LOOP AT T_AFKO1 INTO W_AFKO1.
* CHECK TO SEE IF THE OBKNR IS A PART OF DELETED TABLE
READ TABLE T_STATUS WITH KEY OBJNR = W_AFKO1-OBJNR BINARY SEARCH.
IF SY-SUBRC EQ 0.
DELETE TABLE T_AFKO1 FROM W_AFKO1.
* CLEAR t_afko.
I've run out of options trying to optimize it and would require your suggestions on how to tackle this matter.
For a particular workcenter we get 878969 records into the T_AFKO1. The delete operation reduces it to 45K records. This way there is not much load on JEST which is huge. This excercise alone takes 3-4 mins in our Quality system.
We cant have a date range limitation and the input parameters workcenter/plant are fixed and unfortunately can't add any additional input parameters.
Please suggest if there is a standard FM or API which fetches unconfirmed opearions in a system for production orders.
Thanks all for your time in helping me.