topic Re: issue with the select inside loop in Application Development and Automation Discussions

issue with the select inside loop

Former Member — Thu, 21 Apr 2016 14:44:57 GMT

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,

Re: issue with the select inside loop

former_member196331 — Thu, 21 Apr 2016 14:53:52 GMT

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.

Re: issue with the select inside loop

nikhil_kalawade — Thu, 21 Apr 2016 14:54:12 GMT

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.

Re: issue with the select inside loop

Former Member — Thu, 21 Apr 2016 14:55:46 GMT

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).

Re: issue with the select inside loop

Former Member — Thu, 21 Apr 2016 14:59:50 GMT

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'.

Re: issue with the select inside loop

Former Member — Thu, 21 Apr 2016 15:05:17 GMT

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,

Re: issue with the select inside loop

nikhil_kalawade — Thu, 21 Apr 2016 15:08:29 GMT

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

Re: issue with the select inside loop

Former Member — Thu, 21 Apr 2016 15:15:46 GMT

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.

Re: issue with the select inside loop

nikhil_kalawade — Thu, 21 Apr 2016 15:18:10 GMT

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

Re: issue with the select inside loop

Former Member — Thu, 21 Apr 2016 15:56:38 GMT

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.

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

Thanks,

Faraz Khan

Re: issue with the select inside loop

former_member186741 — Thu, 21 Apr 2016 23:09:10 GMT

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.