Performance issue about VBAP inner join VBAK (Help)

[Former Member]

Hi guys,

The following clauses will try to get one year's sales order data.

    SELECT vbap~vbeln vbap~posnr vbap~matnr
           vbap~meins vbap~spart vbap~netwr
           vbap~waerk vbap~kwmeng vbak~audat vbak~auart
           vbak~augru vbak~vkorg
        INTO TABLE gt_vbap FROM vbap
        INNER JOIN vbak
        ON  vbap~vbeln = vbak~vbeln
*   FOR ALL entries IN gt_matnr  "limit the result set==>by James
*   WHERE vbap~matnr = gt_matnr-matnr "limit the result set==>by James
    WHERE  vbap~abgru = space   "Reason for rejection of so
          AND vbap~spart IN s_spart
          AND vbap~werks IN s_werks
          AND vbak~audat IN gr_datum "Set the user specified date range
          AND vbak~augru IN s_augru
          AND vbak~vkorg IN s_vkorg.

Is there any performance improve suggestion, currently it runs unbearable slowly?

Thanks very much!

[Former Member]

Hi,

Try this one.

SELECT vbeln posnr matnr

meins spart netwr

waerk kwmeng INTO TABLE gt_vbap FROM vbap

WHERE abgru = space "Reason for rejection of so

ANDspart IN s_spart

AND werks IN s_werks.

SELECT audat auart

augru vkorg

INTO TABLE gt_vbap FROM vbaK

FOR ALL ENTRIES OF GT_VBAK

WHERE audat IN gr_datum "Set the user specified date range

AND augru IN s_augru

AND vkorg IN s_vkorg.

<b>Regards

Debjani

Reward point for helpful answer</b>

[Former Member]

Thanks for all.

So do you guys has any suggestion on the following code?

 DESCRIBE TABLE gt_vbeln LINES l_lines.
  IF l_lines = 0.
***>>Links20060411
*  ELSEIF l_lines GT c_1000.
*    SELECT vbelv posnv vbeln posnn vbtyp_n rfmng
*        APPENDING TABLE gt_vbfa_all PACKAGE SIZE c_1000
*        FROM vbfa
*        FOR   ALL ENTRIES IN gt_vbeln
*        WHERE vbelv   EQ  gt_vbeln-vbelv
*          AND posnv   EQ gt_vbeln-posnv
*          AND vbtyp_n IN ('T', 'J', 'R', 'h').
*    ENDSELECT.
*  ELSE.
*    SELECT vbelv posnv vbeln posnn vbtyp_n rfmng
*        INTO TABLE gt_vbfa_all FROM vbfa
*        FOR   ALL ENTRIES IN gt_vbeln
*        WHERE vbelv   EQ  gt_vbeln-vbelv
*          AND posnv   EQ  gt_vbeln-posnv
*          AND vbtyp_n IN ('T', 'J', 'R', 'h').
  ELSEIF l_lines > c_1000.
    SELECT vbelv posnv vbeln posnn vbtyp_n rfmng
        APPENDING TABLE gt_vbfa PACKAGE SIZE c_1000
        FROM vbfa
        FOR   ALL ENTRIES IN gt_vbeln
        WHERE vbelv   =  gt_vbeln-vbelv
          AND posnv   = gt_vbeln-posnv
          AND vbtyp_n IN ('T', 'J').
    ENDSELECT.
  ELSE.
    SELECT vbelv posnv vbeln posnn vbtyp_n rfmng
        INTO TABLE gt_vbfa FROM vbfa
        FOR   ALL ENTRIES IN gt_vbeln
        WHERE vbelv   =  gt_vbeln-vbelv
          AND posnv   =  gt_vbeln-posnv
          AND vbtyp_n IN ('T', 'J').
  ENDIF.

Currently it runs timeout ,as the l_lines is very very large.

I think maybe we can change the package size. But what's the best package size for performance?

Thanks..

[Former Member]

Hi

Hi try this and see, changed the fields sequence,

still it is taking time try to create an secondary index on the where condition fields and see

SELECT vbak_{vbeln vbak}audat vbak~auart

vbak_{augru vbak}vkorg

vbap_{posnr vbap}matnr

vbap_{meins vbap}spart vbap~netwr

vbap_{waerk vbap}kwmeng

INTO TABLE gt_vbap FROM vbak

INNER JOIN vbap

ON vbak_{vbeln = vbap}vbeln

WHERE vbak~audat IN gr_datum

AND vbak~augru IN s_augru

AND vbak~vkorg IN s_vkorg

AND vbap~abgru = space

AND vbap~spart IN s_spart

AND vbap~werks IN s_werks.

<b>Reward points for useful Answers</b>

Regards

Anji

[Former Member]

Guys,

Please give me a hand.

Thanks very much!

-James