topic Re: LOOP AT ... WHERE ... in Application Development and Automation Discussions

LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:14:49 GMT

Hi!

I like using the LOOP AT ... WHERE ... statement.

However it is really slow, if the internal table contains many (over 10000 entries), even if it is perfectly ordered and I'm using the full key in the WHERE condition.

Could you suggest me any similar statements with a better performance on larger internal tables?

Thank you

Tamá

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:18:00 GMT

Hi,

Using Loop at.....

always create performance issue.

to improve the performance you can do the following:

1. Use DESCRIBE command to get the line no of the table. Say it becomes N

2. Now use Do N TIMES........ ENDDO. for looping to get the data.

You may use WHILE..........ENDWHILE with suitable condition, too.

Regards,

Anirban

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:18:35 GMT

if you have a loop inside a loop you can better use parralel cursor for this

see a thread like this

for example

perhaps put some more of you're code for a good answer this is to less to make a suggestion for .

kind regards

arthur de smidt

Edited by: A. de Smidt on Jul 18, 2008 8:20 AM

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:20:20 GMT

Tamas,

LOOP ... WHERE is faster than LOOP/CHECK because LOOP ... WHERE

evaluates the specified condition internally.

As with any logical expressions, the performance is better if the

operands of a comparison share a common type.

The performance can be further enhanced if LOOP ... WHERE is combined

with FROM i1 and/or TO i2, if possible.

Amit.

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:22:21 GMT

Anirban,

earlier you wrote:

loop at....
always create performance issue.

are you become crazy on SDN?think before posting so than no need to update your own reply.i would suggest you that please read the question first than if possible only than reply.

you reapeat tamas's question into your reply.

Amit.

Edited by: Amit Gujargoud on Jul 18, 2008 8:23 AM

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:23:48 GMT

Hi Tamas,

Using Loop at Where statement is the better option while we are dealing with large amount of data.

If we use only LOOP statement, then it runs for all the records of the table by checking the condition for each and every record.

But whereas LOOP AT ... WHERE directly processes the records statisfying the condition in WHERE, ignoring all other records.

Check this following link for code optimization.

http://www.sapbrainsonline.com/ARTICLES/TECHNICAL/optimization/optimization.html

Hope this helps you.

Regards,

Chandra Sekhar

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:27:59 GMT

Please check the sample progam below, this is available in saptechncal.com(Author :Suresh Kumar Parvathaneni ), as I am unable to post the link, i'm copying the code. Hope this helps


REPORT ZINTERNAL_TABLE_OPERATIONS.* Program to find the best method in reading the internal tables
* Author: Suresh Kumar Parvathaneni* Type declaration
TYPES:
  BEGIN OF TY_MARA,
    MATNR LIKE MARA-MATNR,
    MTART LIKE MARA-MTART,
  END OF TY_MARA.* Internal table declaration
DATA:
  T_MARA TYPE STANDARD TABLE OF TY_MARA,
  T_MARA1 TYPE SORTED TABLE OF TY_MARA
          WITH NON-UNIQUE KEY MTART.* Variable declaration
DATA:
  W_COUNTER TYPE I,
  W_RUNTIME1 TYPE I,
  W_RUNTIME2 TYPE I,
  W_TABIX LIKE SY-TABIX.* Table workarea definition
DATA:
  WA_MARA TYPE TY_MARA.SELECT MATNR                           " Material Number
       MTART                           " Material Type
  FROM MARA
  INTO TABLE T_MARA.T_MARA1[] = T_MARA[].* CASE 1: Processing internal table using LOOP..WHERE ConditionGET RUN TIME FIELD W_RUNTIME1.LOOP AT T_MARA INTO WA_MARA WHERE MTART EQ 'FHMI'.
  ADD 1 TO W_COUNTER.
ENDLOOP.GET RUN TIME FIELD W_RUNTIME2.
* Calculate Runtime
W_RUNTIME2 = W_RUNTIME2 - W_RUNTIME1.
WRITE W_RUNTIME2.CLEAR W_COUNTER.* CASE 2: Using a Sorted tableGET RUN TIME FIELD W_RUNTIME1.
LOOP AT T_MARA1 INTO WA_MARA WHERE MTART EQ 'FHMI'.
  ADD 1 TO W_COUNTER.
ENDLOOP.GET RUN TIME FIELD W_RUNTIME2.
* Calculate Runtime
W_RUNTIME2 = W_RUNTIME2 - W_RUNTIME1.
WRITE W_RUNTIME2.CLEAR W_COUNTER.* CASE 3: Using INDEX on a sorted tableGET RUN TIME FIELD W_RUNTIME1.
READ TABLE T_MARA1 INTO WA_MARA WITH KEY MTART = 'FHMI'.
IF SY-SUBRC EQ 0.
  W_TABIX = SY-TABIX + 1.
  ADD 1 TO W_COUNTER.
  LOOP AT T_MARA1 INTO WA_MARA FROM W_TABIX.
    IF WA_MARA-MTART NE 'FHMI'.
      EXIT.
    ENDIF.
    ADD 1 TO W_COUNTER.
  ENDLOOP.
ENDIF.
GET RUN TIME FIELD W_RUNTIME2.
* Calculate Runtime
W_RUNTIME2 = W_RUNTIME2 - W_RUNTIME1.
WRITE W_RUNTIME2.Following is the analysis report in microseconds, as per the data volume:   

Records: 21,390 

Iteration No 
 Using Normal LOOP & WHERE 
 Using Sorted table LOOP & WHERE 
 Using INDEX on Sorted table 
 
1 
 897 
 887 
 11 
 
2 
 839 
 879 
 10 
 
3 
 839 
 877 
 10 
 
4 
 834 
 880 
 9 
 
5 
 842 
 837 
 10 
 

Records: 132,693 

Iteration No 
 Using Normal LOOP & WHERE 
 Using Sorted table LOOP & WHERE 
 Using INDEX on Sorted table 
 
1 
 34239  
 35774  
 3567 
 
2 
 34271  
 38250  
 3592 
 
3 
 34492  
 36534  
 3554 
 
4 
 34198  
 35695  
 3584 
 

Sorted table might have given a better performance here if the field in the WHERE condition is the first field in the internal table. However, from the above statistics, we can say that method 3 is better than the other 2 methods. In production environment, the data would be huge and the performance could be much improved with this simple technique.

Re: LOOP AT ... WHERE ...

venkatasriram_mygapula — Fri, 18 Jul 2008 06:29:22 GMT

Hi,

go for some performance techniques.

by using sort the internal table and by suing read statement with binary search. always checking sy-subrc.

thanks.

sriram.

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:48:11 GMT

Hi,

Please check this sample code:

My previous code was like this as given below...

DATA: 
  i_vbak  TYPE TABLE OF vbak,
  w_vbak  LIKE LINE OF i_vbak.

SELECT * 
  FROM vbak 
  INTO TABLE i_vbak.

LOOP AT i_vbak INTO w_vbak
                 WHERE vkorg EQ 'R100'.

WRITE:/ w_vbak-vkorg.
ENDLOOP.

Now i have done this like below for increasing performance..

It will increase the performance.

DATA:
  i_vbak  TYPE TABLE OF vbak,
  w_vbak  LIKE LINE OF i_vbak,
  w_tabix TYPE syst-tabix.

SELECT *
  FROM vbak
  INTO TABLE i_vbak.

SORT i_vbak BY vkorg.

READ TABLE i_vbak INTO w_vbak
                  WITH KEY vkorg = 'R100'
                  BINARY SEARCH.

CHECK sy-subrc EQ 0.

LOOP AT i_vbak INTO w_vbak
              FROM sy-tabix.
  IF w_vbak-vkorg = 'R100'.
    WRITE:/
      w_vbak-vkorg.
  ELSE.
    EXIT.
  ENDIF.
ENDLOOP.

Regards

Adil

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 06:54:13 GMT

You can avoid loop inside a loop by using

AT NEW

AT END

ON CHANGE OF

this will be performance wise better then nested loops.

Regards

Bikas

Re: LOOP AT ... WHERE ...

JozsefSzikszai — Fri, 18 Jul 2008 07:22:08 GMT

hi Tamás,

what you mean by "even if it is perfectly ordered"?

The internal table has to be SORTED or HASHED typed, than is the LOOP AT ... WHERE ... faster.

Pls. try the following test program (or create something similar):

DATA : gt_bkpf_st TYPE STANDARD TABLE OF bkpf,
       gt_bkpf_so TYPE SORTED   TABLE OF bkpf
                  WITH UNIQUE KEY mandt bukrs belnr gjahr,
       gt_bkpf_ha TYPE HASHED   TABLE OF bkpf
                  WITH UNIQUE KEY mandt bukrs belnr gjahr.

FIELD-SYMBOLS : <gw_bkpf> TYPE bkpf.

DATA : gw_timestamp TYPE timestampl.

SELECT * UP TO 100000 ROWS
       INTO TABLE gt_bkpf_ha
       FROM bkpf
       ORDER BY PRIMARY KEY.

gt_bkpf_st = gt_bkpf_so = gt_bkpf_ha.

SORT gt_bkpf_st BY mandt bukrs belnr gjahr.

GET TIME STAMP FIELD gw_timestamp.
WRITE : / gw_timestamp.
LOOP AT gt_bkpf_st ASSIGNING <gw_bkpf> WHERE bukrs EQ ....
  <gw_bkpf>-blart = 'SA'.
ENDLOOP.

GET TIME STAMP FIELD gw_timestamp.
WRITE : / gw_timestamp.
LOOP AT gt_bkpf_so ASSIGNING <gw_bkpf> WHERE bukrs EQ ....
  <gw_bkpf>-blart = 'SA'.
ENDLOOP.

GET TIME STAMP FIELD gw_timestamp.
WRITE : / gw_timestamp.
LOOP AT gt_bkpf_ha ASSIGNING <gw_bkpf> WHERE bukrs EQ ....
  <gw_bkpf>-blart = 'SA'.
ENDLOOP.

GET TIME STAMP FIELD gw_timestamp.
WRITE : / gw_timestamp.

Even the standard table is SORTed, the LOOP AT ... WHERE operation took more time, than on the sorted and the hashed table.

hope this helps

Re: LOOP AT ... WHERE ...

Former Member — Fri, 18 Jul 2008 07:39:58 GMT

I got the same results, however it is a bit harder to code, especially if there are many fields to check, but of course it seems this kind of code has the best performance.

READ TABLE T_MARA1 INTO WA_MARA WITH KEY MTART = 'FHMI'.
IF SY-SUBRC EQ 0.
  W_TABIX = SY-TABIX + 1.
  ADD 1 TO W_COUNTER.
  LOOP AT T_MARA1 INTO WA_MARA FROM W_TABIX.
    IF WA_MARA-MTART NE 'FHMI'.
      EXIT.
    ENDIF.
    ADD 1 TO W_COUNTER.
  ENDLOOP.
ENDIF.