topic Performance Issue in the code in Application Development and Automation Discussions

Performance Issue in the code

Former Member — Thu, 21 Feb 2019 10:35:59 GMT

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.

Re: Performance Issue in the code

BaerbelWinkler — Thu, 21 Feb 2019 11:01:11 GMT

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.

Re: Performance Issue in the code

Florian — Thu, 21 Feb 2019 11:34:06 GMT

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

Re: Performance Issue in the code

matt — Thu, 21 Feb 2019 12:08:05 GMT

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

Re: Performance Issue in the code

Former Member — Thu, 21 Feb 2019 14:13:39 GMT

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

Re: Performance Issue in the code

Sandra_Rossi — Thu, 21 Feb 2019 18:28:50 GMT

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.

Re: Performance Issue in the code

Sandra_Rossi — Thu, 21 Feb 2019 18:30:12 GMT

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

Re: Performance Issue in the code

Former Member — Thu, 21 Feb 2019 20:51:01 GMT

Well observed @Sandra Rossi, changed to asterisk.

Re: Performance Issue in the code

ged_hurst — Wed, 03 Apr 2019 09:12:35 GMT

Check

@it_vepo

is not empty.

Re: Performance Issue in the code

r010101010 — Fri, 05 Apr 2019 12:37:18 GMT

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

Less data to process.