Skip to Content
avatar image
-1
Former Member

Alter non key column of database table for each entry of internal table

Hi All,

Requirement: My Database Table dfkkop has a Z field called zzactive which I added using an append structure. It has type xfeld (char 1). I would like to set the value of this field as 'X'. I have an internal table with only 4 fields (opbel, opupw, opupk, opupz), which also happen to be the primary keys for the table dfkkop. This internal table contains the list of records for which zzactive should be set as 'X'.

Facts:

1) Consider that total number of records in internal table may be large >10,000

2) DFKKOP database table has 194 fields.

3) I am using HANA and would like to make sure that the database takes major load.

Problem: What is the best approach to code in my program.

a) Select all 194 field values in another internal table and use Update statement

b) Something like below which obviously will also give ATC error.

LOOP AT i_table INTO i_wa.

UPDATE dfkkop SET zzactive = 'X'

WHERE opbel = i_wa-opbel AND opupw = i_wa-opupw AND opupk = i_wa-opupk

AND opupz = i_wa-opupz.

ENDLOOP.

c) Some other syntax or variant of update statement. Please explain.

d) Any other technique. Please explain.

The question is mostly from performance point of view.

A humble request, please avoid posting syntax from SAP help for Modify / Update statements. I hope for a 'to the point' answer for resolving the problem and would be happy to answer if you need any relevant information.

Add comment
10|10000 characters needed characters exceeded

  • b) Why does it give an "obvious ATC error"? It's using the whole primary key I guess, so it's okay. From performance point of view, a bulk of UPDATE is identical to UPDATE FROM TABLE. Doing a direct update of a standard table is allowed for a Z field. Maybe it's because of HANA then? (I don't know its peculiarities)

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 11, 2016 at 12:01 PM

    It's all about balancing language features and performance:

    a) yes to SELECT all fields if you want to use UPDATE FROM TABLE, and don't want to use a projection view (see d)

    b) why not ; which ATC error is it?

    c) no other syntax

    d) if there's a performance issue, you may define a projection view which contains only key fields + the fields you want to update, of type "read and write".

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 10, 2016 at 11:26 PM

    I'm very much confused by this question... So, you have an internal table with the primary key values for which you need to update just one field. I'm not sure if there is anything new for that in ABAP 7.4-5 or something specific to HANA but in such case wouldn't UPDATE command be the only option?

    I'd expect MODIFY to be slower because it either creates a record or updates. In this case, since you got the keys from somewhere, the records with those keys should already exist, so it's UPDATE (unless you feel you need to cover both scenarios).

    But you lost me at p. a with selecting 194 field values. Why would you need that? You've just stated that you already have the keys and you know what value needs to go into that one field. Based on that, other fields and their number are completely irrelevant here. You only need to know the key to identify the record to be updated.

    There is no other syntax than the one you'd find in ABAP Help. IMHO the most performance potential here is not in UPDATE syntax but in identifying records to update. If you could use some other criteria to do mass update then it'd be more efficient. But with this particular scenario I don't believe many options would exist.

    Again, not sure about HANA but you might want to think how you'll commit the updates. In non-HANA environment, the updates occupy "rollback memory" (not sure if it's the official term) before COMMIT and eventually it could cause an overflow. Although 10K records is actually not that huge amount for the modern systems.

    IMHO you really should consider reading some literature on the subject if you are serious about this and it's not some random interview question. Also simply testing different option in your system would be by far more educational than asking on SCN.

    Add comment
    10|10000 characters needed characters exceeded