Select in Loop performance tuning

[former_member697046]

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.

[cdprasanna]

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.

[matt]

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.

[Sandra_Rossi]

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

[Sandra_Rossi]

Please, never use SELECT inside the loops.

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

Use the transaction code SAT to troubleshoot performance issues.

[matt]

Or rather than

IF eina->matnr <> previous_eina->matnr

Use GROUP BY if you want to be really modern.

[matt]

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

• Table ITB_EINA with header line
• ON CHANGE