This is a test message to test the length of the message box.
Login
ABAP Select from itab
Created by Software-Heroes

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.

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.

 

Source:
SAP Documentation - SELECT FROM @itab


Included topics:
Modernes ABAPSELECTInterne Tabelle
Comments (0)

ABAP - Performance for the SELECT

Category - ABAP

In this article we will look at a few special cases with the SELECT and examine the performance of these constructs. We'll show you the current alternatives and give you little tips while reading.

04/02/2021

ABAP - The new Select

Category - ABAP

The customization of the ABAP language also has implications and improvements for the well-known and important Select. In this article we want to briefly describe what has changed and what you get for benefits.

10/19/2018