Avoid select within a loop

[Former Member]

Can anyone suggest an alternative for the following code where there is a SELECT within a LOOP.

IF  t_inttab IS INITIAL.
loop at t_inttab into w_inttab where scedule_dep = 'X'.

  SELECT objid sobid FROM hrp1001 INTO TABLE t_objsob
                                          PLVAR = '01'                  AND
                                          OTYPE = 'D'                   AND
                                          OBJID = w_inttab-objid        AND
                                          RSIGN = 'B'                   AND
                                          RELAT = '020'                 AND
                                          SCLAS = 'E' .
   endloop.
  ENDIF.

[ravi_lanjewar]

Hi,

I think you have to search on SDN.

Mean while you can used for all entries in to read data into internal table

 delete  t_inttab from where scedule_dep ne 'X'.
 IF  t_inttab IS INITIAL.
   SELECT objid sobid INTO TABLE t_objsob
    FROM hrp1001 client specified
     for all entries in t_inttab
     wher mandt = sy-mandt
             PLVAR = '01'                  AND
             OTYPE = 'D'                   AND
             OBJID = t_inttab-objid        AND
             RSIGN = 'B'                   AND
             RELAT = '020'                 AND
             SCLAS = 'E' .
  ENDIF.

[Former Member]

Just a small correction to the code, It should not be

IF t_inttab IS INITIAL.

instead it should have been

IF t_inttab IS NOT INITIAL.

Regards

Ranganath

[Former Member]

Yes i also think that it was NOT INITIAL.....

Because if it is initial then it means that there is no data in that internal table.....

[Former Member]

Thanks !

[Former Member]

thanks !

[Former Member]

Could you alsp help me in how to avoid nested loop for teh following code :

loop at t_objsob into w_objsob.
 loop at p_t_elist into w_elist where objid = w_objsob-objid.
  w_newlist-pernr = w_elist-pernr.
 w_newlist-sobid = w_objsob-sobid.
append w_newlist to t_newlist
clear w_newlist.
 endloop.
endloop

[Former Member]

We cannot always avoid the nested loops. Sometimes it is required due to the table field relationships. If you see that you'll always have unique record in table p_t_elist for a single record of OBJID from t_objsob, then you can use a READ statement.

Otherwise if there are multiple records, you can optimize your inner loop in the following way,

1) Declare the internal table p_t_elist as sorted table with key OBJID instead of standard, This will use implicit BINARY SEARCH while selecting.

2) Use the parallel cursor method, by having the internal table p_t_elist sorted on OBJID, and reading the records from the next index onwards with Loop at p_t_elist .... from lv_index.

Regards

Ranganath

[Former Member]

Hi Maansi,

Instead of nested loops you can use loop and read statements only if you know that the other table i.e. p_t_elist has not more than one OBJID entry in it. Also, for better performance use field symbols instead of work area.

*" Sort internal table 
SORT p_t_elist by OBJID.

loop at t_objsob assigning <fs_objsob>.
   read table p_t_elist assigning <fs_elist> with key objid = <fs_objsob>-objid [BINARY SEARCH].
    if sy-subrc eq 0 and <fs_elist> is assigned.
        w_newlist-pernr = <fs_elist>-pernr.
        w_newlist-sobid = <fs_objsob>-sobid.
        append w_newlist to t_newlist
        clear w_newlist.
     endif.
endloop

Hope that helps!

Regards,

Saba

[Former Member]

>We cannot always avoid the nested loops.

can not be avoided after FAE, use a join if possible.

1) Declare the internal table p_t_elist as sorted table with key OBJID instead of standard, This will use implicit BINARY SEARCH while selecting.

This will solve your problem

2) Use the parallel cursor method, by having the internal table p_t_elist sorted on OBJID, and reading the records from the next index onwards with Loop at p_t_elist .... from lv_index.

I would not recommend that, outdatet solution!

Siegfried

[Former Member]

>

> 2) Use the parallel cursor method, by having the internal table p_t_elist sorted on OBJID, and reading the records from the next index onwards with Loop at p_t_elist .... from lv_index.
> I would not recommend that, outdatet solution!
> 
> Siegfried

Siegfried, why is this considered an outdated solution? Is it because of maintenance nightmare (and chances of really messing it up) or there is no tangible performance benefits?

[Former Member]

>Siegfried, why is this considered an outdated solution? Is it because of maintenance nightmare (and chances of really messing it up) >or there is no tangible performance benefits?

> Is it because of maintenance nightmare (and chances of really messing it up)

First of all, the functionality should be correct and this can be major issue

> there is no tangible performance benefits?

I can provide the best performance, however solutions with nested operations (do not take nested loops to literally, it includes all loop in loop, read in loop, any other operation in loop) can be as good. You must take care that the inner operation is optimized!

And do not forget that nested cursor require that both tables are sorted in the same way, and sorts are expensive. In nested operations is not necessary that the outer table is sorted, so it can that one or even two additional sorts are required (sort plus sort back to original order).

Siegfried

[Former Member]

Hi,

I think you want to select the records from database table hrp1001 corresponding OBJID available in the internal table.

Just set the select statement outside the loop. Once you fetch all the entries. Then you delete the records which has different object ID other than in internal table. So you are avoiding repeated access of database.

SELECT objid sobid FROM hrp1001 INTO TABLE t_objsob

where PLVAR = '01' AND

OTYPE = 'D' AND

RSIGN = 'B' AND

RELAT = '020' AND

SCLAS = 'E' . " Only one time Access with database

loop the table t_inttab into w_inttab where scedule_dep = 'X'.

delete from the table t_objsob where objid ne w_inttab-objid.

" Other manipulations

endloop.

Regards,

Chellamma Chandrasekar

[Former Member]

Hi,

Check this link. This will also guide. Hope you would have gone through it.if not, please go head.

Regards,

Chellamma Chandrasekar