VERY IMPORTANT: Performance Optimization for the following Code

[Former Member]

Hi,

Here is my current code in the program that I working on:

SELECT SUM( KLMENG ) INTO I_ZDAILY_MVMT-DLY_BO

FROM VBAP

WHERE MATNR = I_ZDAILY_MVMT-MATNR AND

WERKS = I_ZDAILY_MVMT-WERKS AND

ERDAT = V_DATE.

The problem is that this code seems to be a bottleneck in the program, using about 50-52 seconds per record/matnr that is processes.

How do I crease performance?

I have tried table VAPMA, but VAPMA doesn't include KLMENG as a field.

Are there any other work-around's (joins on vbak, indexes and etc)?

Please help.

All responses will be rewarded and highly appreciated.

Thanks,

John

[Former Member]

Hi,

Here is my current code in the program that I working on:

SELECT SUM( KLMENG ) INTO I_ZDAILY_MVMT-DLY_BO

FROM VBAP

WHERE MATNR = I_ZDAILY_MVMT-MATNR AND

WERKS = I_ZDAILY_MVMT-WERKS AND

ERDAT = V_DATE.

The problem is that this code seems to be a bottleneck in the program, using about 50-52 seconds per record/matnr that is processes.

How do I crease performance?

I have tried table VAPMA, but VAPMA doesn't include KLMENG as a field.

Are there any other work-around's (joins on vbak, indexes and etc)?

Please help.

All responses will be rewarded and highly appreciated.

Thanks,

John

[Former Member]

Why don't use Group clause..

SELECT SUM( KLMENG ) INTO I_ZDAILY_MVMT-DLY_BO

FROM VBAP

WHERE MATNR = I_ZDAILY_MVMT-MATNR AND

WERKS = I_ZDAILY_MVMT-WERKS AND

ERDAT = V_DATE

group by klmeng.

if it is taking again then use sum operation in loop endloop by using internal table events.

Reward Points if it is helpful

Thanks

Seshu

[Former Member]

Hi,

Thank you for the suggestions. However, both methods are unsatisfactory and don not show a difference (except for "group by KLMENG) which increases performance by 1-2 seconds, which isn't very significant, considering the 52 seconds.)

Can you offer anything else, or is an index my best solution?

Thanks,

John

[Former Member]

index would be the best option

[uwe_schieferstein]

Hello John

Perhaps you could try the following:

(1) Select all required records from VBAP:

TYPES: BEGIN OF ty_s_select.
TYPES:   matnr    TYPE matnr.
TYPES:   werks    TYPE werks.
TYPES:   erdat    TYPE erdat.
TYPES:   klmeng   TYPE klmeng.
TYPES: END OF ty_s_select.
TYPES: ty_t_select    TYPE STANDARD TABLE OF ty_s_select
                      WITH DEFAULT KEY.

DATA:
  gs_select    TYPE ty_s_select,
  gt_select    TYPE ty_t_select,
  gt_collect   TYPE ty_t_select.


START-OF-SELECTION.

  SELECT matnr werks erdat klmeng FROM  vbap
    INTO TABLE gt_select
    WHERE matnr IN o_matnr
    AND   werks IN o_werks
    AND   erdat IN o_erdat.

(2) Collect the records into a new itab.

  LOOP AT gt_select INTO gs_select.
    COLLECT gs_select INTO gt_data.
  ENDLOOP.

Regards

Uwe