Avoiding UPDATE statement inside the LOOP.

[Former Member]

Hello,

My requiremnt is to update records in a Z-table for all the records in an internal table.For each record update failure , I need to display in the output.

So instead of using the LOOP statement , I was thinking of Creating a Function Module to avoid an UPDATE statement inside the loop. This FM would be called in the program.

The Importing parameter of the FM would be a internal table containing records that needs to be updated in the database table. The exporting parameter would be a Error Log table .

In the FM I would read the importing table from index 1 and use the UPDATE statement to directly update the Ztable.

If an error was found then update the Error Log table.

Which one is better in terms of performance.

1. Using a LOOP statement and using an UPDATE statement inside the LOOP.

2. Using the FM module LOGIC mentioned above.

Thanks

Nishant

[Former Member]

Hi Nish,

Not sure what exactly the benefit would be to move the code to a FM. Also not sure what you mean by:

 the FM I would read the importing table from index 1 and use the UPDATE statement to directly update the Ztable.

In case you do not want to do an iterative update, you can always opt for the follwoing statement:

UPDATE target source. 

in which you update the whole internal table at once. The only problem is that you will not know exactly which record failed in case the retun code is not zero.

Kind regards,

Robert

[yuri_ziryukin]

Hello Nishant,

your idea of moving an update into the function module will not bring any performance improvement.

May I ask you to give more details about the table you would like to update?

1. How many fields are you updating and how many fileds has the table in total?

2. How big is the table (entries)?

3. How big is your internal table that you are using to update?

4. Just to make it clear: the reason of the sy-subrc <> 0 after update is missing record with the required key, right?

Yuri

[former_member194613]

you are taking the recommendation do not UPDATE in a LOOP too literally.

A READ starting from index1 is nothing different than a LOOP. The FM does only add further overhead, so it is even worse.

If your solution must really work on a large number of records, then you should use a SELECT FAE returning the keys of the records which are available in the table. Then you must split your table efficiently (use LOOP and READ on sorted table) into the part which can be UPDATED and the part which can not (is an INERT possible?).

The UPDATE can be done with arrays: UPDATE db_tab FROM TABLE itab.

I am sorry, this is only a rough guideline, consult the online-help for the details.

Siegfried

[yuri_ziryukin]

Yep, proposal from Siegfried makes sense, but also has to be tested. With different number of entries you may get different results. That's why I was asking for the number of records to be updated.

Another thing that we have seen is that update of 1-2 fields on a table with many fields can be faster if a view is defined and updated instead of the table itself.

Here are the results of our tests with UPDATE in one internal system:

The first part (Form UPDATE_SINGLE) is the update of single rows in a loop using UPDATE ... SET. In the example below this took 58.1 s.
The second part (Form ARRAY_UPDATE_ALL) is the update of complete rows using the Array Update UPDATE ... FROM TABLE. In the example below this took  39.0 s.
The third part (ARRAY_UPDATE_VIEW) is the update of the field to be changed using the Array Update on a view. In the example this took 4.1 s.

[SuhaSaha]

Hello Yuri,

Another thing that we have seen is that update of 1-2 fields on a table with many fields can be faster if a view is defined and updated instead of the table itself.

Can you elaborate on this one please?

Thanks,

Suhas

[yuri_ziryukin]

Hello Yuri,
> 
> 

Another thing that we have seen is that update of 1-2 fields on a table with many fields can be faster if a view is defined and updated instead of the table itself.

> 
> Can you elaborate on this one please? 
> 
> Thanks,
> Suhas

Hello Suhas,

in the SAP data dictionary you can define a view on the table and put in a view only key fields plus the field(s) to be updated. In the "Maint.Status" tabstrip of the view definition you can specify that this view can be used for changes also.

Our test shows that for tables with many fields the update is done much faster in this case.

Regards,

Yuri