Skip to Content
0
Former Member
Sep 18, 2012 at 02:03 PM

# Optimal Join conditions...need suggestions

15 Views

Hi Gurus,

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
FROM CRHD
INTO LV_OBJID
WHERE ARBPL = P_WKCNTR
AND WERKS = P_PLANT.

SELECT A~AUFNR
A~RSNUM

B~RUECK
B~OBJNR
C~AUERU
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.
ENDIF.
ENDIF.

* 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.
ENDIF.
CLEAR T_STATUS.
* CLEAR t_afko.
ENDLOOP.

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.

Thanks

Naveen