selection from bkpf & bseg

[Former Member]

Hi experts,

Iam trying to get the data from bkpf & bseg tables.

It is consuming lot of time.What is the better way to improve the performance using select statement..

select bukrs belnr gjahr blart bldat
       budat cpudt kursf usnam waers xblnr
from bkpf into corresponding fields of table t_bkpf
                  where bukrs in s_bukrs and
                        belnr in s_belnr and
                        gjahr in s_gjahr and
                        budat in s_budat and
                        blart in s_blart and
                        xblnr in s_xblnr.

loop at t_bkpf.
    select * from bseg where belnr = t_bkpf-belnr and
                                    bukrs = t_bkpf-bukrs and
                                    gjahr = t_bkpf-gjahr.
      move-corresponding bseg to t_bseg.

      if sy-subrc = 0.
        append t_bseg.
        clear t_bseg.
      endif.
    endselect.
  endloop.

<REMOVED BY MODERATOR - REQUEST OR OFFER POINTS ARE FORBIDDEN>

thanks

kaki

Edited by: Alvaro Tejada Galindo on Dec 26, 2008 11:05 AM

[Former Member]

Hi experts,

Iam trying to get the data from bkpf & bseg tables.

It is consuming lot of time.What is the better way to improve the performance using select statement..

select bukrs belnr gjahr blart bldat
       budat cpudt kursf usnam waers xblnr
from bkpf into corresponding fields of table t_bkpf
                  where bukrs in s_bukrs and
                        belnr in s_belnr and
                        gjahr in s_gjahr and
                        budat in s_budat and
                        blart in s_blart and
                        xblnr in s_xblnr.

loop at t_bkpf.
    select * from bseg where belnr = t_bkpf-belnr and
                                    bukrs = t_bkpf-bukrs and
                                    gjahr = t_bkpf-gjahr.
      move-corresponding bseg to t_bseg.

      if sy-subrc = 0.
        append t_bseg.
        clear t_bseg.
      endif.
    endselect.
  endloop.

<REMOVED BY MODERATOR - REQUEST OR OFFER POINTS ARE FORBIDDEN>

thanks

kaki

Edited by: Alvaro Tejada Galindo on Dec 26, 2008 11:05 AM

[Former Member]

hi,

try with this

select bukrs belnr gjahr blart bldat

budat cpudt kursf usnam waers xblnr

from bkpf into corresponding fields of table t_bkpf

where bukrs in s_bukrs and

belnr in s_belnr and

gjahr in s_gjahr and

budat in s_budat and

blart in s_blart and

xblnr in s_xblnr.

select (specify the fields and avoid *) from bseg

into table t_bseg for all entries in t_bkpf

where where belnr = t_bkpf-belnr and

bukrs = t_bkpf-bukrs and

gjahr = t_bkpf-gjahr.

cheers,

sasi

[Former Member]

hi,

if you are specific to customer / vendor then we can go for BSAD/BSAK instead of BSEG

cheers,

sasi

[Former Member]

Hi Kaki,

First things first, Please avoid using selects in a loop.

This is a performance over head.Instead , use "for all entries" statement.

MOre over when you make selects on huge tables like BKPF and BSEG, use indexing for those tables in se11.

Regards,

ravi

[Former Member]

Kaki,

If your itab t_bkpf has the same structure as that of selection fields, then dont use the "Corresponding fields".

using corresponding fields will add additional load for matching the field name.

Also if you can, instead of using "IN" operants use "="...like BUKRS = '2929' . This will depend on your requirement.

This will help in improving some what.

Btw, keep in mind your query will take time depending on how many rows exists.

[Former Member]

Hi,

Try with Logical database. For example you can use BRM - Accounting documents.

Svetlin

[Former Member]

Hi experts..

Thanks for all the replies.

Mr.Sasi solution was workedout.

Thank u

kaki

[Former Member]

Hi Lavanya,

We can not use Clustered tables(bseg) & pooled tables

(ex: a001) in joins.

cheers

kaki

[Former Member]

hi

You can use joins in this to reduce the overhead of two selects as there are common selection fields bukrs gjahr and belnnr.

Select (whatever fields u want) from bkpf

join bseg

on bseg_{belnr = bkpf}belnr and

bseg_{bukrs = bkpf}bukrs and

bseg_{gjahr = bkpf}gjahr

where (give the selection conditions).

Regards.

[Former Member]

hi,

i think this will not work because we cannot use join condition on cluster tables.

thanks,

anupama.

[Former Member]

Make sure that s_bukrs is not empty.

If s_belnr is empty, retrieve all of the document statuses from the domain and use them in the select and ensure that at least one of s_xblnr and s_budat is not empty. This will allow an index to be used.

Rob