REPORT ztest_selects LINE-SIZE 80 MESSAGE-ID 00.
DATA: t001 TYPE t001,
bkpf TYPE bkpf.
SELECT-OPTIONS: s_bukrs FOR bkpf-bukrs MEMORY ID buk OBLIGATORY.
SELECT-OPTIONS: s_belnr FOR bkpf-belnr MEMORY ID bln OBLIGATORY.
PARAMETERS: p_gjahr LIKE bkpf-gjahr MEMORY ID gjr OBLIGATORY.
SELECTION-SCREEN ULINE.
PARAMETERS: p_loop1 TYPE i OBLIGATORY
DEFAULT 5,
p_loop2 TYPE i OBLIGATORY
DEFAULT 10.
TYPES: BEGIN OF t001_type,
bukrs TYPE t001-bukrs,
END OF t001_type,
BEGIN OF bkpf_type,
bukrs TYPE bkpf-bukrs,
belnr TYPE bkpf-belnr,
gjahr TYPE bkpf-gjahr,
END OF bkpf_type.
DATA: t001_int TYPE TABLE OF t001_type,
t001_wa TYPE t001_type,
bkpf_int TYPE TABLE OF bkpf_type,
bkpf_wa TYPE bkpf_type.
DATA: start TYPE i,
end TYPE i,
dif TYPE i.
START-OF-SELECTION.
DO p_loop1 TIMES.
PERFORM simple_select.
PERFORM nested_select.
PERFORM for_all_entries.
PERFORM inner_join.
PERFORM outer_join.
PERFORM sub_query.
PERFORM unqualified_select.
SKIP 1.
ENDDO.
*&---------------------------------------------------------------------*
*& Form simple_select
*&---------------------------------------------------------------------*
* First we get documents using a select statement that is
* fully qualified on the primary key. Because buffering may be an issue,
* the first select will be disregarded in this test. However, in real
* life, this would be the important time.
*----------------------------------------------------------------------*
FORM simple_select.
* Do an initial select of the documents we intend to get. Due to
* buffering, the first select may take much longer then the next one.
SELECT bukrs belnr gjahr
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs IN s_bukrs
AND belnr IN s_belnr
AND gjahr EQ p_gjahr.
IF sy-subrc <> 0.
MESSAGE ID '00' TYPE 'E' NUMBER '001' WITH
'No Data meets selection criteria'.
ENDIF.
* Next we get the same document using the same fully qualified select
* statement. We will use this in comparisons.
GET RUN TIME FIELD start.
DO p_loop2 TIMES.
SELECT bukrs belnr gjahr
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs IN s_bukrs
AND belnr IN s_belnr
AND gjahr EQ p_gjahr.
ENDDO.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for first SELECT (fully qualified)',
055 ':', dif, 'microseconds'.
ENDFORM. " simple_select
*&---------------------------------------------------------------------*
*& Form nested_select
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM nested_select.
* Use the same fully qualified SELECT, but this time nested. As usual,
* Ignore the first SELECT and use the subsequent ones for comparison.
REFRESH: bkpf_int.
SELECT bukrs FROM t001
INTO t001_wa
WHERE bukrs IN s_bukrs.
SELECT bukrs belnr gjahr
FROM bkpf
INTO bkpf_wa
WHERE bukrs EQ t001_wa-bukrs
AND belnr IN s_belnr
AND gjahr EQ p_gjahr.
APPEND bkpf_wa TO bkpf_int.
ENDSELECT.
ENDSELECT.
* Next we get the same document using the same fully qualified select
* statement. We will use this in comparisons.
GET RUN TIME FIELD start.
DO p_loop2 TIMES.
REFRESH: bkpf_int.
SELECT bukrs FROM t001
INTO t001_wa
WHERE bukrs IN s_bukrs.
SELECT bukrs belnr gjahr
FROM bkpf
INTO bkpf_wa
WHERE bukrs EQ t001_wa-bukrs
AND belnr IN s_belnr
AND gjahr EQ p_gjahr.
APPEND bkpf_wa TO bkpf_int.
ENDSELECT.
ENDSELECT.
ENDDO.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for second SELECT (nested)',
055 ':', dif, 'microseconds'.
ENDFORM. " nested_select
*&---------------------------------------------------------------------*
*& Form for_all_entries
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM for_all_entries .
* Use the same fully qualified SELECT, but this time with
* FOR ALL ENTRIES.
* Ignore the first set of SELECTs
SELECT bukrs
FROM t001
INTO TABLE t001_int
WHERE bukrs IN s_bukrs.
SELECT bukrs belnr gjahr
FROM bkpf
INTO TABLE bkpf_int
FOR ALL ENTRIES IN t001_int
WHERE bukrs EQ t001_int-bukrs
AND belnr IN s_belnr
AND gjahr EQ p_gjahr.
* Use these SELECTs in comparisons.
GET RUN TIME FIELD start.
DO p_loop2 TIMES.
SELECT bukrs
FROM t001
INTO TABLE t001_int
WHERE bukrs IN s_bukrs.
SELECT bukrs belnr gjahr
FROM bkpf
INTO TABLE bkpf_int
FOR ALL ENTRIES IN t001_int
WHERE bukrs EQ t001_int-bukrs
AND belnr IN s_belnr
AND gjahr EQ p_gjahr.
ENDDO.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for third SELECT (using FOR ALL ENTRIES)',
055 ':', dif, 'microseconds'.
ENDFORM. " for_all_entries
*&---------------------------------------------------------------------*
*& Form inner_join
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM inner_join .
* Use the same fully qualified SELECT, but this time with an INNER JOIN.
SELECT t001~bukrs bkpf~belnr bkpf~gjahr
FROM bkpf
INNER JOIN t001 ON
t001~bukrs EQ bkpf~bukrs
INTO TABLE bkpf_int
WHERE t001~bukrs IN s_bukrs
AND bkpf~belnr IN s_belnr
AND bkpf~gjahr EQ p_gjahr.
* Use this select in comparisons.
GET RUN TIME FIELD start.
DO p_loop2 TIMES.
SELECT t001~bukrs bkpf~belnr bkpf~gjahr
FROM bkpf
INNER JOIN t001 ON
t001~bukrs EQ bkpf~bukrs
INTO TABLE bkpf_int
WHERE t001~bukrs IN s_bukrs
AND bkpf~belnr IN s_belnr
AND bkpf~gjahr EQ p_gjahr.
ENDDO.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for fourth SELECT (using an INNER JOIN)',
055 ':', dif, 'microseconds'.
ENDFORM. " inner_join
*&---------------------------------------------------------------------*
*& Form outer_join
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM outer_join .
* Use the same fully qualified SELECT, but this time with an OUTER JOIN.
SELECT t001~bukrs bkpf~belnr bkpf~gjahr
FROM bkpf
LEFT OUTER JOIN t001 ON
t001~bukrs EQ bkpf~bukrs
INTO TABLE bkpf_int
WHERE bkpf~bukrs IN s_bukrs
AND bkpf~belnr IN s_belnr
AND bkpf~gjahr EQ p_gjahr.
* Use this select in comparisons.
GET RUN TIME FIELD start.
DO p_loop2 TIMES.
SELECT t001~bukrs bkpf~belnr bkpf~gjahr
FROM bkpf
LEFT OUTER JOIN t001 ON
t001~bukrs EQ bkpf~bukrs
INTO TABLE bkpf_int
WHERE bkpf~bukrs IN s_bukrs
AND bkpf~belnr IN s_belnr
AND bkpf~gjahr EQ p_gjahr.
ENDDO.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for fifth SELECT (using an OUTER JOIN)',
055 ':', dif, 'microseconds'.
ENDFORM. " outer_join
*&---------------------------------------------------------------------*
*& Form sub_query
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM sub_query .
* And a sub-query
SELECT bukrs belnr gjahr
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs IN
( SELECT bukrs
FROM t001
WHERE bukrs IN s_bukrs )
AND belnr IN s_belnr
AND gjahr EQ p_gjahr.
* Use this select in comparisons.
GET RUN TIME FIELD start.
DO p_loop2 TIMES.
SELECT bukrs belnr gjahr
FROM bkpf
INTO TABLE bkpf_int
WHERE bukrs IN
( SELECT bukrs
FROM t001
WHERE bukrs IN s_bukrs )
AND belnr IN s_belnr
AND gjahr EQ p_gjahr.
ENDDO.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for sixth SELECT (using a sub-query)',
055 ':', dif, 'microseconds'.
ENDFORM. " sub_query
*&---------------------------------------------------------------------*
*& Form unqualified_select
*&---------------------------------------------------------------------*
* Compare the above results with a SELECT that is only partially
* qualified.
*----------------------------------------------------------------------*
FORM unqualified_select.
* Ignore the first SELECT
SELECT bukrs belnr gjahr
FROM bkpf
INTO TABLE bkpf_int
WHERE belnr IN s_belnr
AND gjahr EQ p_gjahr.
* Use this select in comparisons.
GET RUN TIME FIELD start.
DO p_loop2 TIMES.
SELECT bukrs belnr gjahr
FROM bkpf
INTO TABLE bkpf_int
WHERE belnr IN s_belnr
AND gjahr EQ p_gjahr.
ENDDO.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for seventh SELECT (partially qualified)',
055 ':', dif, 'microseconds'.
ENDFORM. " unqualified_select
