Optimal Join conditions...need suggestions

[Former Member]

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

[volker_borowski2]

Can you please give us the DB accessplan (either from ST05 or SQL trace).

How big are the tables involved (all three in MB and #rows)

On this WHERE clause, I'd expect an index access on ARBID on AFVC as a starter,

then it should join AFKO and the do the outer join to AFRU.

Volker

And something else: why do you delete from the internal table after you already fetched the data. Try not to select it right in the beginning by adjusting WHERE.

Message was edited by: Volker Borowski

[Former Member]

And something else: why do you delete from the internal table after you already fetched the data. Try not to select it right in the beginning by adjusting WHERE.

Message was edited by: Volker Borowski

AFRU-RUECK is part of a right outerjoin and I can't include it in the where clause. This unfortunaltely cannot be changed because if an operation is not confirmed then it wouldn't have an entry in AFRU but would also be required.

Including JEST in the join was more expensive than a seperate access and then looping as it would be a negative whereclause condition.

Unfortunately dont have access to internet from the system where I connect to SAP and hence cannot post DB access plans...but I included number of rows of each internal table.

In SM66 the Program space had exceeded ~200MB (Extended) after 45 minutes of running online before sys admin terminated it.

Trying to impose a date limitation on date range for data to be fetched.

Sorry for the delay in response .... I've some serious connectivity issues

I'll change index access on AFVC as suggested and check. Thanks for that suggestion.