Select faster...

[Former Member]

Hi experts,

I have a program that extract data from BSEG and BKPF, and this select is so slowler...Have any idea to do it more faster?

thanks a lot

select BUKRS BELNR GJAHR BUZEI KOKRS HKONT BSCHL WRBTR SHKZG LIFNR

from BSEG

into corresponding fields of itab

for all entries in ZBKPF (ZBKPF has 97.000 registers now )

where BUKRS = zbkpf-bukrs

and BELNR = zbkpf-belnr

and GJAHR = zbkpf-gjahr

and KOART = 'S'

and HKONT in ZCOUNT.

append itab.

endselect.

[Former Member]

Hi

Use

select BUKRS BELNR GJAHR BUZEI KOKRS HKONT BSCHL WRBTR SHKZG LIFNR

from BSEG

into corresponding fields of <b>table</b> itab

for all entries in ZBKPF (ZBKPF has 97.000 registers now )

where BUKRS = zbkpf-bukrs

and BELNR = zbkpf-belnr

and GJAHR = zbkpf-gjahr

and KOART = 'S'

and HKONT in ZCOUNT.

I would also suggest that you select first the entries from ZBKPF into an internal table and then fire the query on BSEG . This would avoid blocking of two tables together.

Regards

Suruchi

Message was edited by:

Suruchi Mahajan

[Former Member]

Hi Marc,

select BUKRS BELNR GJAHR BUZEI KOKRS HKONT BSCHL WRBTR SHKZG LIFNR

from BSEG

into <b>table</b> itab

for all entries in ZBKPF (ZBKPF has 97.000 registers now )

where BUKRS = zbkpf-bukrs

and BELNR = zbkpf-belnr

and GJAHR = zbkpf-gjahr

and KOART = 'S'

and HKONT in ZCOUNT.

Reward Points if it is useful.

Thanks

Hari krishna.

[Former Member]

Hi Marc,

Dont use corresponding fields remove corresponding fields and make sure that in the where condition use the secondary index order.i.e for eg in the where condition you are using this order

<b>where BUKRS = zbkpf-bukrs

and BELNR = zbkpf-belnr

and GJAHR = zbkpf-gjahr

and KOART = 'S'

and HKONT in ZCOUNT.</b>

If your secondary index has

BELNR

BUKRS

GJAHR

HKONT &

KOART

Then follw this order this will incress your performance.

Thanks

Vikranth Khimavath

[Clemenss]

Hi Khimavath ,

very useful recommendation for releases before R/3 3.1.

Meanwhile they have a database interface that takes care for key field sequence and translates into corresponding fields into moves at program generation time.

Really important is full key specification and pre-selection via secondary index tables (as I already mentioned).

Regards,

Clemens

[kostas_tsioubris]

Hi,

BSEG is a cluster table so the performance will not be the best. Maybe you should consider using an index table as BSIS.

Kostas

[Former Member]

Can I use BSIS instead BSEG? I'll try it!

[Former Member]

Hi,

Hi,

BSEG and BSIS (Accounting: Secondary Index for G/L Accounts)

BSEG-BUKRS = BSIS-BUKRS

BSEG-HKONT = BSIS-HKONT

BSEG-GJAHR = BSIS-GHAJR

BSEG-BELNR = BSIS-BELNR

BSEG-BUZEI = BSIS-BUZEI.

You have to table, BUKRS, HKONT, GJAHR, BELNR, BUZEI from BSEG and read BSIS. You can also table BUKRS & HKONT from BSEG and read BSIS and then find the document by BELNR.

<b>Reward points</b>

Regards

[Former Member]

check it,

select BUKRS BELNR GJAHR BUZEI KOKRS HKONT BSCHL WRBTR SHKZG LIFNR

from BSEG

into corresponding fields of table itab

for all entries in ZBKPF (ZBKPF has 97.000 registers now )

where BUKRS = zbkpf-bukrs

and BELNR = zbkpf-belnr

and GJAHR = zbkpf-gjahr

and KOART = 'S'

and HKONT in ZCOUNT.

[Former Member]

Hi Marc,

BSEG being a cluster table has all the tables around it merged. Thus contains all the fields of the tables around it.

For a similar requirement which required the customer's data am using BSID and BSAD to suffice the requirement thus keeping BSEG at bay.

Use the correct tables and avoid the usage of cluster table else maintanance and performance issues may arise.

Hope this solves and gives you some knowledge to use the tables against using BSEG.

Reward Points if useful.

Thanks,

Tej..

[Clemenss]

Hi Marc,

experienced developer will select from BSEG only if the full key of the table can be supplied with selection criteria.

For this purpose it is recommenden to select first from a useful secondary index table like BSID BSIS BSAS BSAK and what ther is more. Make a forum search on above tables and get all information youu may need.

I don't know your requirement in business terms, don't know the logic of ZBKPF. But I'm convinced you can reduce the number of records in ZBKF drastically using secondary index tables.Also, you can get the respective line item no BUZEI from those tables.

Regards,

Clemens

[Former Member]

Hi Marc,

As BSEG being a cluster table has all the tables around it merged. So it takes lot of time to execute it. So better to use the other alternative tables EX:- BSIS , etc.

Thanks& regards

Venkat