What is faster UPDATE in a loop with SET or UPDATE TABLE outside a loop

[stephenl1]

I have an interesting scenario, for which I find very little info on the Internet, as most blogs deal with simple update database table scenarios, not complex.

In my case I need to update a column in a HANA database table to a value held in a related database table.

In Microsoft SQL I would do this as a single update statement, as follows

UPDATE primary " Alias for primary table
SET primary~<column_x> = secondary~<column_y> 
FROM db_primary_table AS primary           " Primary table
INNER JOIN db_secondary_table AS secondary " secondary table
 ON <some join conditions>
WHERE <some conditions>.

I have written the above statement in the form I would expect it to be written in ABAP, even though this format is not yet possible. This allows the value of the primary table to be set based on the value in a secondary table, or even a third or fourth joined table, and is a very powerful approach.

To do the same in ABAP currently, I must first select the records I require based on the join logic, as follows.

SELECT primary~key_1
  , primary~key_2
  , primary~field_x
  , secondary~field_y
FROM db_primary_table AS primary           " Primary table
INNER JOIN db_secondary_table AS secondary " secondary table
 ON <some join conditions>
WHERE <some conditions>
INTO TABLE @data(lt_records_to_update).

having selected the records, I then have to chose between two methods of updating them.

Method 1 uses a loop, and UPDATE SET to update db_primary_table, row by row.

Method 2 fetches the full row data from db_primary_table to an Internal table, updates the internal table field field_x based on the data in lt_records_to_update, then outside of the loop does an array update.

Method 1 would look as follows

IF NOT lt_records_to_update[] IS INITIAL.
     LOOP AT lt_records_to_update ASSIGNING FIELD-SYMBOL(<ls_record_to_update>).
        UPDATE db_primary_table SET field_X = <ls_record_to_update>-field_y
                       WHERE key_1 = <ls_record_to_update>-key_1
                       AND   key_2 = <ls_record_to_update>-key_2.
     ENDLOOP.
     COMMIT WORK.
ENDIF.

This is very simple but does involve repeated DB operations within a loop. The benefit is fairly concise code, and by using the SET parameter on the UPDATE statement, I only need worry about one column in the table along with the tables two key fields.

Method 2 would look as follows.

IF NOT lt_records_to_update[] IS INITIAL.

   " Get all records to be updated
   SELECT * FROM db_primary_table 
   INTO TABLE @data(lt_primary_table)
   FOR ALL ENTRIES IN @lt_records_to_update
   WHERE key_1 = @lt_record_to_update-key_1
         key_2 = @lt_record_to_update-key_2.

   LOOP AT lt_primary_table ASSIGNING FIELD-SYMBOL(<ls_primary_row>).

       " For each record, obtain the value to be used in the update
       READ TABLE lt_records_to_update
       ASSIGNING FIELD-SYMBOL(<ls_update_data>)
       WITH KEY key_1 = <ls_primary_row>-key_1
                key_2 = <ls_primary_row>-key_2.

       " Update the value
       <ls_primary_row>-field_x = <ls_update_data>-field_y.

   ENDLOOP.

   UPDATE db_primary_table FROM TABLE lt_primary_table.

   COMMIT WORK.

ENDIF.

Considering that the db_primary_table has excess of 60 columns, and we are only updating a maximum of 100 rows, which is the best approach, method 1 or method 2?

I am told I should not perform updates within a loop, but of the search results I found on the Internet on this subject, they infer Method 1 is better, as only a few fields are updated, and method 2 would be better if lots of fields were being updated. If this case, what considerations determine when to switch from using method 1 to method 2?

[Sandra_Rossi]

For information, I also documented a third method = Method 2 + create a "DDIC database view with update enabled" to minimize the volume and increase the performance, here: https://answers.sap.com/questions/275746/update-of-one-field-but-multiple-records-performan.html

[stephenl1]

Thanks sandra.rossi I can see how a DD view would be of benefit, especially in my situation where the table being updated has over 60 columns. Reducing the data set being passed to the database, which then must be examined for changes to know what is to be updated would make a considerable difference, as you have shown.

Ideally I'd like SAP to implement the UPDATE statement in SQL to the same level as Microsoft SQL's update, allowing you to update a field in the primary table from any field in any of the secondary joined tables. As you can see from the top of my post, this reduces all the other code I've illustrated to just one UPDATE statement, which would be a significant improvement to SAP SQL.