topic Re: Avoid Where in loop..and select single in loop. in Application Development and Automation Discussions

Avoid Where in loop..and select single in loop.

Former Member — Thu, 07 Jan 2010 07:12:03 GMT

Hi ,

I want to avoid Where condition in loop and select single in loop...statement for my code can anyone help me in this.

LOOP AT it_cdred INTO wa_cdred WHERE fname = 'SGTXT'.

wa_change_temp-mandt = sy-mandt.

wa_change_temp-changed_dt = wa_cdred-udate.

SELECT SINGLE awkey FROM bkpf INTO lv_awkey WHERE bukrs = wa_change_temp-bukrs

AND belnr = wa_change_temp-belnr.

can any one help me in this.

Tks,

Kmr

Re: Avoid Where in loop..and select single in loop.

Former Member — Thu, 07 Jan 2010 08:26:50 GMT

it is not possible to avoid a WHERE in a LOOP.

You must take care that the table is sorted in the order of the used fields and that you use an READ BINARY SEARCH and an EXIT-condition, see

Measurements on internal tables: Reads and Loops:

/people/siegfried.boes/blog/2007/09/12/runtimes-of-reads-and-loops-on-internal-tables

Or use a SORTED TABLE with appropriate keys.

Instead of the SELECT SINGLE youz should use an FOR ALL ENTIRIES outside of the LOOP and inside of the LOOP

an optimized READ, BINARY SEARCH or again better SORTED TABLE, the other part of the blog.

Siegfried

Re: Avoid Where in loop..and select single in loop.

kesavadas_thekkillath — Thu, 07 Jan 2010 08:33:34 GMT

before the loop pick the data from table bkpf using for all entries.

Then in loop read the internal table using binary search.

Is this loop inside a loop ?

Re: Avoid Where in loop..and select single in loop.

Former Member — Thu, 07 Jan 2010 16:30:45 GMT

Hi ,

Select single can be replaced by fetch of records from database table and then read of records using binary search.

To remove where clause from loop, you need to consider the same condition while fetch of records in this internal table.

Hope this helps you.

Re: Avoid Where in loop..and select single in loop.

Former Member — Thu, 07 Jan 2010 16:39:04 GMT

Hi,

try this way....


    sort it_cdred  by fname.
    READ TABLE it_cdred INTO wa_cdred  WITH KEY
                   fname = 'SGTXT'    BINARY Search.
    IF sy-subrc = 0.
        W_L_TABX = SY-TABIX.
       DO.
           READ TABLE it_cdred INTO wa_cdred   INDEX W_L_TABIX.
           IF SY-SUBRC NE 0  OR wa_cdred-fname NE 'SGTXT'   . 
             EXIT.
          ENDIF.
          SELECT SINGLE awkey FROM bkpf INTO lv_awkey 
                                                WHERE bukrs = wa_change_temp-bukrs
                                                 AND  belnr = wa_change_temp-belnr.
    "Move to target table or Modify
          W_L_TABX = W_L_TABX + 1.
    ENDDO.
  ENDIF.

Regards,

Prabhuds

Re: Avoid Where in loop..and select single in loop.

Former Member — Fri, 08 Jan 2010 09:36:13 GMT

please do not recommend weird workarounds:


DO.
      READ TABLE it_cdred INTO wa_cdred   INDEX W_L_TABIX.
       IF SY-SUBRC NE 0  OR wa_cdred-fname NE 'SGTXT'   . 
             EXIT.
       ENDIF.
...
ENDDO.

is identical to well known workaround, which is descirbed in the blog


LOOP AT it_cdred INTO wa_cdred FROM  INDEX W_L_TABIX.
       IF SY-SUBRC NE 0  OR wa_cdred-fname NE 'SGTXT'   . 
             EXIT.
       ENDIF.
...
ENDLOOP.

I do not repeat it every time, because it is crucial, that the exit is used etc, ... which is better explained in the blog.

Re: Avoid Where in loop..and select single in loop.

Former Member — Fri, 08 Jan 2010 15:14:59 GMT

Hi,

I agree with Siegfried Boes comment in the threads. And I would just like to add one more point. As I'm not aware of the number of Table entries, you can use a Field-Symbol which will be faster compared to a normal internal table. And hence your overall performance will significantly improve.

Hope this helps.

Thanks,

Samantak.

Re: Avoid Where in loop..and select single in loop.

Former Member — Thu, 18 Feb 2010 16:11:47 GMT

it_cdred_tmp[] = it_cdred[]

delete it_cdred_tmp where fname <> u2018SGTXTu2019.

Sort i it_cdred_tmp by bukrs belnr.

Select awkey FROM bkpf into talble it_awkey for all entries of it_cdred_tmp

Where bukrs = it_cdred_tmp -bukrs

AND belnr = it_cdred_tmp u2013belnr.

After you can take a loop on it_cdred_tmp ot it_cdred according your requierement and use a read table with binary search on it_awkey

Re: Avoid Where in loop..and select single in loop.

VivekG — Sun, 21 Feb 2010 10:11:06 GMT

Hi,

Use for all entries and then select all the entries from the table and then use loop and then use read statement inside the loop.

Use BINARY SEARCH statement with read statement(sort the internal table by with the key u are reading it.

Let me know if it helps else will provide the sample code.

Vivek

Re: Avoid Where in loop..and select single in loop.

VivekG — Sun, 21 Feb 2010 10:26:35 GMT

Hi ,

I am giving the sample code also below.

select bukrs belnr awkey from bkpf into table li_bkpf

for all entries in it_cdred

where bukrs = it_cdred-bkrs

and belnr = it_cdred-belnr

and fname = 'SGTXT'.

LOOP AT it_cdred INTO wa_cdred.

read table li_bkpf into lw_bkpf with key bukrs = it_cdred-bkrs

belnr = it_cdred-belnr.

if sy-subrc = 0.

lv_awkey = lw_bkpf-awkey.

endif.

endloop.

Thanks,

Vivek Gupta

Re: Avoid Where in loop..and select single in loop.

Former Member — Mon, 22 Feb 2010 09:25:23 GMT

o.k., last comment there are 2 possiblities where a LOOP WHERE might appear:

1. As explained in my above posts it is the unavoidable solution for nested loops where the inner loop must find several

records. Then it must be optimized with a sorted table or workaround see above.

2. It can happen, that there is no outer loop. Then the internal table contains to many data, maybe data which are never needed at all. Then it is much better to move the conditon into the WHERE condition of the SELECT. I do not see the FOR ALL ENTRIES, it is a fixed condition and just be added to the SELECT.

I have read several times, that people do not add NOT EQUAL conditions or conditions to non-primary key fields to the SELECT, this is incorrect. Just add it and you save a lot of time in SELECT and some time in the LOOP.

Siegfried