topic Re: Select in Loop performance tuning in Application Development and Automation Discussions

Select in Loop performance tuning

Former Member — Mon, 17 Aug 2020 01:27:17 GMT

Dear friends.

Can you please advise on the below code.

I know that using a select statement within a loop gives bad performance but I am not good in abap. Can you please help me to fine tune the below code for optimum performance.

LOOP AT ITB_EINA.
    ON CHANGE OF ITB_EINA-MATNR.
      CLEAR WK_MTART.
      SELECT SINGLE * FROM MARA
        WHERE MATNR = ITB_EINA-MATNR.
      IF SY-SUBRC = 0.
        WK_MTART = MARA-MTART.
      ENDIF.
    ENDON.
    IF WK_MTART = 'ROH'.
      MOVE-CORRESPONDING ITB_EINA TO ITB_EINA2.
      APPEND ITB_EINA2.
    ENDIF.
  ENDLOOP.

Re: Select in Loop performance tuning

cdprasanna — Mon, 17 Aug 2020 02:50:01 GMT

types : begin of lty_matnr,
	  matnr type matnr,
	end of lty_matnr.

data : lt_matnr type table of lty_matnr.

LOOP AT ITB_EINA.
    ON CHANGE OF ITB_EINA-MATNR.
  append itab_eina-matnr to lt_matnr.	
ENDLOOP.

*make sure lt_matnr has no duplicates and not empty for better performance

sort lt_matnr by matnr.
delete adjacent duplicates from lt_matnr.

if  lt_matnr is not initial.
  select matnr MTART
  from MARA
  into lt_matnr2
  for all entries in lt_matnr 
  where matnr = lt_matnr-matnr.
    if sy-subrc eq 0.
     LOOP AT ITB_EINA.
	<your logic goes here>
     endloop.
    endif.
endif.

Regards,

Prasanna CD.

Re: Select in Loop performance tuning

Sandra_Rossi — Mon, 17 Aug 2020 04:34:46 GMT

Not related to your question but you are cumulating many obsolete and prone-to-errors ABAP constructs (header lines and ON CHANGE OF cannot be used in ABAP Objects, since around 2000):

Header line (ITB_EINA, ITB_EINA2)
Use of implicit work area/no explicit target for SELECT (MARA structure)
ON CHANGE OF

Don't use them! Instead, use for instance:

LOOP AT itb_eina REFERENCE INTO DATA(eina). (>= 7.40)
SELECT SINGLE ... INTO @DATA(mara) (>= 7.40)
IF eina->matnr <> previous_eina->matnr

Re: Select in Loop performance tuning

Sandra_Rossi — Mon, 17 Aug 2020 04:37:43 GMT

Please, never use SELECT inside the loops.

Use SELECT ... INTO TABLE itab ... before the loops.

Use the transaction code SAT to troubleshoot performance issues.

Re: Select in Loop performance tuning

matt — Mon, 17 Aug 2020 10:25:09 GMT

Or rather than

IF eina->matnr <> previous_eina->matnr

Use GROUP BY if you want to be really modern.

Re: Select in Loop performance tuning

matt — Mon, 17 Aug 2020 10:27:08 GMT

No check whether it_mara is empty before the SELECT. Still using obsolete forms:

Table ITB_EINA with header line
ON CHANGE

Re: Select in Loop performance tuning

matt — Mon, 17 Aug 2020 13:14:16 GMT

Don't use tables with headers. Don't use ON CHANGE. Both are obsolete.

If you define lt_matnr as SORTED TABLE OF lty_matnr WITH UNIQUE KEY matnr.

Then you don't need:

sort lt_matnr by matnr.
delete adjacent duplicates from lt_matnr.