ABAP - SELECT FROM @itab
Selecting via an internal table used to be realized with many lines of code, but today it also works practically via the select.
Table of contents
In this article we want to take a look at the extension of SELECT Stamenet for local tables and what we can use it for in a meaningful way. Afterwards, we also want to take a look at the performance when accessing the data.
Preparation
Before we can access data, we should first generate some dummy data. At this point, this data does not come from the database, but we generate it from our own structure and our own little logic.
DATA(lo_random_number) = NEW zcl_demo_random( id_min = 1 id_max = 99 ).
DATA(lo_random_currency) = NEW zcl_demo_random( id_min = 1 id_max = 3 ).
DO id_number_of_entries TIMES.
TRY.
DATA(ls_structure) = VALUE ts_structure(
guid = cl_system_uuid=>create_uuid_x16_static( )
number = lo_random_number->rand( )
text = |Item No. { sy-index }|
currency = SWITCH #( lo_random_currency->rand( )
WHEN 1 THEN 'EUR'
WHEN 2 THEN 'USD'
WHEN 3 THEN 'CHF'
)
).
CATCH cx_uuid_error.
CONTINUE.
ENDTRY.
INSERT ls_structure INTO TABLE rt_result.
ENDDO.
DATA(lo_random_index) = NEW zcl_demo_random( id_min = 1 id_max = id_number_of_entries ).
DATA(ld_done) = 0.
WHILE ld_done < id_number_of_gbp.
DATA(ld_pos) = lo_random_index->rand( ).
IF rt_result[ ld_pos ]-currency <> 'GBP'.
rt_result[ ld_pos ]-currency = 'GBP'.
ld_done += 1.
ENDIF.
ENDWHILE.
An internal table with random data is generated. In the first loop, the table is filled with random values, currencies and a continuous text. In the second loop we change a certain number of rows and exchange the currency.
Default access
Now we can use SELECT to access the generated internal table, as if we were reading from an database table, but we also need an alias for the table. Here are some examples:
" Normal Select with currency
SELECT *
FROM @lt_standard_data AS data
WHERE currency = 'GBP'
INTO TABLE @DATA(lt_currency_gbp).
out->write( lt_currency_gbp ).
" Select with fields
SELECT text, number
FROM @lt_standard_data AS data
WHERE currency = 'GBP'
AND number > 50
INTO TABLE @DATA(lt_fields_gbp).
out->write( lt_fields_gbp ).
" Count
SELECT COUNT(*)
FROM @lt_standard_data AS data
WHERE number < 10
INTO @DATA(ld_low_count).
out->write( ld_low_count ).
In the first access we read all fields and restrict to a currency, the result is the corresponding rows from the table. In the second access we only want to read two fields and have an extended query against two fields. And as you know it from SELECT, you can also generate a new internal table with your own row type using an inline declaration. In the third query we want to count records, which also works and returns the number of records with a number less than 10.
Advanced access
Let's now look at an extended case that is a bit more complex because we are also working with a sorting of the data here.
SELECT *
FROM @lt_standard_data AS data
WHERE number > 20 AND number < 30
ORDER BY number DESCENDING
INTO TABLE @DATA(lt_order)
UP TO 20 ROWS.
out->write( lines( lt_order ) ).
out->write( lt_order ).
We restrict the data, sort the result and want to read the first 20 rows of it. In this case we get a corresponding message from the compiler to read.
So what happened? Depending on the complexity and the form of access to the table, the system decides whether the query is to be carried out on the ABAP stack or whether a temporary table is to be created in order to carry out the operations on it. The compiler uses this form to indicate that the query cannot be carried out on the ABAP stack.
Hint: You can read more about this behavior in the official documentation (see below).
Performance
Now what about the performance when accessing the data via the different methods? To do this, we take the table with 2 million records and execute the SELECT on the standard table and the sorted table with a secondary key. In this case, we also use the new FILTER statement to get the appropriate data.
SELECT *
FROM @lt_standard_data AS data
WHERE currency = 'GBP'
INTO TABLE @DATA(lt_standard_gbp).
SELECT *
FROM @lt_sorted_data AS data
WHERE currency = 'GBP'
INTO TABLE @DATA(lt_sorted_gbp).
DATA(lt_filtered_gbp) = FILTER #( lt_sorted_data USING KEY curr_key WHERE currency = 'GBP ' ).
Accordingly, we also need a time measurement in order to be able to carry out the comparison. The full example is below. So we now get the corresponding time measurement:
Using the example, we can easily understand that the use of the SELECT does not work without a loss of performance. Accessing the sorted table takes over half a second. Access via FILTER hardly requires any performance. It is also noticeable that access to the standard table is faster than to the sorted data. We can't explain this behavior exactly, but we think it has to do with the persistence of the data and the management of the secondary key.
Example
As always, you can find the full example we used at the end of the article. You can find more information about the random number generator in another article.
CLASS zcl_select_with_itab DEFINITION PUBLIC FINAL CREATE PUBLIC.
PUBLIC SECTION.
INTERFACES if_oo_adt_classrun.
TYPES:
BEGIN OF ts_structure,
guid TYPE sysuuid_x16,
number TYPE i,
text TYPE c LENGTH 40,
currency TYPE waers,
END OF ts_structure,
tt_standard TYPE STANDARD TABLE OF ts_structure WITH EMPTY KEY,
tt_sorted TYPE SORTED TABLE OF ts_structure
WITH UNIQUE KEY guid
WITH NON-UNIQUE SORTED KEY curr_key COMPONENTS currency.
PROTECTED SECTION.
PRIVATE SECTION.
METHODS:
get_initial_data
IMPORTING
id_number_of_entries TYPE i DEFAULT 1000
id_number_of_gbp TYPE i DEFAULT 5
RETURNING VALUE(rt_result) TYPE tt_standard.
ENDCLASS.
CLASS zcl_tselect_with_itab IMPLEMENTATION.
METHOD if_oo_adt_classrun~main.
DATA(lt_standard_data) = get_initial_data( id_number_of_entries = 2000000 ).
DATA(lt_sorted_data) = CORRESPONDING tt_sorted( lt_standard_data ).
" Normal Select with currency
SELECT *
FROM @lt_standard_data AS data
WHERE currency = 'GBP'
INTO TABLE @DATA(lt_currency_gbp).
out->write( lt_currency_gbp ).
" Select with fields
SELECT text, number
FROM @lt_standard_data AS data
WHERE currency = 'GBP'
AND number > 50
INTO TABLE @DATA(lt_fields_gbp).
out->write( lt_fields_gbp ).
" Count
SELECT COUNT(*)
FROM @lt_standard_data AS data
WHERE number < 10
INTO @DATA(ld_low_count).
out->write( ld_low_count ).
" Select with order and limitation
SELECT *
FROM @lt_standard_data AS data
WHERE number > 20 AND number < 30
ORDER BY number DESCENDING
INTO TABLE @DATA(lt_order)
UP TO 20 ROWS.
out->write( lines( lt_order ) ).
out->write( lt_order ).
" Performance check
DATA(lo_timer) = NEW zcl_demo_runtime( ).
DATA(ld_start) = lo_timer->get_runtime( ).
SELECT *
FROM @lt_standard_data AS data
WHERE currency = 'GBP'
INTO TABLE @DATA(lt_standard_gbp).
out->write( |Standard: { lo_timer->get_runtime( ) - ld_start }| ).
ld_start = lo_timer->get_runtime( ).
SELECT *
FROM @lt_sorted_data AS data
WHERE currency = 'GBP'
INTO TABLE @DATA(lt_sorted_gbp).
out->write( |Sorted: { lo_timer->get_runtime( ) - ld_start }| ).
ld_start = lo_timer->get_runtime( ).
DATA(lt_filtered_gbp) = FILTER #( lt_sorted_data USING KEY curr_key WHERE currency = 'GBP ' ).
out->write( |Filter: { lo_timer->get_runtime( ) - ld_start }| ).
ENDMETHOD.
METHOD get_initial_data.
DATA(lo_random_number) = NEW zcl_demo_random( id_min = 1 id_max = 99 ).
DATA(lo_random_currency) = NEW zcl_demo_random( id_min = 1 id_max = 3 ).
DO id_number_of_entries TIMES.
TRY.
DATA(ls_structure) = VALUE ts_structure(
guid = cl_system_uuid=>create_uuid_x16_static( )
number = lo_random_number->rand( )
text = |Item No. { sy-index }|
currency = SWITCH #( lo_random_currency->rand( )
WHEN 1 THEN 'EUR'
WHEN 2 THEN 'USD'
WHEN 3 THEN 'CHF'
)
).
CATCH cx_uuid_error.
CONTINUE.
ENDTRY.
INSERT ls_structure INTO TABLE rt_result.
ENDDO.
DATA(lo_random_index) = NEW zcl_demo_random( id_min = 1 id_max = id_number_of_entries ).
DATA(ld_done) = 0.
WHILE ld_done < id_number_of_gbp.
DATA(ld_pos) = lo_random_index->rand( ).
IF rt_result[ ld_pos ]-currency <> 'GBP'.
rt_result[ ld_pos ]-currency = 'GBP'.
ld_done += 1.
ENDIF.
ENDWHILE.
ENDMETHOD.
ENDCLASS.
Conclusion
Do you still need READ TABLE when there is also SELECT? The answer is not easy, especially when you look at the performance of the statement and there are already functions that can do something similar. In special and complex cases, however, it is worth using the SELECT statement to make the code leaner.
