This is a test message to test the length of the message box.
Login
|
ABAP Inverted Individual Index
Created by Software-Heroes

ABAP - Inverted Individual Index

243

Let's take a look at a warning that has recently appeared for database tables in the ABAP environment. We'll investigate the cause and attempt to correct the warning.

Advertising


In this article, we'll look at a warning message you might now receive for tables if you're using a current release in the cloud.

 

Introduction

Warning messages always try to alert us to something that might not be quite correctly represented in the system or that could function differently. We've noticed that in tables with only one key field, we repeatedly receive a warning message, at least next to the client. There's no quick fix for this warning message, as you can see in the screenshot below. If the table has multiple index fields, i.e., more than one additional index field, then this warning currently doesn't appear. So, the question for us is: What does this warning mean, why does it appear, and how can we get rid of it?

 

Analysis

If we then hover the mouse over the warning message in the source code or over the icon on the side, we get a longer text. It says that we should use a key of type INVERTED INDIVIDUAL, but there is no further information on what to do or what is behind it.

 

We get more details in Eclipse if we switch to the Problems view. The Problems View also displays this message again. Here, we can right-click on the message and select the "Problem Description" option from the context menu.

 

If we then select this option, we at least receive a more detailed error description as well as further links to find information. The first conclusion seems to be that we are using exactly one additional index column besides the client. Therefore, the error message does not appear if, for example, we have two additional key fields.

 

Solution

Let's follow the trail, read the provided note in detail, and then try making the change.

 

Note

The documentation we receive via the Problems View and the subsequent link refers to a note (2600076) where we can find further information about the Background information, the actual test, the possible courses of action, and the consequences will help you better understand the content. The suggestion is generally good, and you should take a look if you want to learn more.

 

Background

Perhaps a brief background on the topic of columns within a HANA database: When learning the basics of HANA, you learn that the special feature compared to a normal database is that, firstly, we reside entirely in the database server's main memory, thus enabling fast read and write operations. Secondly, most tables are stored in a column-based manner. HANA is therefore fast and performant because each column has its own indexes, meaning we don't always have to read the key to access the data efficiently. This is particularly advantageous when considering calculations: When we need to combine columns, there are certain performance benefits if we can access these columns very quickly and the data is located directly one after the other in the database.

The same applies to the table index that is created. The primary index can consist of multiple columns, which, according to the documentation, are then merged on the database side. However, this is not necessary if, for example, we only have one column as the index. Thus, we can save the administrative costs of the primary index, as we don't need to join any additional fields.

 

Annotation

To eliminate the warning message and change the index, we can use an annotation. The annotation is described in the note. In addition to an annotation, we can also use SE11 if we are in an on-premises system to change the key there via the properties. Since we are in a cloud environment here and don't have access to the GUI, we use the annotation, which we place in the table header. After activation, the warning message should now also disappear.

@AbapCatalog.primaryKey.invertedIndividualIndex : true

 

Effects

The most important question that actually remains is what effect the annotation has. Why do we have to insert it, and can't the system do it for us? Let's list some advantages and disadvantages of using this option. As described in the note.

Advantages Disadvantages

Significantly reduced memory consumption

Read performance

Fewer I/O operations

Slow write operation (also reading)
Less table optimization required  
Faster write operations  

 

The first points of the advantages mainly point to the topic of HANA performance and are not so relevant for the developer. Here it is mainly an optimization on the HANA side, which is also a prerequisite for using these indexes. However, this also has disadvantages when reading, or rather, when reading performance, if we access the data records in specific scenarios.

 

Test

Since the documentation states that this could have an impact on performance, we will examine some test scenarios and measure whether we actually notice the performance difference when we perform selections on the two tables.

 

Setup

For this purpose, we define two tables that are structured completely identically. Let's call them Table A and Table B, and add the annotation to Table B to firstly remove the warning and secondly to perform the table index change. Then we create a class that generates the same data in each table, namely exactly 20,000 data records, which we previously randomly combined to obtain the same amount of data and the same data structure for comparison. Additionally, we define five different test cases in which we want to examine the performance.

  1. The first test case directly reads the key and the associated short text. Here, we go completely over the key field.
  2. The second case reads the entire line for each currency.
  3. The third case performs a GROUP BY operation, i.e., an aggregation.
  4. In the fourth case, we do not read over the key field.
  5. In the last case, we use a key operation, but not the direct EQUAL, rather a BETWEEN.

 

Result

We ran the operation several times to see if the resulting changes were easily noticeable or if there were any significant deviations. Generally speaking, Table B with the adjusted primary key already shows deviations when reading: almost all scenarios are somewhat slower than normal access. However, it's also fair to say that the deviations weren't too significant initially. This means that in everyday use, with very large datasets, the deviation could be somewhat larger, so you should keep an eye on performance when dealing with batch processing.

 

We haven't yet examined the write operations in detail in this case. According to the documentation, there are supposed to be advantages when writing, but in normal use cases, we rarely perform bulk write operations on tables. If we look towards RAP or processing, these are mostly just individual data records that are updated or created.

 

Complete Example

As always, you can find the test resources used, the class for generating the test data, the two tables, and the actual test class for measuring the performance differences, in a separate GitHub repository to see what we have implemented there.

 

Conclusion

We now know what the warning message means. We also know how to correct the warning message. However, the question remains: Should we do this if it primarily results in worse performance, but at least benefits our base administrator? Therefore, we do not yet have a clear recommendation for its use.

 

Further information:
SAP Help - Table Annotations
SAP Help - Indexes in Database Tables


Included topics:
Modernes ABAPTable IndexInverted Key
Comments (0)



And further ...

Are you satisfied with the content of the article? We post new content in the ABAP area every Tuesday and Friday and irregularly in all other areas. Take a look at our tools and apps, we provide them free of charge.


ABAP - XCO Excel

Category - ABAP

What can the XCO library for Excel in ABAP actually do? Here we look at the current status of the API.

11/29/2024

ABAP - SELECT FROM @itab

Category - ABAP

Selecting via an internal table used to be realized with many lines of code, but today it also works practically via the select.

01/20/2021