topic joining select statements-performance problem in Application Development and Automation Discussions

joining select statements-performance problem

Former Member — Tue, 30 Aug 2005 03:42:34 GMT

Here two cases are there.Is it posiible to combine the two cases and make it one? I am using OR in the select statement, performance is very bad, i want to improve it.Is there any chance of avoiding that...

select vbeln from vbfa into itab-vbeln where vbelv = final_data-xblnr1

or vbelv = final_data-xblnr2.

select single vbeln from vbrk into tabvbrk-vbeln

where vbeln = itab-vbeln and fkart = 'RTS'.

if sy-subrc eq 0.

move tabvbrk-vbeln to final_data-vbeln1.

modify final_data.

endif.

endselect.

select vbeln from vbfa into itab-vbeln where vbelv = final_data-xblnr1

or vbelv = final_data-xblnr2.

select single vbeln from vbrk into tabvbrk-vbeln

where vbeln = itab-vbeln and fkart = 'ZTR'.

if sy-subrc eq 0.

move tabvbrk-vbeln to final_data-vbeln2.

modify final_data.

endif.

endselect.

Thanks,

fractal

Re: joining select statements-performance problem

Former Member — Tue, 30 Aug 2005 04:03:32 GMT

1. Instead of using or in where clause you can define Range for vbelv using RANGES statement. You have to populate the Ranges internal table with the two values of vbelv that you are using in the where clause. Then you can use the 'IN' option in where clause. This will improve performance.

2. Instead of Select Endselect you should use "select into table 'itab'". All data will then come into internal table 'itab'. You can then loop on this internal table 'itab' and include both select single statements in the loop, one below the other. This will further improve performance.

Message was edited by: Sudhir Bhate

Re: joining select statements-performance problem

Former Member — Tue, 30 Aug 2005 04:29:10 GMT

Hi,

Use Ranges for this purpose:

RANGES:
r_fkart for vbrk-fkart.

r_fkart-low = 'RTS'.
r_fkart-sign = 'I'.
r_fkart-option = 'EQ'.
append r_fkart.

Similarly do for 'ZTR'.


select vbeln from vbfa into itab-vbeln where vbelv = final_data-xblnr1 
or vbelv = final_data-xblnr2. 

select single vbeln from vbrk into tabvbrk-vbeln 
where vbeln = itab-vbeln and fkart in r_fkart.

Best Regards,

Anjali

Re: joining select statements-performance problem

Former Member — Tue, 30 Aug 2005 04:32:07 GMT

Instead of SELECTing single from VBRK for all items (You need only header record), you can SELECT data from VBFA into internal table and then based on that internal table SELECT data from VBRK.

In other words, avoid nested SELECT statement.

Re: joining select statements-performance problem

Former Member — Tue, 30 Aug 2005 04:33:51 GMT

The first main thing is dont use select... endselect.

select vbeln from vbfa into itab-vbeln where vbelv = final_data-xblnr1 
or vbelv = final_data-xblnr2.

U can use

select vbeln from vbfa into 
 corresponding fields of table itab
 for all entries in final_data
 where vbelv = final_data-xblnr1 
 or   vbelv = final_data-xblnr2.

Instead of this

select single vbeln from vbrk into tabvbrk-vbeln 
where vbeln = itab-vbeln and fkart = 'RTS'. 
if sy-subrc eq 0. 
move tabvbrk-vbeln to final_data-vbeln1. 
modify final_data. 
endif. 
endselect.

Use

select vbeln from vbrk 
        appending table final_data 
        where vbeln = itab-vbeln 
        and fkart = 'RTS'. 
if sy-subrc eq 0. 

endif.

Try like this.

Hope this helps.

Kindly reward points for the answer which helped u and helped to solve the problem.

Re: joining select statements-performance problem

Former Member — Tue, 30 Aug 2005 05:48:21 GMT

Hi,

First avoid select end select statement, for that define one internal table for the first select, check the code below

data : begin of itab1 occurs 0,

vbeln like vbak-vbeln,

end of itab1.

data : begin of itab2 occurs 0,

vbeln like vbak-vbeln,

end of itab2.

select vbeln from vbfa into table itab1where vbelv = final_data-xblnr1 or vbelv = final_data-xblnr2.

if not itab1[] is initial.

select vbeln from vbrk into table itab2

for all entriels in itab1

where vbeln = itab-vbeln and fkart = 'RTS'.

endif.

Cheers,

Sasi

Re: joining select statements-performance problem

Former Member — Tue, 30 Aug 2005 13:35:22 GMT

One more suggestion.

Use IN instead of OR.

select vbeln from vbfa into itab-vbeln

<b>where vbelv IN ( final_data-xblnr1, vbelv = final_data-xblnr2 ).</b>

select single vbeln from vbrk into tabvbrk-vbeln

where vbeln = itab-vbeln and fkart = 'RTS'.

if sy-subrc eq 0.

move tabvbrk-vbeln to final_data-vbeln1.

modify final_data.

endif.

endselect.