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.
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.
Solution :
Use Inner Join always for header item table data selection.
Example: VBRK Join VBRP
LIKP Join LIPS
VBAK Join VBAP
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
*&---------------------------------------------------------------------*
*& 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.
hir im fresher,
ReplyDelete1.how your saying its hana db.
2. what will happen if we connect joins for master data to transaction data