In this recipe, we will see how we can replace for all entries within a select statement with ranges in the where clause. The ranges maybe used to improve performance. This two-table example is just for illustrative purpose and comparison. You may apply the concepts to other tables and fields. You may or may not further refine this based on your requirement.
We will create a simple program that uses the for all entries addition in the select statement for the two tables cobk and coep. We declare two internal tables t_header and t_line_items. The t_header table contains one field for the belnr document number, whereas the items table t_line_items has three fields belnr, period (period), and amount wtgbtr (amount).
We first select up to 35,000 numbers from table cobk into the internal table t_header based on the code kokrs equal to 1000. Next, the table coep is read for kokrs 1000 and for all document numbers contained in table t_header using the for all entries addition. The data is read and stored in table t_line_items. We also make sure that the for all entries table is not empty.
In this recipe, we will see how we can improve the performance of the code by replacing the for all entries construct with a range of document numbers. For this, we need to add some additional code before the second select statement.
For forming ranges of document numbers, we will use the function module WLF_CREATE_RANGE_FOR_WBELN. You may copy or write your own code as well but since we have both the number used having same length and type (character 10), the function module WLF_CREATE_RANGE_FOR_WBELN may be used. The steps for writing the additional code for forming ranges are shown as follows:
- We declare one range table
r_headerbased on the typeWBELN_RAN_ITAB. The structure ofWBELN_RAN_ITABis shown as follows:
- In addition, a
temp_headervariable is declared based on the typeWBRK_KEY_ITAB(our function module will accept this type). - We then sort the table
t_headerand assign thet_headertable to ourtemp_headerinternal table. - The function module
WLF_CREATE_RANGE_FOR_WBELNis then called. We pass thetemp_headervariable to the function module for exporting parameterit_wbeln. The function module creates the ranges from the values in thetemp_headertable and returns the range in the tabler_header. - Finally, the
selectstatement is written with thefor all entriespart replaced with the rangesr_header.
Let us now see how the additional code works. The document numbers are passed to the function module. The various numbers ( in our case 35000) are used for creating ranges and returned in the table r_header.
The r_header table is then used in the select statement for reading data from table coep. This technique works best when the document numbers passed to the function modules are close together, so that less rows exists in the ranges table.
The efficiency of the Ranges code may be easily demonstrated. We use the GET RUNTIME statement to find out the relative runtime of other various program segments (such as original select statement part, the function module call, and the select statement using the Ranges table). The following are the runtimes (in microseconds) displayed on the screen:
The t_header table contains 35,000 rows. It can be clearly seen that the time taken by the select statement with for all entries in clause is 1.3 seconds. The function module consumes 0.23 seconds, whereas the select statement having the Range table takes 0.3 seconds. Hence, the combined time for our replacement code is 0.53 seconds (0.23 + 0.30 ) seconds. This is faster than the 1.3 seconds taken by the original select statement using the for all entries clause.