Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Increasing performance of complex query through wa or for all entries

former_member242512
Participant
0 Kudos

Hi this a code it has performane issues . first of all the table used itab_rb_rs is an internal table with header line .

im using now ii_ itab_rb_rs in place of itab_rb_rs .

How can i make it more efficient . HOW CAN I USE FOR ALL ENTRIES or make use of work areas to increase its performance .

SELECT A~LIFNR A~BELNR 
              C~EKGRP A~CPUDT A~XBLNR B~EBELN B~EBELP B~WERKS
              B~WRBTR B~MENGE 
              B~SPGRS A~ZTERM A~WAERS
       APPENDING TABLE  ITAB_RB_RS
       FROM ( RBKP AS A INNER JOIN RSEG AS B ON
                 A~BELNR = B~BELNR AND
                 A~GJAHR = B~GJAHR ) INNER JOIN RBKP_BLOCKED AS C
               ON A~BUKRS = C~BUKRS
               AND A~GJAHR = C~GJAHR
                     WHERE A~BUKRS IN P_BUKRS
       AND A~LIFNR IN P_LIFNR
              AND B~WERKS IN P_WERKS .

Regards .

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi,

In your query you are using 3 tables, RBKP, RSEG and RBKP_BLOCKED. First of all you can retrieve data from RBKP table,

select LIFNR BELNR CPUDT XBLNR ZTERM WAERS
 from RBKP into table i_rbkp 
  where BUKRS IN P_BUKRS
       AND LIFNR IN P_LIFNR.

Then you can retrieve data from RSEG table for all entries in table i_rbkp.

select EBELN EBELP WERKS WRBTR MENGE SPGRS
from RSEG into table i_rseg
for all entries in i_rbkp
where BELNR = i_rbkp-BELNR AND
           GJAHR = i_rbkp-GJAHR AND
           WERKS IN P_WERKS.

Then you can retrieve data from table RBKP_BLOCKED for all entries in i_rbkp.

select EKGRP 
from RBKP_BLOCKED into table i_RBKP_BLOCKED
for all entries in i_rbkp
where BUKRS = i_rbkpBUKRS
            AND GJAHR = i_rbkp-GJAHR.

Try doing in this way.

Thanks,

Archana

9 REPLIES 9

Former Member
0 Kudos

Hi,

In your query you are using 3 tables, RBKP, RSEG and RBKP_BLOCKED. First of all you can retrieve data from RBKP table,

select LIFNR BELNR CPUDT XBLNR ZTERM WAERS
 from RBKP into table i_rbkp 
  where BUKRS IN P_BUKRS
       AND LIFNR IN P_LIFNR.

Then you can retrieve data from RSEG table for all entries in table i_rbkp.

select EBELN EBELP WERKS WRBTR MENGE SPGRS
from RSEG into table i_rseg
for all entries in i_rbkp
where BELNR = i_rbkp-BELNR AND
           GJAHR = i_rbkp-GJAHR AND
           WERKS IN P_WERKS.

Then you can retrieve data from table RBKP_BLOCKED for all entries in i_rbkp.

select EKGRP 
from RBKP_BLOCKED into table i_RBKP_BLOCKED
for all entries in i_rbkp
where BUKRS = i_rbkpBUKRS
            AND GJAHR = i_rbkp-GJAHR.

Try doing in this way.

Thanks,

Archana

0 Kudos

hi im not having data right now .

i want my final data in ITAB_RB_RS . also how to gather data for statement APPENDING TABLE ITAB_RB_RS .

whether thses 3 different joins will be efficient ?

regards .

0 Kudos

Hi,

Loop at internal table base table and read the 2 internal table with key and fetch the from the internal table and move to final output table.

for example.

loop at itab1 into wa_tab1.

read table itab2 into wa_tab2 with key <field> = wa_itab1-field binary search.

if sy-subrc = 0.

move req fields to wa of final internal table.

endif.

do above read for the 3rd intenal table too and move to final wa

append wa to intenal table.

endloop.

Regards,

Bharani

0 Kudos

Hi,

You can use all these tables to combine data in 1 final internal table.

Loop at i_rbkp into wa_rbkp.

* Move all data from rbkp into (LIFNR BELNR CPUDT XBLNR ZTERM WAERS) final internal table work area.

 read table rseg into wa_rseg with key BELNR = wa_rbkp-BELNR AND GJAHR = wa_rbkp-GJAHR.
 if sy-subrc eq 0.

* Move EBELN EBELP WERKS WRBTR MENGE SPGRS from rseg to final internal table work area.

 read table i_RBKP_BLOCKED into wa_RBKP_BLOCKED with key BUKRS = wa_rbkpBUKRS AND GJAHR = wa_rbkp-GJAHR.
 if sy-sub rc eq 0.

* Move EKGRP from RBKP_BLOCKED to final internal table work area.

 append wa_final to i_final.  "Append final work area to final internal table.
endif.
endif.
clear: wa_rbkp, wa_rseg, wa_RBKP_BLOCKED, wa_final.
endloop.

In this way you can combaine data into final internal table.

Thansk,

Archana

Edited by: Archana Pawar on Jan 29, 2010 10:16 AM

Former Member
0 Kudos

hi,

For All entries improve the performance .....

whenever used For All entries first check, the table must not be initial.

exp.

if  i_rbkp is not initial.

select EBELN EBELP WERKS WRBTR MENGE SPGRS
from RSEG into table i_rseg
for all entries in i_rbkp
where BELNR = i_rbkp-BELNR AND
           GJAHR = i_rbkp-GJAHR AND
           WERKS IN P_WERKS.

endif.

regards

Gaurav

0 Kudos

hi ,

And remove the dulplicate entries from internal table too..

Regards,

Bharani

Former Member
0 Kudos

Hi

Use FOR ALL ENTRIES..

select LIFNR BELNR CPUDT XBLNR ZTERM WAERS

from RBKP into table i_rbkp

where BUKRS IN P_BUKRS

AND LIFNR IN P_LIFNR.

select EBELN EBELP WERKS WRBTR MENGE SPGRS

from RSEG into table i_rseg

for all entries in i_rbkp

where BELNR = i_rbkp-BELNR AND

GJAHR = i_rbkp-GJAHR AND

WERKS IN P_WERKS.

select EKGRP

from RBKP_BLOCKED into table i_RBKP_BLOCKED

for all entries in i_rseg

where <conditions>

Former Member
0 Kudos

Hello,

Its a false notion that FOR ALL ENTRIES will work like magic if you are replacing INNER JOIN with FAE. Refer this for a detailed discussion

Using inner join you wont have the over head to combine the internal tables. If you are facing performance issues with inner join, make sure you use the primary keys in the where clauses and in those cases where its not possible, create secondary indexes on those fields

Vikranth

former_member242512
Participant
0 Kudos

thanks a lot for all posts