topic query optimization in Application Development and Automation Discussions

query optimization

Former Member — Wed, 27 Feb 2008 12:45:10 GMT

Hi,

assume it_bsis is having more than 10000 and will increase in future. i Know given below code will affect system performance, so, How should i split the code to attain good performance.

<REMOVED BY MODERATOR>

Code:


LOOP AT it_bsis into wa_bsis.

SELECT SUM( wrbtr )
                   FROM bsis
                   INTO wa_bsis-we86_amt
                   WHERE blart = wa_bsis-blart
                   AND   xref3 = wa_bsis-xref3
                   AND   bschl = '86'.


SELECT SUM( wrbtr )
                   FROM bsis
                   INTO wa_bsis-re86_amt
                   WHERE xref3 = wa_bsis-xref3
                   AND   blart = 'RE'
                   AND   bschl = '86'.

SELECT SUM( wrbtr )
                   FROM bsis
                   INTO wa_bsis-ro96_amt
                   WHERE xref3 = wa_bsis-xref3
                   AND   blart = 'RO'
                   AND   bschl = '96'.

MODIFY it_bsis FROM wa_bsis INDEX sy-tabix TRANSPORTING we86_amt re86_amt ro96_amt.
endloop.

Regards

s.senthil kumar.

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 3:14 PM

Re: query optimization

Former Member — Wed, 27 Feb 2008 12:57:45 GMT

Hi,

Avoid using select statement inside loop. This will increase run time. Instead, use 'for all entries'. Suppose itab1 has all values and u need to select values into itab2 based on all records of itab1, use for all entries in itab1 in the select query.

In ur case, have two seperate internal tables with same key fields and the second internal table with the fields to be modified.

for ex:

check itab1 is not initial.

select matnr werks into table itab2

from marc

for all entries in itab1

where matnr = itab1-matnr.

This will reduce ur execution time.

<REMOVED BY MODERATOR>

Regards,

Ramya

Edited by: Ramya Bashyam on Feb 27, 2008 2:01 PM

Edited by: Alvaro Tejada Galindo on Feb 27, 2008 3:12 PM

Re: query optimization

Former Member — Wed, 27 Feb 2008 20:17:49 GMT


FIELD-SYMBOLS: <FS_BSIS> LIKE LINE OF IT_BSIS.

LOOP AT it_bsis assiging <FS_BSIS>.
 
SELECT SUM( wrbtr )
                   FROM bsis
                   INTO wa_bsis-we86_amt
                   WHERE blart = <FS_BSIS>-blart
                   AND   xref3 = <FS_BSIS>-xref3
                   AND   bschl = '86'.
 
 
SELECT SUM( wrbtr )
                   FROM bsis
                   INTO wa_bsis-re86_amt
                   WHERE xref3 = <FS_BSIS>-xref3
                   AND   blart = 'RE'
                   AND   bschl = '86'.
 
SELECT SUM( wrbtr )
                   FROM bsis
                   INTO wa_bsis-ro96_amt
                   WHERE xref3 = <FS_BSIS>-xref3
                   AND   blart = 'RO'
                   AND   bschl = '96'.
 
MODIFY it_bsis FROM <FS_BSIS>.
endloop.

Use Field-Symbols...They are far way better than WorkAreas...

Greetings,

Blag.