Performance implications of using "SET INDICATORS"

[hbehrens]

I have to update a db table (HANA column store) from a very large itab (millions of rows), but only want to update very few columns - always the same columns, but different values for every row.

I figured I'd use the "INDICATORS" keyword, but the performance is abysmal when compared to the regular version.

TYPES type_db_ind TYPE dbtab WITH INDICATORS update_indicators.
TYPES type_dbtab
  TYPE SORTED TABLE OF type_db_ind 
        WITH UNIQUE KEY ....
DATA large_itab TYPE type_dbtab. "some non-key fields will be initial!
...
UPDATE dbtab
FROM TABLE @large_itab
INDICATORS SET STRUCTURE update_indicators.

Shouldn't it be *faster* considering it's only changing 4 columns of a 60 col table? It seems as if it is doing single updates, one row at a time.

TYPES type_dbtab TYPE SORTED TABLE OF dbtab
  WITH UNIQUE KEY ....

DATA large_itab TYPE type_dbtab. "*all* fields have to be read from dbtab first
...
UPDATE dbtab
FROM TABLE @large_itab.

Is there any way (maybe a parameter) to speed this up or do I still have to read the db table in whole just to update a few columns?

[retired_member]

In your code, it is not shown how the indicator structure is filled.

You say "some non-key fields will be initial!

Where? Can you show, how you fill the indicator structure with value hexadecimal 1 for the fields to be updated?

[hbehrens]

Thanks for your reply! I'll try to elaborate:

"some non-key fields will be initial"

A) Some non-key fields I know will never change ("created by" won't change in an update)

===> Indicator field is always "00", field in abap struct is initial.

B) Some fields will always be updated (changed_by, cond_value...)

===> Indicator field is always "01", field in abap struct is never initial.

"How is the indicator structure filled?"

This is being done in a loop over all rows of the itab which will be used during the UPDATE. Because I know exactly what fields will be changed during an update I can set them like this:

CONSTANTS gc_update_set TYPE x VALUE '01'.
...
    cs_update_price-update_indicators = VALUE #(
      cond_value = gc_update_set
      cond_curr = gc_update_set
      base_quan = gc_update_set
      base_unit = gc_update_set
      delete_flag = gc_update_set
      changed_at = gc_update_set
      changed_by = gc_update_set
      changed_in = gc_update_set ).

This is the actual dbtab, fields that will have their indicator = '01' are highlighted in green, all other non-key fields will be initial in the itab during the UPDATE:

[thomas_kempfle]

I did a simple comparison of the three different UPDATE strategies that I could think of:

1. Update everything
   UPDATE <table> FROM TABLE <driver_table>.
2. Selective update
   LOOP AT <driver_table> REFERENCE INTO DATA(line).
         UPDATE <table>SET .... WHERE ...    
   ENDLOOP.
3. Update indicators
   UPDATE <table> FROM TABLE @<driver_table> INDICATORS SET STRUCTURE <indicators>.

   I let all three strategies compete a few thousand times against each other and found on my system

   • Alternative 1 to be ~40x faster than alternative 2
   • Alternative 2 to be ~4 times faster than alternative 3. 

So if performance is crucial for you, you should currently rather avoid indicator structures, especially when updating many lines in large tables! Looking forward for the SAP improving the performance with one of the next kernels.