is there Golden Rule to update specific columns on Hana DB Table

[markus_kraetzschmar]

Dear Developers,

When updating a set of columns in the Database I may either use a

1. use row based solution, or

Select * from dbtab 
  where "come_conditions" 
  into itab. 

* Change the columns in the itab somewhere based on some defined logic

Update dbtab from itab.

1. work with a column based solution

Select from dbtab
  Fields "Primary_key_fields", field_20, field_31 
  where "come_conditions"
  into itab.

* Change the columns field_20 and field_31 of itab somewhere - same as above

Loop at itap reference into r_itabline. 
Update dbtab set field_20 = r_itabline->field_20 
                 field_31 =  r_itabline->field31
                 where "Primary_key_fields" = r_itabline->"Primary_key_fields".
endloop.

My personal thinking is,

that in the first "row based" solution the "Select * " could cause a larger runtime on a SAP Hana Database while the Update might be faster, especially for a itab with a lot of rows.

In the second "Colum based" solution the “Select of only specific columns” shall be faster. But based on the amount of selected rows in the itab I need to “fire” thousands of single updates for the two field to the Database, which I think could cause a performance issue as well, depending on the number of rows in the itab.

Without going into a detailed example here, does anyone have made experiences on how to Update or Modify DB-Tables in Hana DB in a performant way, especially since a lot of time (at least I) only need to update a few out of many columns.

A lot of documents are dealing with selections, CDS views an all the efficient reading stuff, but on updating or modifying data rows or specific columns, I could not really found a best and efficient practice guide for Hana DB.

Many thanks in advance.

Markus

[Sandra_Rossi]

Although I won't answer your question about what are the criteria for choosing a solution, you missed additional solutions:

3) Update is done by standard, you need (or not) to implement user exits to initialize your custom fields (Fernando answer)

4) Create a database view for more effective update (if performance is critical, i.e. millions of lines updated)

• Create an update database view with only the columns you need for UPDATE (key columns + updated columns)
• Use "Update view from itab", only the columns from the view will be transferred.

5) Native SQL, stored procedure, etc. (a little bit out of ABAP scope)

[markus_kraetzschmar]

Hi Sandra,

thanks for adding other solutions, that I didn't came up with.

Number 4 sounds promising,
but I must admit, I never created an Update Database view and used it. Do you have a link where I can get some more details? Searching the net did not bring really useful results expect se11 or Sybase specific issues.

Number 3 may not apply since everything is custom logic and number 5, is still alive ;-), but I’m not so familiar with this type of coding.

Many thanks and kind regards
Markus

[Sandra_Rossi]

SE11 > create view > type "database view" > attributes > type "read and update" (so that ABAP "UPDATE view" is accepted)

That's it.