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: 

performance promblem with FOR ALL ENTRIES

Former Member
0 Kudos
208

hello frnds...

this code is taking too much time, is thr any way to optimize this code....

SELECT  bukrs
          belnr
          gjahr
          bldat
          blart
          INTO TABLE t_bkpf
          FROM bkpf
          FOR ALL ENTRIES IN t_sum
          WHERE gjahr = t_sum-mjahr
          AND bukrs = t_sum-bukrs
          AND blart IN ('PR','KP','RE','SA')
          AND ( budat >= s_budat-low AND budat <= s_budat-high ).

  SELECT
         bukrs
         belnr
         gjahr
         buzei
         shkzg
         dmbtr
         vorgn
         matnr
         werks
         menge
         INTO TABLE t_voucher
         FROM bseg
         FOR ALL ENTRIES IN t_bkpf
         WHERE bukrs = t_bkpf-bukrs
         AND belnr = t_bkpf-belnr
         AND gjahr = t_bkpf-gjahr
         AND  hkont IN s_hkont
         AND matnr IN s_matnr
         AND werks IN  s_werks
         AND ( hkont  >= s_hkont-low AND hkont  <= s_hkont-high ).

thanks&regards

shishir

Edited by: Julius Bussche on Feb 26, 2009 12:27 PM

Please use meaningfull subject titles and code tags

1 ACCEPTED SOLUTION

Former Member
0 Kudos
95

Use Range tables and arrange documents type accordingly.

BSEG and MKPF tables will take more time than others

Try with inner join instead of for all entries.

14 REPLIES 14

Former Member
0 Kudos
95

hi,

Check this way,....

r_budat-sign = 'I'.
r_budat-option = 'BT'.
r_budat-low = s_budat-low.
r_budat-high = s_budat-high .
Append r_budat.

r_hkont-sign = 'I'.
r_hkont-option = 'BT'.
r_hkont-low = s_hkont-low.
r_hkont-high = s_hkont-high .
Append r_hkont.

SELECT bukrs
belnr
gjahr
bldat
blart
INTO TABLE t_bkpf
FROM bkpf
FOR ALL ENTRIES IN t_sum
WHERE gjahr = t_sum-mjahr
AND bukrs = t_sum-bukrs
AND blart IN ('PR','KP','RE','SA')
AND  budat IN r_budat.                    " Changes

SELECT
bukrs
belnr
gjahr
buzei
shkzg
dmbtr
vorgn
matnr
werks
menge
INTO TABLE t_voucher
FROM bseg
FOR ALL ENTRIES IN t_bkpf
WHERE bukrs = t_bkpf-bukrs
AND belnr = t_bkpf-belnr
AND gjahr = t_bkpf-gjahr
AND hkont IN s_hkont
AND matnr IN s_matnr
AND werks IN s_werks
AND  hkont in r_hkont.       " Changes

0 Kudos
95

thx for this code.... but still its taking too much time.....

0 Kudos
95

which select is taking more time?? and please give the number of entries coming for the tables that you are using for FOR ALL ENTRIES

0 Kudos
95

which select is taking more time?? and please give the number of entries coming for the tables that you are using for FOR ALL ENTRIES

0 Kudos
95

t_bkpf is taking time..... 7830 records cuming in this.......

0 Kudos
95

so u are talking about first select statement?

if yes then how many entries are in t_sum. and are they sorted or not.. (what are the fields of t_sum)

and see.. u are using only "t_sum-bukrs" in your selection. so before selection check if there are distinct entries of bukrs present in t_sum or not.

if not then filter out distinct bukrs value to some other table and then use for all entries for this new internal table with distinct bukrs values.

Former Member
0 Kudos
95

this code ofcoz is dependent on the the amount of data the internal table holds which are to be used for 'FOR ALL ENTRIES. check if those entries are sorted or not.. if not please sort then with the keys that u use for where clause..

Former Member
0 Kudos
96

Use Range tables and arrange documents type accordingly.

BSEG and MKPF tables will take more time than others

Try with inner join instead of for all entries.

0 Kudos
95

INNER JOIN may seem helpful...

but range table should not be used if the range exceeds 5000 entries

0 Kudos
95

>

> INNER JOIN may seem helpful

It may seem helpful but because BSEG is a cluster table it cannot be used here.

OP - please see [Quickly Retrieving FI document Data from BSEG|/people/rob.burbank/blog/2007/11/12/quickly-retrieving-fi-document-data-from-bseg]

Rob

former_member194613
Active Contributor
0 Kudos
95

how many lines are in your internal tables and how many lines in the DB tables

are all ranges filled?

Use between for the last condition.

Maybe archiving would be the best option.

Siegfried

0 Kudos
95

thanks all... problem solved......... thanks for ur suggestion's.....

former_member194613
Active Contributor
0 Kudos
95

10 points for this answer !???. Join was recommended by several, ranges only by this one.

There is a simple reasson behind the ranges are not recommended!

The ranges are faster for small tables but they will dimp when the ranges become too large!!!!

> Use Range tables and arrange documents type accordingly.

> BSEG and MKPF tables will take more time than others

> Try with inner join instead of for all entries.

0 Kudos
95

@Siegfried - you may be interested in this discussion in the Suggestions and Comments forum:

[Minus rating|;.

Rob