Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance implications of using "SET INDICATORS"

hbehrens
Discoverer
732

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?

2 REPLIES 2

horst_keller
Product and Topic Expert
Product and Topic Expert
0 Kudos
531

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
Discoverer
0 Kudos
531

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: