Skip to Content
0
Former Member
Mar 02, 2009 at 05:32 AM

Query takes too much of exec time.

26 Views

Hi Experts

Initially, I had the below query, which took too much of execution time.

SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG
         A~ERDAT A~ERZET A~PSTYV D~AUART E~ETTYP
         E~EDATU
           INTO TABLE INT_COLL_ORD
      FROM VBAP AS A
      INNER JOIN VBAK AS D ON D~VBELN EQ A~VBELN AND
                           D~MANDT EQ A~MANDT
      INNER JOIN VBEP AS E ON E~VBELN EQ A~VBELN AND
                           E~POSNR EQ A~POSNR    AND
                          E~MANDT EQ A~MANDT
           WHERE           A~VBELN IN s_VBELN AND
                           D~auart in s_auart AND
                           D~vkorg in s_vkorg AND
                           D~vbtyp eq 'C'     AND
  ( ( matnr LIKE c_prefix_sp AND zz_msposnr NE 0 AND kbmeng EQ 0 )
  OR ( matnr LIKE c_prefix_fp AND kwmeng NE A~kbmeng ) ) AND
                           A~ABGRU EQ SPACE      AND
                           A~MTVFP IN R_MTVFP    AND
                           A~PRCTR IN R_PRCT     AND
                           E~ETENR EQ '1'.

  SORT INT_COLL_ORD BY VBELN POSNR ETTYP.
  DELETE ADJACENT DUPLICATES FROM INT_COLL_ORD COMPARING VBELN POSNR.
  CHECK NOT INT_COLL_ORD[] IS INITIAL.

  SELECT VBELN UVALL CMGST INTO TABLE INT_VBUK
          FROM VBUK FOR ALL ENTRIES IN INT_COLL_ORD
          WHERE VBELN = INT_COLL_ORD-VBELN AND
                UVALL NE 'A'.
  SORT INT_VBUK BY VBELN.
  DELETE ADJACENT DUPLICATES FROM INT_VBUK COMPARING VBELN.

So, I split this big INNER JOIN of 3 Tables into 2 different INNERJOINs as below:

SELECT A~AUART B~VBELN B~UVALL B~CMGST
  INTO TABLE INT_VBUK
  FROM VBAK AS A INNER JOIN VBUK AS B
  ON A~VBELN EQ B~VBELN
  WHERE A~VBELN IN s_VBELN AND
  A~auart in s_auart AND
  A~vkorg in s_vkorg AND
  A~vbtyp eq 'C' AND
  B~UVALL NE 'A'.

  IF NOT INT_VBUK[] IS INITIAL.

    SORT INT_VBUK BY VBELN.
    DELETE ADJACENT DUPLICATES FROM INT_VBUK COMPARING VBELN.

    SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG A~ERDAT A~ERZET A~PSTYV B~ETTYP B~EDATU
    INTO TABLE INT_COLL_ORD
    FROM VBAP AS A INNER JOIN VBEP AS B
    ON B~VBELN EQ A~VBELN AND B~POSNR EQ A~POSNR AND B~MANDT EQ A~MANDT
    FOR ALL ENTRIES IN INT_VBUK
    WHERE A~VBELN = INT_VBUK-VBELN AND
    ( ( matnr LIKE c_prefix_sp AND zz_msposnr NE 0 AND kbmeng EQ 0 )
    OR ( matnr LIKE c_prefix_fp AND kwmeng NE A~kbmeng ) ) AND
    A~ABGRU EQ SPACE AND
    A~MTVFP IN R_MTVFP AND
    A~PRCTR IN R_PRCT AND
    B~ETENR EQ '1'.

  ENDIF.

  SORT INT_COLL_ORD BY VBELN POSNR ETTYP.
  DELETE ADJACENT DUPLICATES FROM INT_COLL_ORD COMPARING VBELN POSNR.
  CHECK NOT INT_COLL_ORD[] IS INITIAL.

  loop at int_coll_ord.
    read table int_vbuk with key vbeln = int_coll_ord-vbeln BINARY SEARCH.
    int_coll_ord-auart = int_vbuk-auart.
    modify int_coll_ord.
  endloop.

Still, it is noted that the performance is not improved at all. All the more, this query takes more time than the prior one.

Could anybody help me to re-write this query in an effective way?

Regards

BD