Improving Internal Table Performance Using Secondary Keys

Objective

After completing this lesson, you will be able to improve internal table performance using secondary keys

Secondary Keys

The video outlines the difference between reading an internal table with without its key.

To speed up the access to the internal table using fields that are not part of the primary key, you can add one or more secondary keys to the definition of your internal table.

As shown in the figure, a secondary key of an internal table can be either sorted or hashed. As with the primary key of internal tables, a sorted key may be either unique or non-unique, while a hashed key must always be unique.

Watch this video to know how you can use a secondary key to improve internal table performance with secondary keys.

If an internal table has a non-unique secondary key, the system does not create the key until the program addresses the table using the key. Creating the key for the first time costs extra runtime, and consequently the first read access is generally more expensive than it would have if you had not used the key. However, once the key exists, subsequent read accesses are very fast (and considerably faster than reading the table without the secondary key).

If you change the table contents, the system does not automatically update the index, but waits to see if there are any more read accesses that use the secondary key. At the first subsequent read access, the system updates the secondary key. This is not as expensive as creating the key, but there are moderate update costs.

A unique secondary key has two purposes – firstly to speed up access to the table, and secondly to ensure that the key combination of all of the rows in the table is unique. Consequently, the system must always keep the key up to date and uses a different update strategy to secondary keys that are non-unique.

Any operation that changes the contents of the internal table (filling or changing its contents) leads to the index either being created (first access) or updated (subsequent accesses). Read accesses are always fast because the secondary index is always already up to date.

Try It Out: Secondary Keys

  1. Create a new class that implements the interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( )
    Code Snippet
    1234567891011121314
    
data(object) = new lcl_flights( ).


* object->read_primary( ).
object->read_non_key( ).
object->read_secondary_1( ).
object->read_secondary_2( ).
object->read_secondary_3( ).


out->write( 'Done' ).
  3. Switch to the Local Types tab and copy the following code snippet into the editor:
    Code Snippet
    1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162
    
CLASS lcl_flights DEFINITION.
PUBLIC SECTION.
METHODS constructor.
METHODS read_primary.
METHODS read_non_key.
METHODS read_secondary_1.
METHODS read_secondary_2.
METHODS read_secondary_3.


PRIVATE SECTION.
DATA connections TYPE SORTED TABLE OF Zs4d401_flights WITH NON-UNIQUE KEY carrier_id connection_Id flight_date.
DATA connections_sk TYPE SORTED TABLE OF Zs4d401_flights WITH NON-UNIQUE KEY carrier_id connection_id flight_date
WITH NON-UNIQUE SORTED KEY k_plane COMPONENTS plane_type_id.


ENDCLASS.


CLASS lcl_flights IMPLEMENTATION.


METHOD constructor.
SELECT FROM Zs4d401_flights FIELDS * INTO TABLE @connections.
SELECT FROM Zs4d401_flights FIELDS * INTO TABLE @connections_sk.
ENDMETHOD.


METHOD read_non_key.
LOOP AT connections INTO DATA(connection) WHERE plane_type_id = '737-800'.
ENDLOOP.
ENDMETHOD.


METHOD read_primary.
DATA count TYPE i VALUE 1.
LOOP AT connections INTO DATA(connection) WHERE carrier_id = 'LH' AND connection_id = '0405' .
count += 1.
ENDLOOP.
ENDMETHOD.


METHOD read_secondary_1.
LOOP AT connections_sk INTO DATA(connection) USING KEY k_plane WHERE plane_type_id = '737-800'.
ENDLOOP.
ENDMETHOD.


METHOD read_secondary_2.
LOOP AT connections_sk INTO DATA(connection) USING KEY k_plane WHERE plane_type_id = '737-800'.
ENDLOOP.
ENDMETHOD.
METHOD read_secondary_3.
LOOP AT connections_sk INTO DATA(connection) USING KEY k_plane WHERE plane_type_id = '737-800'.
ENDLOOP.


ENDMETHOD.


ENDCLASS.
  4. Select Ctrl + F3 to activate the class.
  5. Right-click in the Editor and choose Profile As ... → ABAP Application (Console).
  6. In the Details, uncheck the checkbox SQL database access and choose Finish.
  7. Switch to the ABAP Profiler perspective and double-click on the trace item (you may need to refresh the display first) .
  8. Choose Hit List.
  9. Compare the runtimes of the methods read_primary( ), read_secondary_1( ), read_secondary_2( ) and read_secondary_3( ).

Define and Use a Secondary Key

You want to further improve the access to static attribute flights_buffer. Instead of a sorted table with just one key, you define it as a hashed table with an additional sorted secondary key, to optimize access where only the carrier_id field is specified.

Template:

  • /LRN/CL_S4D401_ITS_TABLE_KIND (Global Class)

Solution:

  • /LRN/CL_S4D401_ITS_SEC_KEY (Global Class)

Task 1: Copy Template (Optional)

Copy the template class. If you finished the previous exercise, you can skip this task and continue editing your class ZCL_##_SOLUTION.

Steps

  1. Copy class /LRN/CL_S4D401_ITS_TABLE_KIND to a class in your own package (suggested name: ZCL_##_SOLUTION, where ## stands for your group number).

    1. In the Project Explorer view, right-click class /LRN/CL_S4D401_ITS_TABLE_KIND to open the context menu.

    2. From the context menu, choose Duplicate ....

    3. Enter the name of your package in the Package field. In the Name field, enter the name ZCL_##_SOLUTION, where ## stands for your group number.

    4. Adjust the description and choose Next.

    5. Confirm the transport request and choose Finish.

  2. Activate the copy.

    1. Press Ctrl + F3 to activate the class.

Task 2: Define Secondary Key

In local class LCL_PASSENGER_FLIGHTS, turn the flights_buffer attribute into a hashed table. Then define a secondary key that optimizes access with only the carrier_id field.

Steps

  1. Navigate to the definition of static attribute flights_buffer in local class LCL_PASSENGER_FLIGHT.

    1. For example, expand node LCL_PASSENGER_FLIGHT in the Outline view and choose FLIGHTS_BUFFER.

  2. Adjust the definition of the flights_buffer attribute. Set the table kind to HASHED TABLE and adjust the primary key definition accordingly.

    1. The key of hashed tables has to be unique.

    2. Adjust the code as follows:

      Code Snippet
      123456789
      
* CLASS-DATA: flights_buffer
*       TYPE SORTED TABLE OF st_flights_buffer
*       WITH NON-UNIQUE KEY carrier_id connection_id flight_date.
 CLASS-DATA: flights_buffer 
        TYPE HASHED TABLE OF st_flights_buffer
        WITH UNIQUE KEY carrier_id connection_id flight_date.

  3. Define a secondary key (suggested name: sk_carrier) with key field carrier_id.

    1. Adjust the code as follows:

      Code Snippet
      123456
      
  CLASS-DATA: flights_buffer
        TYPE HASHED TABLE OF st_flights_buffer
        WITH UNIQUE KEY carrier_id connection_id flight_date
        WITH NON-UNIQUE SORTED KEY sk_carrier COMPONENTS carrier_id.

  4. Analyze the syntax errors and warnings on the Problems view.

    1. Analyze the Problems view below the Editor view.

    2. Double-click a finding to navigate to the affected code.

Task 3: Use Secondary Key

In local class LCL_PASSENGER_FLIGHTS, adjust the accesses to static attribute flights_buffer where only the field carrier_id is specified. Make the ABAP run time use the new secondary key.

Steps

  1. Navigate to the call of function line_exists. Make the statement use the secondary key.

    1. Adjust the code as follows:

      Code Snippet
      1234567
      
  IF NOT line_exists(  flights_buffer[ 
                               KEY sk_carrier 
                        COMPONENTS carrier_id = i_carrier_id 
                       ] 
                    ).

      Note

      The COMPONENTS addition is optional and can be omitted. We use it here for clarification.

  2. Navigate to the table comprehension. Make the statement use the secondary key.

    1. Adjust the code as follows:

      Code Snippet
      12345678910111213
      
  r_result = VALUE #(
                 FOR <flight> IN flights_buffer
               USING KEY sk_carrier
               WHERE ( carrier_id = i_carrier_id )
               ( NEW lcl_passenger_flight(
                       i_carrier_id    = <flight>-carrier_id
                       i_connection_id = <flight>-connection_id
                       i_flight_date   = <flight>-flight_date
                 )
               )
             ).

  3. Activate and test your global class as console app.

    1. Press Ctrl + F3.

    2. Press F9.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn