Hi ABAPeople! In this long promised article, I will talk about primary keys and indexes in internal tables. Nope, I’m not talking about database tables. Yes, I’m talking about “internal” tables. Yes, you heard it right: You can define primary keys and indexes for internal tables, just like database tables. Yes, this improves performance a lot.
Although this is an easy technique, the syntax to define such internal tables is slightly different than your usual definitions. You will get used to it in no time though, and it’s well worth the effort.
Let’s start with regular standard tables.
Standard tables are our regular, all-purpose, all-around ABAP internal tables. When you define an ITAB just like “…type table of…” , you are actually defining a standard table. They are simple, easy-to-use; but usually not the best option in terms of performance.
Use standard tables if…
- …you will “LOOP” without any conditions; or
- …you will “READ TABLE” using an index (like READ TABLE … INDEX 5)
Here is a simple example on how to use standard tables:
types: tt_mara type standard table of mara. data: gt_mara type tt_mara, gr_mara type ref to mara. * How to append data append initial line to gt_mara reference into gr_mara. gr_mara->matnr = ‘A12345’. “… * How to loop loop at gt_mara reference into gr_mara. “whatever endloop. * How to read table read table gt_mara reference into gr_mara index 1.
Hashed Primary Key
OK, things are about to get excited now; so pay attention!
Just like defining a primary key on a database table, we can also define a primary key on an ITAB for faster data access. “Hashed key” is a primary key type we can define on an ITAB.
Use hashed keys if…
- …you will “READ TABLE” with conditions
- …you are sure that condition fields are unique
Here is a simple example:
types: tt_marc type hashed table of marc with unique key primary_key components matnr werks. data: gt_marc type tt_marc, gs_marc type marc, gr_marc type ref to marc. * How to append data gs_marc-matnr = ‘A12345’. gs_marc-werks = ‘1200’. “… insert gs_marc into table gt_marc. “… * How to read read table gt_marc reference into gr_marc with unique key primary_key components matnr = ‘A12345’ werks = ‘1200’.
In this example; we are sure that MATNR + WERKS will always be unique in GT_MARC. If we intend to access this ITAB using those fields within a “READ TABLE” statement, having a hashed primary key is the answer for fastest data access.
If you use a hashed key, data access speed is independent from ITAB size. This means; an entry in GT_MARC with 1.000.000 lines will be accessed as fast as an entry in GT_MARC with 10 lines.
Please note that we can’t use pointers when appending data into GT_MARC:
append initial line to gt_marc reference into gr_marc. gr_marc->matnr = ‘A12345’. “ SHORT DUMP!
Once you append GT_MARC, the keys (MATNR + WERKS) are already indexed; so you can’t change them any more. Imagine GT_MARC like a database table, and you’ll have an easier time getting used to this logic: After inserting a record into the database table MARC, you can’t change MATNR + WERKS again, can you?
Mind you that hashed tables are proven to be faster than database buffers and nested loops as well.
Sorted Primary Key
Sorted tables follow a similar logic like hashed tables, but for a different purpose: We use them to “LOOP” instead of “READ TABLE”.
Use sorted primary keys if…
- …you will “LOOP” with conditions
- …you are sure that “WHERE” condition fields are unique
Here is a simple example:
TYPES: tt_marc TYPE SORTED TABLE OF marc WITH UNIQUE KEY primary_key COMPONENTS matnr werks. DATA: gt_marc TYPE tt_marc, gs_marc TYPE marc, gr_marc TYPE REF TO marc. * How to append data gs_marc-matnr = 'A12345'. gs_marc-werks = '1200'. "… INSERT gs_marc INTO TABLE gt_marc. "… * How to loop LOOP AT gt_marc REFERENCE INTO gr_marc USING KEY primary_key WHERE matnr EQ 'A12345'. ENDLOOP.
Please note that we didn’t use *all* the fields in the primary_key (MATNR + WERKS). Partial access (only with MATNR) is also possible.
Following the same logic in hashed tables, we got to be sure that MATNR + WERKS are unique in GT_MARC. Due to the same reasons in hashed tables, we can’t use pointers when appending data into GT_MARC.
Just like a database table can have a primary key + indices, an ITAB can (optionally) have a primary key and (optionally) any number of secondary indices.
This makes sense when you need to access an ITAB using “READ TABLE” and “LOOP” simultaneously.
Here is a simple example:
types: tt_mseg type standard table of mseg with unique hashed key k1 components mblnr mjahr zeile with non-unique sorted key k2 components matnr. data: gt_mseg type tt_mseg, gs_mseg type mseg, gr_mseg type ref to mseg. * How to append data gs_mseg-matnr = ‘A12345’. “… insert gs_mseg into table gt_mseg. * How to read read table gt_mseg reference into gr_mseg with table key k1 components mblnr = ‘1234567890’ mjahr = ‘2014’ zeile = ‘0001’. * How to loop loop at gt_mseg reference into gr_mseg using key k2 where matnr eq ‘A12345’. “whatever endloop.
In this example, GT_MSEG doesn’t have a primary key. We could have defined a primary key using MBLNR + MJAHR + ZEILE; however, I didn’t do it due to demonstration purposes – having a primary key is optional in ITABs.
The first key, k1, is a unique hashed key. We will use this key to access the table using “READ TABLE” statements, and we promise SAP that each MBLNR + MJAHR + ZEILE combo will be unique. Please note that all hashed keys *must* be unique, you can’t define non-unique hashed keys.
The seond key, k2, is a non-unique sorted key. We will use this key to access the table using “LOOP” statements, and we tell SAP that MATNR values will not be unique. Please note that sorted keys can be unique or non-unique. Unique sorted keys provide better performance though.
In this article, I have shared the know-how on using primary + secondary keys within ITAB’s. If you combine this technique with the use of pointers (explained in a former article), the performance of your loops will sky-rocket!
If you are looking for a quantitative performance comparison, check the post ABAP Nested Loop Performance .
Feel free to contact me if you have questions.
Leave a Reply