For all entries vs Inner join

We have lot of confusion that when to use inner join and for entries. I have created sample program to check performance.

Solution :

  Use Inner Join always for header item table data selection.

  Example: VBRK Join VBRP

                  LIKP Join LIPS

                  VBAK Join VBAP

  User  For all entries for mater data to transaction data
               
  Example: VBRP For all entries MARA
                  VBAP For all entries KNA1

Demo Program

 VBRK Vs VBRP Performance 

 *&---------------------------------------------------------------------*
*& Report  ZJOIN_PERFORMANCE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zjoin_performance.

TABLES : vbrk.

DATA : v_start_time TYPE timestampl,
       v_end_time   TYPE timestampl,
       v_diff_w     TYPE p DECIMALS 5.

SELECT-OPTIONS : s_erdat FOR vbrk-erdat.

START-OF-SELECTION.

 do 5 TIMES.

  GET TIME STAMP FIELD v_start_time.

  PERFORM for_all_entries.

  GET TIME STAMP FIELD v_end_time.
  v_diff_w = v_end_time - v_start_time.

  WRITE :/(30) 'for all entries', v_diff_w.
  CLEAR : v_start_time, v_end_time, v_end_time.

  GET TIME STAMP FIELD v_start_time.

  PERFORM inner_join.

  GET TIME STAMP FIELD v_end_time.
  v_diff_w = v_end_time - v_start_time.

  WRITE :/(30) 'Inner Join', v_diff_w.
  CLEAR : v_start_time, v_end_time, v_end_time.

 ENDdo.

END-OF-SELECTION.
*&---------------------------------------------------------------------*
*&      Form  FOR_ALL_ENTRIES
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM for_all_entries .

  TYPES : BEGIN OF ty_final,
            vbeln TYPE vbrk-vbeln,
            posnr TYPE vbrp-posnr,
            erdat TYPE vbrk-erdat,
            matnr TYPE vbrp-matnr,
          END OF ty_final.


  TYPES : BEGIN OF ty_vbrk,
            vbeln TYPE vbrk-vbeln,
            erdat TYPE vbrk-erdat,
          END OF ty_vbrk.

  TYPES : BEGIN OF ty_vbrp,
            vbeln TYPE vbrk-vbeln,
            posnr TYPE vbrp-posnr,
            matnr TYPE vbrp-matnr,
          END OF ty_vbrp.


  DATA : lt_vbrk  TYPE STANDARD TABLE OF ty_vbrk WITH UNIQUE SORTED KEY by_vbeln COMPONENTS vbeln,
         lt_vbrp  TYPE STANDARD TABLE OF ty_vbrp,
         lt_final TYPE STANDARD TABLE OF ty_final.

  DATA : ls_final TYPE ty_final.


  SELECT vbeln,
         erdat
         FROM vbrk
         INTO TABLE @lt_vbrk
         WHERE erdat IN @s_erdat.

  CHECK lt_vbrk[] IS NOT INITIAL.
  SELECT vbeln,
         posnr,
         matnr
         FROM vbrp
         INTO TABLE @lt_vbrp
         FOR ALL ENTRIES IN @lt_vbrk
         WHERE vbeln = @lt_vbrk-vbeln.

  LOOP AT lt_vbrp ASSIGNING FIELD-SYMBOL(<fs_vbrp>).
    READ TABLE lt_vbrk ASSIGNING FIELD-SYMBOL(<fs_vbrk>) WITH KEY by_vbeln COMPONENTS vbeln = <fs_vbrp>-vbeln.
    CHECK sy-subrc = 0.
    ls_final-vbeln = <fs_vbrp>-vbeln.
    ls_final-posnr = <fs_vbrp>-posnr.
    ls_final-erdat = <fs_vbrk>-erdat.
    ls_final-matnr = <fs_vbrp>-matnr.
    APPEND ls_final TO lt_final.
    CLEAR ls_final.
  ENDLOOP.


ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  INNER_JOIN
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM inner_join .
  TYPES : BEGIN OF ty_final,
            vbeln TYPE vbrk-vbeln,
            erdat TYPE vbrk-erdat,
            posnr TYPE vbrp-posnr,
            matnr TYPE vbrp-matnr,
          END OF ty_final.
  DATA : lt_final TYPE STANDARD TABLE OF ty_final.

  DATA : ls_final TYPE ty_final.

  SELECT a~vbeln,
         a~erdat,
         b~posnr,
         b~matnr
         FROM vbrk AS a
         INNER JOIN vbrp AS b
         ON b~vbeln = a~vbeln
         INTO TABLE @lt_final
         WHERE a~erdat IN @s_erdat.

ENDFORM.

Results :

 Join is good performance

  VBRP Vs MARA Performance 


*&---------------------------------------------------------------------*
*& Report  ZJOIN_PERFORMANCE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zjoin_performance.

TABLES : vbrk.

DATA : v_start_time TYPE timestampl,
       v_end_time   TYPE timestampl,
       v_diff_w     TYPE p DECIMALS 5.

SELECT-OPTIONS : s_erdat FOR vbrk-erdat.

START-OF-SELECTION.

  DO 5 TIMES.

    GET TIME STAMP FIELD v_start_time.

    PERFORM for_all_entries.

    GET TIME STAMP FIELD v_end_time.
    v_diff_w = v_end_time - v_start_time.

    WRITE :/(30) 'for all entries', v_diff_w.
    CLEAR : v_start_time, v_end_time, v_end_time.

    GET TIME STAMP FIELD v_start_time.

    PERFORM inner_join.

    GET TIME STAMP FIELD v_end_time.
    v_diff_w = v_end_time - v_start_time.

    WRITE :/(30) 'Inner Join', v_diff_w.
    CLEAR : v_start_time, v_end_time, v_end_time.

  ENDDO.

END-OF-SELECTION.
*&---------------------------------------------------------------------*
*&      Form  FOR_ALL_ENTRIES
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM for_all_entries .

  TYPES : BEGIN OF ty_final,
            vbeln TYPE vbrk-vbeln,
            posnr TYPE vbrp-posnr,
            matnr TYPE vbrp-matnr,
            ersda TYPE mara-ersda,
          END OF ty_final.

  TYPES : BEGIN OF ty_vbrp,
            vbeln TYPE vbrk-vbeln,
            posnr TYPE vbrp-posnr,
            matnr TYPE vbrp-matnr,
          END OF ty_vbrp.

  TYPES : BEGIN OF ty_mara,
            matnr TYPE vbrp-matnr,
            ersda TYPE mara-ersda,
          END OF ty_mara.

  DATA : lt_vbrp  TYPE STANDARD TABLE OF ty_vbrp,
         lt_mara  TYPE STANDARD TABLE OF ty_mara WITH UNIQUE SORTED KEY by_matnr COMPONENTS matnr,
         lt_final TYPE STANDARD TABLE OF ty_final.

  DATA : ls_final TYPE ty_final.

  SELECT vbeln,
         posnr,
         matnr
         FROM vbrp
         INTO TABLE @lt_vbrp
         WHERE erdat IN @s_erdat.

  CHECK lt_vbrp[] IS NOT INITIAL.

  DATA(lt_vbrp_temp) = lt_vbrp[].
  SORT lt_vbrp_temp ASCENDING BY matnr.
  DELETE ADJACENT DUPLICATES FROM lt_vbrp_temp COMPARING matnr.

  SELECT matnr,
         ersda
         FROM mara
         INTO TABLE @lt_mara
         FOR ALL ENTRIES IN @lt_vbrp_temp
         WHERE matnr = @lt_vbrp_temp-matnr.

  LOOP AT lt_vbrp ASSIGNING FIELD-SYMBOL(<fs_vbrp>).
    READ TABLE lt_mara ASSIGNING FIELD-SYMBOL(<fs_mara>) WITH KEY by_matnr COMPONENTS matnr = <fs_vbrp>-matnr.
    CHECK sy-subrc = 0.
    ls_final-vbeln = <fs_vbrp>-vbeln.
    ls_final-posnr = <fs_vbrp>-posnr.
    ls_final-matnr = <fs_vbrp>-matnr.
    ls_final-ersda = <fs_mara>-ersda.
    APPEND ls_final TO lt_final.
    CLEAR ls_final.
  ENDLOOP.


ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  INNER_JOIN
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM inner_join .
  TYPES : BEGIN OF ty_final,
            vbeln TYPE vbrk-vbeln,
            posnr TYPE vbrp-posnr,
            matnr TYPE vbrp-matnr,
            ersda TYPE mara-ersda,
          END OF ty_final.

  DATA : lt_final TYPE STANDARD TABLE OF ty_final.

  SELECT a~vbeln,
         a~posnr,
         a~matnr,
         b~ersda
         FROM vbrp AS a
         INNER JOIN mara AS b
         ON b~matnr = a~matnr
         INTO TABLE @lt_final.
ENDFORM.


Results :


 For all entries good performance.

Note : Performance is different in HANA DB. try to use Join as much as possible.

Performance READ TABLE with Secondary Table Key Vs Binary Search

Secondary Table key

Access to an internal table using a secondary key is always optimized. This allows additional optimized keys to be introduced for sorted and hashed tables as well as optimized key accesses for standard tables.

• With Unique Sorted Key – Immediate update. Table key will be up to date
• With Non-Unique Sorted Key – Lazy update. Key will be update after first table access. Accessing same record 2^nd time is more faster.

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-us/abenitab_key_secondary_update.htm

Binary Search

• We are using Binary search Instead linear search. Need to sort internal table before read

https://help.sap.com/doc/saphelp_nw70/7.0.31/en-US/fc/eb373d358411d1829f0000e829fbfe/content.htm?no_cache=true

Secondary Table key (WITH UNIQUE SORTED KEY) Vs Binary Search

REPORT  zperformance_test_uk.

DATA: lv_sta_time TYPE timestampl,
      lv_end_time TYPE timestampl,
      lv_diff_w   TYPE p DECIMALS 5,
      lv_diff_f   LIKE lv_diff_w.


PARAMETERS : p_count TYPE c LENGTH 10 OBLIGATORY.

START-OF-SELECTION.


  TYPES : BEGIN OF ty_vbrp,
            vbeln TYPE vbrp-vbeln,
            posnr TYPE vbrp-posnr,
            matnr TYPE vbrp-matnr,
          END OF ty_vbrp.

  DATA : lt_vbrp         TYPE STANDARD TABLE OF ty_vbrp.


  TYPES : BEGIN OF ty_mara,
            matnr TYPE vbrp-matnr,
          END OF ty_mara.

  DATA : lt_mara         TYPE STANDARD TABLE OF ty_mara,
         lt_mara_sec_key TYPE STANDARD TABLE OF ty_mara WITH UNIQUE SORTED KEY by_matnr COMPONENTS matnr.


  SELECT vbeln
         posnr
         matnr
         FROM vbrp
         INTO TABLE lt_vbrp
         UP TO p_count ROWS.

  IF lt_vbrp[] IS NOT INITIAL.

    SELECT matnr
           FROM mara
           INTO TABLE lt_mara.
*      Removed FOR ALL ENTRIES IN to get more data
*           FOR ALL ENTRIES IN lt_vbrp
*           WHERE matnr = lt_vbrp-matnr.
    lt_mara_sec_key = lt_mara.

  ENDIF.


  WRITE: /(50) 'WITH UNIQUE SORTED KEY'.

  DO 5 TIMES.

    GET TIME STAMP FIELD lv_sta_time.

    LOOP AT lt_vbrp ASSIGNING FIELD-SYMBOL(<fs_vbrp>).
      READ TABLE lt_mara_sec_key TRANSPORTING NO FIELDS WITH TABLE KEY by_matnr COMPONENTS matnr = <fs_vbrp>-matnr.
    ENDLOOP.


    GET TIME STAMP FIELD lv_end_time.

    lv_diff_w = lv_end_time - lv_sta_time.
    WRITE: /(30) 'Secondary Key', lv_diff_w.
    CLEAR : lv_diff_w, lv_end_time, lv_sta_time.

*******************************************************************************************************************
    GET TIME STAMP FIELD lv_sta_time.

    SORT lt_mara ASCENDING BY matnr.
    LOOP AT lt_vbrp ASSIGNING <fs_vbrp>.
      READ TABLE lt_mara TRANSPORTING NO FIELDS WITH KEY matnr = <fs_vbrp>-matnr
                                           BINARY SEARCH.
    ENDLOOP.


    GET TIME STAMP FIELD lv_end_time.

    lv_diff_w = lv_end_time - lv_sta_time.
    WRITE: /(30) 'Binary Search', lv_diff_w.
    CLEAR : lv_diff_w, lv_end_time, lv_sta_time.
    WRITE: /(30) '----------------------------'.

  ENDDO.

Results:

Immediate update. Table Secondary key is faster

LOOP AT performance Work area Vs Inline Work Area Vs Field Symbol Vs Inline Field Symbol

We are all using field symbol and work area for loop. But, we didn't measure, which is good performance.

Most of the time, We use work area to process the internal table. We can increase program performance sing the field-symbols.

Demo program

REPORT zloop_performance_test.

DATA : v_start_time TYPE timestampl,
       v_end_time   TYPE timestampl,
       v_diff_w     TYPE p DECIMALS 5.

TYPES : BEGIN OF ty_vbrp,
          vbeln TYPE vbrp-vbeln,
        END OF ty_vbrp.

DATA : lt_vbrp TYPE STANDARD TABLE OF ty_vbrp,
       ls_vbrp TYPE ty_vbrp.

FIELD-SYMBOLS : <fs_vbrp> TYPE ty_vbrp.

PARAMETERS p_count TYPE n LENGTH 6.

* Start of selection

START-OF-SELECTION.

* Get billing item data
  SELECT vbeln
      FROM vbap
      INTO TABLE lt_vbrp
      UP TO p_count ROWS.

END-OF-SELECTION.

* 1 . Loop with work area

  GET TIME STAMP FIELD v_start_time.

  LOOP AT lt_vbrp INTO ls_vbrp.
  ENDLOOP.

  GET TIME STAMP FIELD v_end_time.
  v_diff_w = v_end_time - v_start_time.

  WRITE :/(30) 'Loop with Work area', v_diff_w.
  CLEAR : v_start_time, v_end_time, v_end_time.
* 2 . Loop with work area inline declaration
  GET TIME STAMP FIELD v_start_time.

  LOOP AT lt_vbrp INTO DATA(ls_vbrp_inline).
  ENDLOOP.

  GET TIME STAMP FIELD v_end_time.

  v_diff_w = v_end_time - v_start_time.

  WRITE:/(30) 'Loop with Inline Work area', v_diff_w.
  CLEAR : v_start_time, v_end_time, v_end_time.
*   3 . Loop with Field symbol
  GET TIME STAMP FIELD v_start_time.

  LOOP AT lt_vbrp ASSIGNING <fs_vbrp>.
  ENDLOOP.

  GET TIME STAMP FIELD v_end_time.

  v_diff_w = v_end_time - v_start_time.

  WRITE:/(30) 'Loop with Field Symbol', v_diff_w.
  CLEAR : v_start_time, v_end_time, v_end_time.
* 4 . Loop with Field symbol inline declaration

  GET TIME STAMP FIELD v_start_time.

  LOOP AT lt_vbrp ASSIGNING FIELD-SYMBOL(<fs_vbrp_inline>).
  ENDLOOP.

  GET TIME STAMP FIELD v_end_time.

  v_diff_w = v_end_time - v_start_time.

  WRITE:/(30) 'Loop with Inline Field Symbol', v_diff_w.
  CLEAR : v_start_time, v_end_time, v_end_time.

Results statistics