Improving Internal Table Performance Using Secondary Keys
After completing this lesson, you will be able to:After completing this lesson, you will be able to:
- Improve internal table performance using secondary keys
Using 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: Working With Secondary Keys
- Create a new class that implements the interface IF_OO_ADT_CLASSRUN.
- Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( )
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' ).
- Switch to the Local Types tab and copy the following code snippet into the editor:
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.
- Select Ctrl + F3 to activate the class.
- Right-click in the Editor and choose Profile As ... → ABAP Application (Console).
- In the Details, uncheck the checkbox SQL database access and choose Finish.
- Switch to the ABAP Profiler perspective and double-click on the trace item (you may need to refresh the display first) .
- Choose Hit List.
- Compare the runtimes of the methods
read_primary( ), read_secondary_1( ), read_secondary_2( ) and read_secondary_3( ).
Save progress to your learning plan by logging in or creating an account
