Performance Issue in the code

[Former Member]

Hi Team,

The below code is taking lot of time. How can i improve the performance for this code -

SELECT venum AS venum,
 exidv AS exivd
 INTO TABLE @DATA(it_vekp)
 FROM vekp
 FOR ALL ENTRIES IN @it_vepo
 WHERE venum = @it_vepo-unvel.


 LOOP AT it_vekp INTO DATA(wa_vekp).
  UPDATE vekp SET erlkz = 'X' WHERE venum = wa_vekp-venum.
   IF sy-subrc = 0.
   COMMIT WORK.
 ENDIF.
ENDLOOP.

[BaerbelWinkler]

Babu - this is not an answer to your question but a comment on the code you shared:

Hard updates to SAP-tables like you do here are not a good idea and shouldn't be done as you run at least the risks of a) creating inconsistencies and b) circumvent tracking of table changes.

[Florian]

maybe you should just have a look at your course book. Pretty sure there is a chapter in, that a commit work should not be placed at that line...

[matt]

Also, if you corrupt your database somehow, then SAP will charge you $$$$ to fix, and you may well be violating license conditions.

[Former Member]

Hi Badu,

Despite all the comments about your question, and by the way, they are totally right, if we look just on the performance side, using update inside a loop is not a good idea (without even mention the commit!). Try to use modify, like below, and check if performance improves.

select *
  into table @data(it_vekp)
  from vekp
  for all entries in @it_vepo
 where venum = @it_vepo-unvel.

data ls_vekp like line of it_vekp.
ls_vekp-erlkz = 'X'.

modify it_vekp from ls_vekp transporting erlkz where erlkz is initial.
modify vekp from table it_vekp.

Regards,

Rafael

[Sandra_Rossi]

Your solution doesn't compile because the components of IT_VEKP (3 columns as far as I can see) don't correspond to (all) the columns of VEKP.

To use MODIFY vekp, you must use an internal table with all the columns of VEKP.

[Former Member]

Well observed @Sandra Rossi, changed to asterisk.

[Sandra_Rossi]

Please provide the execution plan (SQL EXPLAIN) of both the SELECT and UPDATE.

[ged_hurst]

Check

@it_vepo

is not empty.

[r010101010]

You can also restrict the data set adding erlkz = space.

Less data to process.