topic Re: Sequential Read on VBAK in Application Development and Automation Discussions

Sequential Read on VBAK

Former Member — Mon, 07 May 2007 07:19:31 GMT

Hi All,

Please help me to avoid sequential access on the table VBAK.

as i am using this query on VBAK,VBUK,VBPA and VBFA.

the query as follows.

How to improve the performance of this query,its very urgent please give me the sample query if possible.

SELECT a~vbeln

a~erdat

a~ernam

a~vkorg

a~vkbur

a~kunnr

b~kunnr AS kunnr_shp

b~adrnr

c~gbstk

c~fkstk

d~vbeln AS vbeln_i

INTO CORRESPONDING FIELDS OF TABLE t_z92sales

FROM vbak AS a INNER JOIN vbuk AS c ON a_{vbeln = c}vbeln

INNER JOIN vbpa AS b ON a_{vbeln = b}vbeln

AND b_{posnr = 0 AND b}parvw = 'WE'

LEFT OUTER JOIN vbfa AS d ON a_{vbeln = d}vbelv AND d~vbtyp_n = 'M'

WHERE ( a~vbeln BETWEEN fvbeln AND tvbeln )

AND a~vbtyp = 'C'

AND a~vkorg IN vkorg_s

AND c~gbstk IN gbstk_s.

Re: Sequential Read on VBAK

Former Member — Mon, 07 May 2007 07:24:27 GMT

hi ramada,

in your code try to avoid into corresponding fileds keyword instead of that try to use into table that's all.

if possible try to avoid even 'joins' in your querry instead of that u can use for all entries key word.

these can definatly help u.

regards...

seshu.

Message was edited by:

maddipatla Seshu chowdary

Re: Sequential Read on VBAK

valter_oliveira — Mon, 07 May 2007 10:40:29 GMT

Hi.

Sometimes, simplificating helps performance.

Try this:

DATA: wa type t_z92sales.

SELECT a~vbeln a~erdat a~ernam a~vkorg a~vkbur a~kunnr b~gbstk b~fkstk
INTO (wa-vbeln, wa-erdat, wa-ernam, wa-vkorg, wa-vkbur, wa-kunnr, 
          wa-gbstk, wa-fkstk)
FROM vbak AS a INNER JOIN vbuk AS b 
ON a~vbeln = b~vbeln.
WHERE ( a~vbeln BETWEEN fvbeln AND tvbeln ) 
AND a~vbtyp = 'C' 
AND a~vkorg IN vkorg_s 
AND b~gbstk IN gbstk_s. 

  SELECT SINGLE kunnr adrnr FROM vbpa 
       INTO (wa-kunnr_shp, wa-adrnr)
  WHERE vbeln = wa-vbeln
        AND posrnr = '000000'
        AND parvw = 'WE'.
  CHECK sy-subrc EQ 0.
  
  CLEAR: wa-vbeln_i.
  SELECT vbeln FROM vbfa UP TO 1 ROWS
       INTO (wa-vbeln_i)
  WHERE vbelv = wa-vbeln
        AND vbtyp_n = 'M'.
    EXIT.
  ENDSELECT.

  APPEND wa TO t_z92sales.

ENDSELECT.

Hope this helps.

For a different query, must know the scenario.

Reward helpfull ideas.

Best regards.

Valter Oliveira.

Re: Sequential Read on VBAK

Former Member — Mon, 07 May 2007 14:18:27 GMT

Hi Ramada,

Try using the following code. It is a lot faster.

RANGES: r_vbeln FOR vbka-vbeln.

DATA: w_index TYPE sy-tabix,

      BEGIN OF t_vbak OCCURS 0,
        vbeln  TYPE vbak-vbeln,
        erdat  TYPE vbak-erdat,
        ernam  TYPE vbak-ernam,
        vkorg  TYPE vbak-vkorg,
        vkbur  TYPE vbak-vkbur,
        kunnr  TYPE vbak-kunnr,
        del(1) TYPE c         ,
      END OF t_vbak,

      BEGIN OF t_vbuk OCCURS 0,
        vbeln TYPE vbuk-vbeln,
        gbstk TYPE vbuk-gbstk,
        fkstk TYPE vbuk-fkstk,
      END OF t_vbuk,

      BEGIN OF t_vbpa OCCURS 0,
        vbeln TYPE vbpa-vbeln,
        kunnr TYPE vbpa-kunnr,
        adrnr TYPE vbpa-adrnr,
      END OF t_vbpa,

      BEGIN OF t_vbfa OCCURS 0,
        vbelv TYPE vbfa-vbelv,
        vbeln TYPE vbfa-vbeln,
      END OF t_vbfa,

      BEGIN OF t_z92sales OCCURS 0,
        vbeln     TYPE vbak-vbeln,
        erdat     TYPE vbak-erdat,
        ernam     TYPE vbak-ernam,
        vkorg     TYPE vbak-vkorg,
        vkbur     TYPE vbak-vkbur,
        kunnr     TYPE vbak-kunnr,
        gbstk     TYPE vbuk-gbstk,
        fkstk     TYPE vbuk-fkstk,
        kunnr_shp TYPE vbpa-kunnr,
        adrnr     TYPE vbpa-adrnr,
        vbeln_i   TYPE vbfa-vbeln,
      END OF t_z92sales.


IF NOT fvbeln IS INITIAL
OR NOT tvbeln IS INITIAL.

  REFRESH r_vbeln.

  r_vbeln-sign   = 'I'.

  IF fvbeln IS INITIAL.
    r_vbeln-option = 'EQ'.
    r_vbeln-low    = tvbeln.
  ELSEIF tvbeln IS INITIAL.
    r_vbeln-option = 'EQ'.
    r_vbeln-low    = fvbeln.
  ELSE.
    r_vbeln-option = 'BT'.
    r_vbeln-low    = fvbeln.
    r_vbeln-high   = tvbeln.
  ENDIF.

  APPEND r_vbeln.
  CLEAR r_vbeln.

  SELECT vbeln
         erdat
         ernam
         vkorg
         vkbur
         kunnr
    FROM vbak
    INTO TABLE t_vbak
    WHERE vbeln IN r_vbeln
    AND   vbtyp EQ 'C'
    AND   vkorg IN vkorg_s.
  IF sy-subrc EQ 0.

    SORT t_vbak BY vbeln.

    SELECT vbeln
           gbstk
           fkstk
    FROM vbuk
    INTO TABLE t_vbuk
    FOR ALL ENTRIES IN t_vbak
    WHERE vbeln EQ t_vbak-vbeln
    AND   gbstk IN gbstk_s.

    IF sy-subrc EQ 0.

      SORT t_vbuk BY vbeln.

      LOOP AT t_vbak.

        w_index = sy-tabix.

        READ TABLE t_vbuk WITH KEY vbeln = t_vbak-vbeln
                                   BINARY SEARCH
                                   TRANSPORTING NO FIELDS.
        IF sy-subrc NE 0.
          t_vbak-del = 'X'.
          MODIFY t_vbak INDEX w_index TRANSPORTING del.
        ENDIF.

      ENDLOOP.

      DELETE t_vbak WHERE del EQ 'X'.

    ENDIF.

  ENDIF.

ELSE.

  SELECT vbeln
         gbstk
         fkstk
  FROM vbuk
  INTO TABLE t_vbuk
  WHERE vbtyp EQ 'C'
  AND   gbstk IN gbstk_s.

  IF sy-subrc EQ 0.

    SORT t_vbuk BY vbeln.

    SELECT vbeln
           erdat
           ernam
           vkorg
           vkbur
           kunnr
      FROM vbak
      INTO TABLE t_vbak
      FOR ALL ENTRIES IN t_vbuk
      WHERE vbeln EQ t_vbuk-vbeln
      AND   vkorg IN vkorg_s.

    IF sy-subrc EQ 0.
      SORT t_vbak BY vbeln.
    ENDIF.

  ENDIF.

ENDIF.

IF NOT t_vbak[] IS INITIAL.

  SELECT vbeln
         kunnr
         adrnr
    FROM vbpa
    INTO TABLE t_vbpa
    FOR ALL ENTRIES IN t_vbak
    WHERE vbeln EQ t_vbak-vbeln
    AND   posnr EQ '000000'
    AND   parvw EQ 'WE'.

  IF sy-subrc EQ 0.

    SORT t_vbpa BY vbeln.

    SELECT vbelv
           vbeln
      FROM vbfa
      INTO TABLE t_vbfa
      FOR ALL ENTRIES IN t_vbpa
      WHERE vbelv EQ t_vbpa-vbeln
      AND   vbtyp_n EQ 'M'.

    IF sy-subrc EQ 0.
      SORT t_vbfa BY vbelv.
    ENDIF.

  ENDIF.

ENDIF.

REFRESH t_z92sales.

LOOP AT t_vbak.

  READ TABLE t_vbuk WITH KEY vbeln = t_vbak-vbeln
                             BINARY SEARCH
                             TRANSPORTING
                               gbstk
                               fkstk.
  IF sy-subrc EQ 0.
    READ TABLE t_vbpa WITH KEY vbeln = t_vbak-vbeln
                                       BINARY SEARCH
                                       TRANSPORTING
                                         kunnr
                                         adrnr.
    IF sy-subrc EQ 0.
      READ TABLE t_vbfa WITH KEY vbelv = t_vbak-vbeln
                                 BINARY SEARCH
                                 TRANSPORTING
                                   vbeln.
      IF sy-subrc EQ 0.
        t_z92sales-vbeln     = t_vbak-vbeln.
        t_z92sales-erdat     = t_vbak-erdat.
        t_z92sales-ernam     = t_vbak-ernam.
        t_z92sales-vkorg     = t_vbak-vkorg.
        t_z92sales-vkbur     = t_vbak-vkbur.
        t_z92sales-kunnr     = t_vbak-kunnr.
        t_z92sales-gbstk     = t_vbuk-gbstk.
        t_z92sales-fkstk     = t_vbuk-fkstk.
        t_z92sales-kunnr_shp = t_vbpa-kunnr.
        t_z92sales-adrnr     = t_vbpa-adrnr.
        t_z92sales-vbeln_i   = t_vbfa-vbeln.
        APPEND t_z92sales.
        CLEAR  t_z92sales.
      ENDIF.
    ENDIF.
  ENDIF.
ENDLOOP.

Re: Sequential Read on VBAK

Former Member — Wed, 09 May 2007 04:51:47 GMT

Dear Mark,

Thank you for the Responce.

Will test the code and let you know the feedback.

Cheers and Regards,

Ramada