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

[fedaros · Accepted solution]

Hi Markus,

I guess the best answer depends on scenario you are in hands.

In performance terms, yes send a command to update many rows on database is for sure faster, but from the other side you are bypassing the SAP application, which may have buffer / caches.

I'd say that if you are using an ABAP report for something it is always best to keep things clean, even if you update all columns than only of what you want for this moment.

There's also a benefit on this approach that you are sending from app, a complete "version" of row that your program maintaned. Today is one column, maybe tomorrow are two and you may face dificults to handle these versions in two worlds (app x db).

Anyhow there's not such a normal behavior to update a lot of rows many times. (of course exception exists)

Hope this helps, Fernando Da Rós

[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.

[markus_kraetzschmar]

Hello Fernando,

thanks for your answer. Seems there is no default best way to do it. It depends, .. as usual ;).

I will go the usual way, too. I analyze the performance in the test system and decide afterwards depending on the results.

KR

Markus

[Sandra_Rossi]

Fernando may not be informed.

If you want to target someone, if this person has posted an Answer, use the button COMMENT, if this person is the Original Poster of the question he/she will be automatically informed, otherwise copy/paste their hyperlinked name so that the person receives a warning (NB: @ doesn't work/but typing this character will suggest hyperlinked names).