topic Re: ABAP query optimization in Application Development and Automation Discussions

ABAP query optimization

former_member693168 — Fri, 23 Oct 2020 20:12:27 GMT

Hi to all.

I must optimize the following loop and query, it is making exactly what I need, but I need make it by the fastest way. How can I do it by the fastest way with maximum performance?

DATA wa_budat TYPE CHVW-BUDAT.

LOOP AT T_SHADOW ASSIGNING FIELD-SYMBOL(<FS_T_SHADOW>) WHERE MENGE IS NOT INITIAL.
  CLEAR wa_budat.

  SELECT SINGLE
    MAX( BUDAT )
  INTO wa_budat
  FROM
     CHVW
  WHERE
    WERKS = <FS_T_SHADOW>-WERKS
    AND MATNR = <FS_T_SHADOW>-MATNR
    AND CHARG = <FS_T_SHADOW>-CHARG
    AND
    (
      AUFNR = <FS_T_SHADOW>-AUFNR
      AND EBELN = <FS_T_SHADOW>-EBELN AND EBELP = <FS_T_SHADOW>-EBELP
      AND KDAUF = <FS_T_SHADOW>-KDAUF AND KDPOS = <FS_T_SHADOW>-KDPOS
      AND VBELN = <FS_T_SHADOW>-VBELN AND POSNR = <FS_T_SHADOW>-POSNR
    )
    AND XSTBW EQ abap_false.

  IF sy-subrc EQ 0.
    <FS_T_SHADOW>-ZZBUDAT = wa_budat.
  ENDIF.
ENDLOOP.

Re: ABAP query optimization

lumirion — Fri, 23 Oct 2020 21:04:39 GMT

Try not to SELECT within LOOP. Select all relevant data before looping with SELECT FOR ALL ENTRIES.

Re: ABAP query optimization

Sandra_Rossi — Sat, 24 Oct 2020 13:08:21 GMT

Don't forget to do performance tests before and after change.

Of course, SELECT inside a loop is prohibited as a rule-of-thumb.

I see that you have MAX, so you can't use FOR ALL ENTRIES.

You may create a temporary database table to contain the internal table T_SHADOW, with columns WERKS, MATNR, CHARG, AUFNR, EBELN, etc.

With a Global Temporary Table (GTT), its content is deleted after COMMIT and concurrency is managed.

I think that you may also join directly with the internal table passed to a database procedure (AMDP/SQL Script).

All these possibilities depend on your ABAP version.

Re: ABAP query optimization

MateuszAdamus — Sat, 24 Oct 2020 18:01:31 GMT

Hello mrrjesus

For one, you should try to provide a value for XZUGA field, as it's the first field of the primary key of the table. It will greatly increase the performance.

Other thing would be to provide a value for the AUFPS field, for the same reason as above.

Now, as to the implementation, you could do as Sandra proposed (temporary table or AMDP procedure). You can also try to do it in plain ABAP like shown below.

DATA(lt_shadow) = t_shadow[].
DELETE lt_shadow WHERE menge IS INITIAL.

SELECT werks, matnr, charg, aufnr, ebeln, ebelp, kdauf, kdpos, vbeln, posnr, budat
  FROM chvw
  FOR ALL ENTRIES IN @lt_shadow
  WHERE WERKS = @lt_shadow-werks
    AND MATNR = @lt_shadow-matnr
    AND CHARG = @lt_shadow-charg
    AND AUFNR = @lt_shadow-aufnr " brackets not needed for AND
    AND EBELN = @lt_shadow-ebeln 
    AND EBELP = @lt_shadow-ebelp
    AND KDAUF = @lt_shadow-kdauf 
    AND KDPOS = @lt_shadow-kdpos
    AND VBELN = @lt_shadow-vbeln 
    AND POSNR = @lt_shadow-posnr
    AND XSTBW = @abap_false
  INTO TABLE @lt_chvw.

SORT lt_chvw BY werks matnr charg aufnr ebeln ebelp kdauf kdpos vbeln posnr budat DESCENDING.

LOOP AT t_shadow REFERENCE INTO DATA(ld_shadow)
  WHERE menge IS NOT INITIAL.

  READ TABLE lt_chvw REFERENCE INTO DATA(ld_chvw)
    BINARY SEARCH
    WITH KEY werks = ld_shadow->werks
             matnr = ld_shadow->matnr
             charg = ld_shadow->charg
             aufnr = ld_shadow->aufnr
             ebeln = ld_shadow->ebeln
             ebelp = ld_shadow->ebelp
             kdauf = ld_shadow->kdauf
             kdpos = ld_shadow->kdpos
             vbeln = ld_shadow->vbeln
             posnr = ld_shadow->posnr.
  CHECK sy-subrc = 0.
  ld_shadow->zzbudat = ld_chvw->budat.
ENDLOOP.

Kind regards,

Mateusz

Re: ABAP query optimization

former_member693168 — Mon, 26 Oct 2020 13:53:20 GMT

Perfect. Your answer is exactly what I need. You gave me a solution with a surgical precision.

Thank you so much. God bless you.

Re: ABAP query optimization

former_member693168 — Mon, 26 Oct 2020 16:52:14 GMT

Thank you. Your strategy is what I need.