issue with the select inside loop

[Former Member]

Hello Experts,

I am doing performance tuning for a program and this has a select inside a loop , which is not recommended.

Could you please suggest, how can I separate the select from the loop.

IF i_stg_temp[] IS NOT INITIAL.

     LOOP AT i_stg_temp INTO w_stg_temp.

       SELECT vttk~tknum vttk~sttrg

              vtts~tsnum vtts~tsrfo

              vtsp~tpnum

              vttp~vbeln

*       vbpa~kunnr vbpa~adrnr

       FROM   vttk JOIN vtts

              ON vttk~tknum = vtts~tknum

              JOIN vtsp

              ON vtts~tknum = vtsp~tknum AND

                 vtts~tsnum = vtsp~tsnum

              JOIN vttp

              ON vtsp~tknum = vttp~tknum AND

                 vtsp~tpnum = vttp~tpnum

              JOIN vbpa

              ON vttp~vbeln = vbpa~vbeln

       INTO TABLE I_STAGE_TEMP

           WHERE  vttk~tknum = w_stg_temp-tknum

                AND vttp~vbeln =  vbpa~vbeln

                AND vbpa~parvw = 'WE'.

       IF i_stage_temp[] IS NOT INITIAL.

         LOOP AT i_stage_temp INTO w_stage.

           APPEND w_stage TO i_stage.

           CLEAR w_stage.

         ENDLOOP.

       ENDIF.

       CLEAR i_stage_temp.

ENDLOOP.

Thanks,

RG

[former_member196331]

hi,

Don't Use Select inside the Loop. It will be very slow and Some time leads to dump also.

Already some data is there

  LOOP AT i_stg_temp INTO w_stg_temp.

  Which means some data is available in table i_stg_temp.

   Now Why don't You use For all Entries.

   Select Desired Fields from Table2 into it_table2 for all entries in i_stg_temp

   where  table2.field1 eq i_stg_temp-table1-field1.

     Now.

     Loop at i_stg_temp into w_stg_temp.

     " Use Read table from  Table2  with key w_stg_temp-field1.

       if sy-subrc eq 0.

         """Do what ever u want.

        endif.

     endloop

    Always read is better than Select inside the loop.

[nikhil_kalawade]

Hi,

You can use for all entries for Loop table ( i_stg_temp ). After INTO TABLE I_STAGE_TEMP Use FOR ALL ENTRIES IN i_stg_temp .

Hope this will help you.

Regards,

Nikhil.

[Former Member]

Hi ,

Thanks for the replies .

I just want to confirm if the select query below does the same .

   SELECT vttk~tknum vttk~sttrg

              vtts~tsnum vtts~tsrfo

              vtsp~tpnum

              vttp~vbeln

*       vbpa~kunnr vbpa~adrnr

       FROM   vttk JOIN vtts

              ON vttk~tknum = vtts~tknum

              JOIN vtsp

              ON vtts~tknum = vtsp~tknum AND

                 vtts~tsnum = vtsp~tsnum

              JOIN vttp

              ON vtsp~tknum = vttp~tknum AND

                 vtsp~tpnum = vttp~tpnum

              JOIN vbpa

              ON vttp~vbeln = vbpa~vbeln

       INTO TABLE I_STAGE_TEMP

          for-all-entries in i_stg_temp

                 where  vttk~tknum = i_stg_temp-tknum

                AND vttp~vbeln =  vbpa~vbeln

                AND vbpa~parvw = 'WE'.

[nikhil_kalawade]

Hi,

Yes it will give you same result. While using for all entries please check the table is NOT INITIAL

(i_stg_temp[]) before the query.

     IF NOT i_stg_temp[ ] INITIAL.

          < your query>

     ENDIF.

Also while answering to you first time same answer given by two consultant already so we can expect that it will work

Hope this will help.

Regards,

Nikhil

[Former Member]

I suggest to use FOR ALL ENTRIES (see ABAP language help for details). In addition you should transfer the result immediately to the result table I_STAGE without your temp table.

Another performance issue could be using INTO at your LOOP statements. Please use field symbols or references, exceptional the table contains only small rows (single small values, like a number).

[Former Member]

hi,

If i_stg_temp[] is not initial.

  SELECT vttk~tknum vttk~sttrg

              vtts~tsnum vtts~tsrfo

              vtsp~tpnum

              vttp~vbeln

*       vbpa~kunnr vbpa~adrnr

       FROM   vttk JOIN vtts

              ON vttk~tknum = vtts~tknum

              JOIN vtsp

              ON vtts~tknum = vtsp~tknum AND

                 vtts~tsnum = vtsp~tsnum

              JOIN vttp

              ON vtsp~tknum = vttp~tknum AND

                 vtsp~tpnum = vttp~tpnum

              JOIN vbpa

              ON vttp~vbeln = vbpa~vbeln

       INTO TABLE I_STAGE_TEMP

          for-all-entries in i_stg_temp

                 where  vttk~tknum = i_stg_temp-tknum

                AND vttp~vbeln =  vbpa~vbeln

                AND vbpa~parvw = 'WE'.

endif.

loop at i_stg_temp into w_stg_temp.

        LOOP AT i_stage_temp INTO w_stage where tknum eq w_stg_temp-tknum.

           APPEND w_stage TO i_stage.

           CLEAR w_stage.

         ENDLOOP.

       ENDIF.

       CLEAR i_stage_temp.

endloop.

I am not sure if the underlined part works

Thank,

RG

[Former Member]

Yes, the underlined part should work. But remove your LOOPs after the SELECT and use in the SELECT directly I_STAGE at the INTO TABLE statement. You do not need this temporary table I_STAGE_TEMP.

[nikhil_kalawade]

Hi,

In debugger you will get to know that. Please check i_stg_temp-tknum  values only populating in your i_stage_temp.  Please do revert us with your debugging result.

Regards,

Nikhil

[Former Member]

Hi Ramya,

You can break the join into separate select queries as following:

if i_stg_temp is not initial.

select tknum sttrg into table lt_vttk from vttk for all entries in i_stg_temp where tknum = i_stg_temp-tknum.

if sy-subrc = 0.

Select tknum tsnum tsrfo into table lt_vtts from vtts for all entries in lt_vttk where tknum = lt_vttk-tknum.

if sy-subrc = 0.

select tknum tsnum tpnum into table lt_vtsp from vtsp for all entries in lt_vtts where tknum = lt_vtts-tknum and tsnum = lt_vtts-tsnum.

if sy-subrc = 0.

select tknum tpnum vbeln into lt_vttp form vttp for all entries in lt_vtsp where tknum = lt_vtsp-tknum and tpnum = lt_vtsp-tpnum.

if sy-subrc = 0.

select vbeln into table lt_vbpa from vbpa for all entries in lt_vttp where vbeln = lt_vttp=vbeln and parvw = 'WE'.

endif.

endif.

endif.

endif.

endif.

Now, accordingly you can loop and read the internal tables and perform the required operations.

Thanks,

Faraz Khan

[former_member186741]

For all entries is the way to go...either a straightforward version or or a package size option which may be needed if you are dealing with very large tables, the package size can be a variable or parameter

IF i_stg_temp[] IS NOT INITIAL.

* option 1...simple for all entries.... 

        SELECT vttk~tknum vttk~sttrg

               vtts~tsnum vtts~tsrfo

               vtsp~tpnum

               vttp~vbeln

*       vbpa~kunnr vbpa~adrnr

        FROM   vttk JOIN vtts

               ON vttk~tknum = vtts~tknum

               JOIN vtsp

               ON vtts~tknum = vtsp~tknum AND

                  vtts~tsnum = vtsp~tsnum

               JOIN vttp

               ON vtsp~tknum = vttp~tknum AND

                  vtsp~tpnum = vttp~tpnum

               JOIN vbpa

               ON vttp~vbeln = vbpa~vbeln

        into TABLE I_STAGE

                 for all entries in i_stg_temp

            WHERE  vttk~tknum = i_stg_temp-tknum

                 AND vttp~vbeln =  vbpa~vbeln

                 AND vbpa~parvw = 'WE'.

*....option 2... package size option     (needs endselect)   

        SELECT vttk~tknum vttk~sttrg

               vtts~tsnum vtts~tsrfo

               vtsp~tpnum

               vttp~vbeln

        FROM   vttk JOIN vtts

               ON vttk~tknum = vtts~tknum

               JOIN vtsp

               ON vtts~tknum = vtsp~tknum AND

                  vtts~tsnum = vtsp~tsnum

               JOIN vttp

               ON vtsp~tknum = vttp~tknum AND

                  vtsp~tpnum = vttp~tpnum

               JOIN vbpa

               ON vttp~vbeln = vbpa~vbeln

        appending CORRESPONDING FIELDS OF TABLE I_STAGE PACKAGE SIZE  10000

            for all entries in i_stg_temp

            WHERE  vttk~tknum = i_stg_temp-tknum

                 AND vttp~vbeln =  vbpa~vbeln

                 AND vbpa~parvw = 'WE'.

       endselect.