topic Performance Issue while accessing table BSEG in Application Development and Automation Discussions

Performance Issue while accessing table BSEG

Former Member — Mon, 14 Feb 2011 20:08:54 GMT

Hi All,

I see a performance issue when accessing tables BSEG using the data from table BKPF.

I extracted the fields from BKPF table and then made the following statemnt :

Select bukrs, belnr, gjahr, buzei, hkont, txjcd, mwart

from bseg

for all entries in it_bkpf

where bukrs EQ it_bkpf-BUKRS AND

belnr EQ it_bkpf-belnr AND

gjahr EQ it_bkpf-gjahr AND

buzei EQ it_bkpf-buzei AND

hkont EQ it_bkpf-hkont.

But this statement causes a serious performance issue and my program stops working from here.

Could someone please help me out with this.

Thanks & Regards,

Sushanth Hulkod

Re: Performance Issue while accessing table BSEG

Former Member — Mon, 14 Feb 2011 20:17:43 GMT

Make sure that you're checking that the internal table with BKPF entries is not empty. I hope you know what happens if that is the case. That apart, since you are using the key BUKRS/BELNR/GJAHR, am sure there shouldn't be an issue. Also, do make sure that the index based on the key fields is active. This is generally a BASIS job. Generally this index should be ON.

Re: Performance Issue while accessing table BSEG

brad_bohn — Mon, 14 Feb 2011 20:44:57 GMT


But this statement causes a serious performance issue and my program stops working from here.

Well, for how many entries in your table? It certainly would not be an issue for only a few entries. Why are you using BUZEI and HKONT in the FOR ALL ENTRIES construct - they aren't BKPF fields? Why aren't those fields processed against parameters or select-options?

Re: Performance Issue while accessing table BSEG

Former Member — Mon, 14 Feb 2011 22:54:10 GMT

Hi Sushanth,

Also remove the BUZEI and HKONT from the WHERE clause since these fields are not part of the BKPF table and therefore blank.

In case you are only interested in GL account items(KOART = S as opposed to vendor/customer postings), you should use table BSIS to get the open items. Use BSAS to get the cleared items.

Kind regards,

Robert

PS. Sorry Brad did not want to repeat you, was in the middle of a my reply when you replied.....

Edited by: RJ. Schamhart on Feb 14, 2011 2:54 PM

Re: Performance Issue while accessing table BSEG

former_member186741 — Mon, 14 Feb 2011 23:13:25 GMT

Your source table for the FAE clause should be sorted to get good performance.

Also, as BRAD AND rj HAVE POINTED OUT, SOME OF THE FIELDS YOU ARE USING INTHE SQL WILL NOT HAVE VALUES............. but WHAT PUZZLES ME IS HOW YOU ARE getting any results at all as the null values for these fields will not be very helpful to the select.

Re: Performance Issue while accessing table BSEG

former_member196280 — Tue, 15 Feb 2011 02:12:16 GMT

Add below code, it may solve your problem.

IF not it_bkpf[] is initial. <-- Add

Select bukrs, belnr, gjahr, buzei, hkont, txjcd, mwart
from bseg
for all entries in it_bkpf
where bukrs EQ it_bkpf-BUKRS AND
belnr EQ it_bkpf-belnr AND
gjahr EQ it_bkpf-gjahr AND
buzei EQ it_bkpf-buzei AND
hkont EQ it_bkpf-hkont.

ENDIF. <-- Add

Regards,

SaiRam

Re: Performance Issue while accessing table BSEG

Former Member — Tue, 15 Feb 2011 03:06:19 GMT

Hi All,

Though my query now is :-

If it_bkpf[] is not initial.

Select bukrs belnr gjahr buzei

from bseg into

corresponding fields of table it_bseg

for all entries in table it_bkpf

where bukrs = it_bkpf-bukrs

and belnr = it_bkpf-belnr

and gjahr = it_bkpf-gjahr.

Endif.

There still seems to be a performance issue and my pgm stops at this point.

Could someone help me out with this.

Thanks & Regards,

Sushanth Hulkod

Re: Performance Issue while accessing table BSEG

Former Member — Tue, 15 Feb 2011 03:23:36 GMT

I see nothing wrong with your code. Are you sure this is where the problem is?

Have you run ST05 to analyze this?

How many entries in IT_BKPF?

Rob

Re: Performance Issue while accessing table BSEG

former_member186741 — Tue, 15 Feb 2011 03:40:48 GMT

Your source table for the FAE clause should be sorted to get good performance.

ie sort it_bkpf by bukrs belnr gjahr

Re: Performance Issue while accessing table BSEG

SuhaSaha — Tue, 15 Feb 2011 05:02:33 GMT


> Your source table for the FAE clause should be sorted to get good performance.
> ie sort it_bkpf by bukrs belnr gjahr

Hello,

In addition to what Neil has added for FAE to work effeciently; you need to perform a D.A.D on the source table based on the fields which are used in the WHERE clause. (Of couse the check for the emptiness of the table is a MUST)

BR,

Suhas

Re: Performance Issue while accessing table BSEG

Former Member — Tue, 15 Feb 2011 05:14:35 GMT

Hi,

Declare your it_bseg with only required fields i.e bukrs belnr gjahr buzei.

and remove"into corresponding fields of".

and use open cursor method.

Regards

Ganesh Reddy

Re: Performance Issue while accessing table BSEG

Former Member — Tue, 15 Feb 2011 05:23:02 GMT

Hi,

The query you have written is fetching all the records exist in BSEG table. You have to excecute this only if there is atleast a single entry in it_bkpf. Write if condition as below..

If it_bkpf[] is not initial.

Select bukrs, belnr, gjahr, buzei, hkont, txjcd, mwart

from bseg

for all entries in it_bkpf

where bukrs EQ it_bkpf-BUKRS AND

belnr EQ it_bkpf-belnr AND

gjahr EQ it_bkpf-gjahr AND

buzei EQ it_bkpf-buzei AND

hkont EQ it_bkpf-hkont.

Endif.

Hope this helps.

Re: Performance Issue while accessing table BSEG

Former Member — Tue, 15 Feb 2011 05:27:34 GMT

Hi Sushanth

1. Always check for all entries table is initial or not, then apply the selection.

Like in your case.

if it_bkpf is not initial.

do your selection here.

endif.

2. Sort your for all entries table as well.

3. Use operators instead of character statements,

like BELNR = it_bkpf-belrn not belnr EQ it_bkpf-belnr.

4. in where condition the sequence should be same.

why are you selecting BUKRS BELNR GJAHR and BUZEI again from BSEG table though you have selected them from BKPF table?

Why are you using into corresponding fields of table?

I hope you need to take care of all the above points and your query seems ok to me,

please debugg and check where is the problem exactly accuring?

Thanks

Lalit Gupta

Re: Performance Issue while accessing table BSEG

former_member196280 — Tue, 15 Feb 2011 05:45:46 GMT

Use the below code, it may improve the performance.

SELECT * INTO TABLE it_bkpf FROM bkpf
            WHERE belnr = '0100000000'
              AND bukrs = 'XXXX'
              AND gjahr = '2011'.

IF NOT it_bkpf[] IS INITIAL.

  SELECT bukrs  belnr  gjahr  txjcd  mwart
  INTO CORRESPONDING FIELDS OF TABLE it_bseg  <-- Try to push the contents to internal table or work area
  FROM bseg
  FOR ALL ENTRIES IN it_bkpf
  WHERE bukrs EQ it_bkpf-bukrs AND
  belnr EQ it_bkpf-belnr AND
  gjahr EQ it_bkpf-gjahr.

ENDIF.

Regards,

SaiRam

Re: Performance Issue while accessing table BSEG

Former Member — Tue, 15 Feb 2011 06:41:24 GMT

If it's Possible Avoid Using BSEG table it's Cluster Table , in place of that Use

bsis "Accounting: Secondary Index for G/L Accounts

bsas "Secondary Index for G/L Accounts (Cleared Items)

bsid " Accounting: Secondary Index for Customers

bsad " Secondary Index for Customers (Cleared Items)

BSAK " Accounting: Secondary Index for Vendors (Cleared Items)

BSIK "Accounting: Secondary Index for Vendors

Edited by: Shailesh Dhanani on Feb 15, 2011 7:41 AM

Re: Performance Issue while accessing table BSEG

Former Member — Tue, 15 Feb 2011 14:07:36 GMT

@Shailesh Dhanani -

> If it's Possible Avoid Using BSEG table it's Cluster Table , in place of that Use...

Absolutely incorrect

@Gamesh Reddy - how would OPEN CURSOR help?

@LalitG - INTO CORRESPONDING adds a very small bi of overhead but is not really a performance issue.

Rob

Re: Performance Issue while accessing table BSEG

brad_bohn — Tue, 15 Feb 2011 14:38:23 GMT

LalitG wrote:


3. Use operators instead of character statements,
like BELNR = it_bkpf-belrn not belnr EQ it_bkpf-belnr.
4. in where condition the sequence should be same.

Not really valid suggestions...

To the poster, some of the suggestions provided are valid and some are not; the valid ones are not likely to increase your performance by an order of magnitude.

You haven't answered the simple questions posed to you, such as the one both Rob and I asked - how many entries in the driver table? If you dump every single BKPF line into an internal table, it stands to reason that the performance on the BSEG SELECT would be bad, NO MATTER WHAT YOU DO. How do you know the BSEG read is the cause of the issue - have you traced the program?