topic Re: Select faster... in Application Development and Automation Discussions

Select faster...

Former Member — Mon, 18 Jun 2007 09:27:09 GMT

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.

Re: Select faster...

Former Member — Mon, 18 Jun 2007 09:31:03 GMT

Use

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.

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

Re: Select faster...

Former Member — Mon, 18 Jun 2007 09:31:20 GMT

Hi Marc,

select BUKRS BELNR GJAHR BUZEI KOKRS HKONT BSCHL WRBTR SHKZG LIFNR

from BSEG

into 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.

Reward Points if it is useful.

Thanks

Hari krishna.

Re: Select faster...

Former Member — Mon, 18 Jun 2007 09:33:05 GMT

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

where BUKRS = zbkpf-bukrs

and BELNR = zbkpf-belnr

and GJAHR = zbkpf-gjahr

and KOART = 'S'

and HKONT in ZCOUNT.

If your secondary index has

BELNR

BUKRS

GJAHR

HKONT &

KOART

Then follw this order this will incress your performance.

Thanks

Vikranth Khimavath

Re: Select faster...

kostas_tsioubris — Mon, 18 Jun 2007 09:33:25 GMT

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

Re: Select faster...

Former Member — Mon, 18 Jun 2007 09:42:17 GMT

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.

Re: Select faster...

Former Member — Mon, 18 Jun 2007 09:45:00 GMT

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

Re: Select faster...

Former Member — Mon, 18 Jun 2007 09:49:20 GMT

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.

Reward points

Regards

Re: Select faster...

Former Member — Mon, 18 Jun 2007 10:01:55 GMT

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..

Re: Select faster...

Clemenss — Mon, 18 Jun 2007 10:02:20 GMT

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

Re: Select faster...

Former Member — Mon, 18 Jun 2007 10:27:30 GMT

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

Re: Select faster...

Clemenss — Mon, 18 Jun 2007 13:30:56 GMT

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