Using Indexes with Internal Tables to Increase Performance

1464-ppt

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 Table

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?

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.

Secondary Indices

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.

Conclusion

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!

Feel free to contact me if you have questions.

Advertisements

2 thoughts on “Using Indexes with Internal Tables to Increase Performance

  1. Great article thanks! Cant wait to see the test results 😀 btw i tested for BPC and results are a bit different (i think this is due to data types are fixed as CHAR32 and all the 12 columns are key ).
    Shortly, i tested for 500.000 records;
    Hash table is faster on “collect into itab” statement,
    Standard table with binary search is faster on reading a single line. (in a DO statement i read same line 500.000 times)

    I changed table structure to 2 fields and tested again, now hash table give better results,
    There is correlation between performance of hash tables and number of columns, but binary search has different behaviors.
    Soon i am planning to post a performance comparison :).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s