Nested INNER JOIN Statements May be Ineffective or inefficient | SAP Programming | ABAP

"Using several nested INNER JOIN statements can be inefficient and cause time out if the tables become too big in the future."

Joins here (in ABAP) are not those Native SQL Joins.  If you are talking about the Core RDBMS, which mean Oracle or SQL Server, then Undoubtedly Joins are the best.

In ABAP, these joins are first split by the ABAP processor and then sent to the database, with the increase in DATA in production system, these joins tend to give way if your database keeps growing larger and larger.

You should rather used "FOR ALL ENTRIES IN" (Tabular conditions), which is a much effecient way as far as performance is concerned.

For example :

DATA: BEGIN OF LINE,
        CARRID   TYPE SPFLI-CARRID,
        CONNID   TYPE SPFLI-CONNID,
        CITYFROM TYPE SPFLI-CITYFROM,
        CITYTO   TYPE SPFLI-CITYTO,
      END OF LINE,
      ITAB LIKE TABLE OF LINE.

LINE-CITYFROM = 'FRANKFURT'.
LINE-CITYTO   = 'BERLIN'.
APPEND LINE TO ITAB.

LINE-CITYFROM = 'NEW YORK'.
LINE-CITYTO   = 'SAN FRANCISCO'.
APPEND LINE TO ITAB.

SELECT CARRID CONNID CITYFROM CITYTO
INTO   CORRESPONDING FIELDS OF LINE
FROM   SPFLI
FOR ALL ENTRIES IN ITAB
WHERE  CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

  WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.

ENDSELECT.

read more...

Purpose of Using PACKAGE SIZE in Select Statement in SAP ABAP Programming

Package size can be used if you for example only want to finish processing a limited amount of data at a time due to lack of memory.

The example below read 50 records at a time from VBAK into an internal table, and selects the corresponding entries from vbap into an internal table. Then the two internal tables can be processed, and the next 50 records from VBAk can be read. Remember to reinitialize tha tables before the next read.



REPORT z_test.


TYPES:
BEGIN OF t_vbak,
vbeln LIKE vbak-vbeln,
erdat LIKE vbak-erdat,
END OF t_vbak,


BEGIN OF t_vbap,
posnr LIKE vbap-posnr,
matnr LIKE vbap-matnr,
meins LIKE vbap-meins,
END OF t_vbap,


BEGIN OF t_report,
vbeln LIKE vbak-vbeln,
erdat LIKE vbak-erdat,
posnr LIKE vbap-posnr,
matnr LIKE vbap-matnr,
meins LIKE vbap-meins,
END OF t_report.


DATA:
li_vbak TYPE t_vbak OCCURS 0,
l_vbak TYPE t_vbak,
li_vbap TYPE t_vbap OCCURS 0,
l_vbap TYPE t_vbap,
li_report TYPE t_report OCCURS 0,
l_report TYPE t_report.


START-OF-SELECTION.
SELECT vbeln erdat
FROM vbak
INTO TABLE li_vbak PACKAGE SIZE 50.
SELECT posnr matnr meins
FROM vbap
INTO TABLE li_vbap
FOR ALL ENTRIES IN li_vbak
WHERE vbeln = li_vbak-vbeln.
IF sy-subrc = 0.
* Now you have the two internal tables li_vbak and li_vbap filled
* with data.
* Do something with the data - remember to reinitialize internal
* tables
ENDIF.
ENDSELECT.

read more...

Usage of 'for all entries' in Select Statement | SAP ABAP Programming

FORM data_retrieval.

DATA: ld_color(1) TYPE c.

DATA: BEGIN OF T_VBAP OCCURS 0,
VBELN LIKE VBAP-VBELN,
MATNR LIKE VBAP-MATNR,
POSNR LIKE VBAP-POSNR,
END OF T_VBAP.

DATA: BEGIN OF T_VBFA OCCURS 0,
VBELV LIKE VBFA-VBELV,
VBELN LIKE VBFA-VBELN,
VBTYP_N LIKE VBFA-VBTYP_N,

END OF T_VBFA.

DATA: BEGIN OF T_VBAK OCCURS 0,
VBELN LIKE VBAK-VBELN,
IHREZ LIKE VBAK-IHREZ,
END OF T_VBAK.

DATA: BEGIN OF T_KNA1 OCCURS 0,
KUNNR LIKE KNA1-KUNNR,
NAME1 LIKE KNA1-NAME1,
END OF T_KNA1.

DATA: BEGIN OF T_MAKT OCCURS 0,
MATNR LIKE MAKT-MATNR,
MAKTX LIKE MAKT-MAKTX,
END OF T_MAKT.

SELECT likp~vbeln likp~lifex likp~bldat likp~wadat likp~wadat_ist likp~kodat likp~lfart
likp~kunnr likp~vstel lips~posnv lips~lfimg lips~vrkme lips~lgmng lips~meins
lips~werks lips~lgort lips~charg lips~vbelv lips~posnr lips~matnr
lips~vbeln LIPS~VGBEL LIPS~VGPOS vbup~kosta vbup~wbsta vbup~posnr vbup~vbeln
* VBAK~IHREZ VBAK~VBELN VBAP~VBELN
INTO CORRESPONDING FIELDS OF TABLE it_itab
FROM ( likp
INNER JOIN lips
ON lips~vbeln = likp~vbeln
INNER JOIN vbup
ON vbup~posnr = lips~posnr
and VBUP~VBELN = LIPS~VBELN )
* left outer join VBAK
* on VBAK~VBELN = LIPS~VGBEL
* inner join VBAP
* on VBAP~VBELN = VBAK~VBELN )
WHERE likp~vbeln IN so_vbeln
AND likp~lifex IN so_lifex
AND likp~lfart IN so_lfart
AND likp~kunnr IN so_kunnr
AND likp~vstel IN so_vstel
AND likp~bldat IN so_bldat
AND likp~wadat_ist IN so_wadat
AND vbup~kosta IN so_kosta
AND vbup~wbsta IN so_wbsta
AND LIPS~LFIMG NE 0.

SELECT VBELN IHREZ INTO TABLE T_VBAK
FROM VBAK
FOR ALL ENTRIES IN IT_ITAB
WHERE VBELN = IT_ITAB-VGBEL.
* APPEND T_VBAK.
* ENDSELECT.

SELECT VBELN MATNR POSNR INTO TABLE T_VBAP
FROM VBAP
FOR ALL ENTRIES IN IT_ITAB
WHERE VBELN = IT_ITAB-VGBEL AND
MATNR = IT_ITAB-MATNR AND
POSNR = IT_ITAB-VGPOS.
* APPEND T_VBAP.
* ENDSELECT.

SELECT VBELV VBELN VBTYP_N INTO TABLE T_VBFA
FROM VBFA
FOR ALL ENTRIES IN IT_ITAB
WHERE VBELV = IT_ITAB-VBELN AND
VBTYP_N = 'M' .

SELECT KUNNR NAME1 INTO TABLE T_KNA1
FROM KNA1
FOR ALL ENTRIES IN IT_ITAB
WHERE KUNNR = IT_ITAB-KUNNR.
* APPEND T_KNA1.
* ENDSELECT.

SELECT MATNR MAKTX INTO TABLE T_MAKT
FROM MAKT
FOR ALL ENTRIES IN IT_ITAB
WHERE MATNR = IT_ITAB-MATNR.
* APPEND T_MAKT.
* ENDSELECT.


*Populate field with color attributes

LOOP AT it_itab INTO wa_ITAB.

* Populate color variable with colour properties

* Char 1 = C (This is a color property)

* Char 2 = 3 (Color codes: 1 - 7)

* Char 3 = Intensified on/off ( 1 or 0 )

* Char 4 = Inverse display on/off ( 1 or 0 )

* i.e. wa_ekko-line_color = 'C410'
REFRESH color.
colourize 'VBELN' 0. " .
WA_ITAB-farbe = color[].

ld_color = ld_color + 1.

* Only 7 colours so need to reset color value

IF ld_color = 3. "8

ld_color = 1.

ENDIF.

CONCATENATE 'C' ld_color '10' INTO wa_ITAB-line_color.
WA_ITAB-NAME1 = ''.
WA_ITAB-MAKTX = ''.
WA_ITAB-IHREZ = ''.
WA_ITAB-VBELV = ''.
READ TABLE T_KNA1 WITH KEY KUNNR = WA_ITAB-KUNNR.
IF SY-SUBRC = 0.
WA_ITAB-NAME1 = T_KNA1-NAME1.
ENDIF.
READ TABLE T_MAKT WITH KEY MATNR = WA_ITAB-MATNR.
IF SY-SUBRC = 0.
WA_ITAB-MAKTX = T_MAKT-MAKTX.
ENDIF.
READ TABLE T_VBAK WITH KEY VBELN = WA_ITAB-VGBEL.
IF SY-SUBRC = 0.
WA_ITAB-IHREZ = T_VBAK-IHREZ.
ENDIF.
READ TABLE T_VBFA WITH KEY VBELV = WA_ITAB-VBELN.
IF SY-SUBRC = 0.
WA_ITAB-VBELVA = T_VBFA-VBELN.
ENDIF.
* READ TABLE T_VBAP WITH KEY VBELN = WA_ITAB-VGBEL
* POSNR = WA_ITAB-VGPOS
* MATNR = WA_ITAB-MATNR.
* IF SY-SUBRC = 0.
* WA_ITAB-IHREZ = T_VBAK-IHREZ.
* ENDIF.



* wa_ekko-line_color = 'C410'.

MODIFY it_itab FROM wa_itab.

ENDLOOP.

ENDFORM. " data_retrieval

read more...