How do improve SELECT in BKPF/BSEG table.

[former_member914659]

Hi everyone.

I'm using this SELECT in a report program but processing this taking about 10 minutes in these two commands:

select belnr

           awkey

      from bkpf

      into table t_bkpf

      for all entries in t_cobk

      where awkey eq t_cobk-awkey.

   select belnr

             vbeln

             vbel2

             kstar

             kostl

             buzei

             kunnr

             vorgn

      from bseg

      into table t_bseg

      for all entries in t_bkpf

      where kstar eq '0000464001'

      and belnr eq t_bkpf-belnr

      and shkzg eq 'S'.

There is another way of improving these SELECT, or another table to use instead of BSEG ?

Tks!

[former_member333937]

Hi Ailton,

Very hard to comment on this since since I do not know what exactly your requirement is and what you are trying to achieve with the report.

That said, there are a few observations:

1. Please use Join instead of For All Entries whenever possible.

2. For first select query, you are not passing any primary key at all and for the second you are passing only one (BELNR). Are you sure do not have company code or fiscal year etc that you can pass to the select query? Doing that should immediately improve the performance.

3. Find out which select query is taking the most time. Then concentrate on that one.

4. I am not sure if it is a good idea but if you do not have other primary keys available and you do need better performance, maybe indexing with required fields might work.

Thanks,

Soumyajit

[Former Member]

Hi,

The select from BKPF you should always use AWTYP along with AWKEY to get the best use of index BKPF~4.

And I would try to avoid BSEG select . Insead of that try to use Indexed table like BSID/BSAD for customer, BSAK/BSAK for Vendors and BSIS/BSAS for G/L lines.

And off course try to twick your where clause for better use of indexes.

[former_member38678]

Hi Ailton,

Please pass the BUDAT when you querying BKPF  if you have a date parameter in your selection it will fetch the data quickly, and then go "FOR ALL ENTRIES" for BSEG to fetch the data.

Otherwise try passing the indexes below in BKPF,

BKPF~1
BKPF~2
BKPF~3
BKPF~4
BKPF~5
BKPF~6
BKPF~BUT..

Hope it will help you.

Regards,

Sri.

[Former Member]

In addition to BELNR and AWKEY, you have to SELECT BUKRS and GJAHR as  well. Then use BUKRS, BELNR and GJAHR in your SELECT on BSEG. This is the only proper way to SELECT on BSEG.

For the SELECT on BKPF, you have to add AWTYP.

Rob

Message was edited by: Rob Burbank