Performance issue with BSEG

[Former Member]

Hi,

I am having serious performance issue due to BSEG table .I am having a change request in which I have to solve the performance issue with regard to BSEG. The situation was that previously they had used select * on both BKPF and BSEG. I removed the select * and selected only those fields which are required as shown below. I also tried using cursors. But the problem is happening in the TEST server where BSEG is having more than 1 crore entries. I have gone through some threads but still not able to understand how to solve this problem. Please help

select bukrs belnr gjahr bldat bstat from bkpf into table T_BKPF_p

WHERE BUKRS IN sd_bukrs AND

BLDAT < s_bldat-low

and BSTAT = ' ' .

select bukrs belnr gjahr shkzg dmbtr hkont from bseg into table T_BSEG_C

FOR ALL ENTRIES IN t_BKPF_p

WHERE BUKRS = T_bkpf_p-bukrs

AND BELNR = T_bkpf_p-belnr

AND GJAHR = T_bkpf_p-gjahr

AND HKONT = SKB1-SAKNR.

[Former Member]

Hi,

I am having serious performance issue due to BSEG table .I am having a change request in which I have to solve the performance issue with regard to BSEG. The situation was that previously they had used select * on both BKPF and BSEG. I removed the select * and selected only those fields which are required as shown below. I also tried using cursors. But the problem is happening in the TEST server where BSEG is having more than 1 crore entries. I have gone through some threads but still not able to understand how to solve this problem. Please help

select bukrs belnr gjahr bldat bstat from bkpf into table T_BKPF_p

WHERE BUKRS IN sd_bukrs AND

BLDAT < s_bldat-low

and BSTAT = ' ' .

select bukrs belnr gjahr shkzg dmbtr hkont from bseg into table T_BSEG_C

FOR ALL ENTRIES IN t_BKPF_p

WHERE BUKRS = T_bkpf_p-bukrs

AND BELNR = T_bkpf_p-belnr

AND GJAHR = T_bkpf_p-gjahr

AND HKONT = SKB1-SAKNR.

[aris_hidalgo]

Hi,

Before doing FOR ALL ENTRIES in BSEG, check first if T_BKPF_P has records. Also, consider using the index tables of BSEG for vendors(BSIK, BSAK), customers(BSID, BSAD) and GL(BSIS, BSAS).

Hope it helps.

[Former Member]

Hi,

I have already checked if t_BKPF_p has any records. But the problem starts when you select from BSEG. Also since BSEG is a cluster table you cannot create index. Also I have used three out of four primary key fields and yet it is taking hours for the select query to execute in the TEST server

[Former Member]

Look at [Quickly Retrieving FI document Data from BSEG|http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3417100)ID0608414050DB01579708719316768280End?blog=/pub/wlg/7692]

[Former Member]

Hi,

I have gone through the blog but the blog is about getting the key fields for BSEG. I already have 3 of the key fields for BSEG. Also the blog says that if you have company code and document number then performance will not be an issue. In my case I am having the company code and document number but the problem is that in the TEST server BSEG is having crores of entries which is creating problems in performance

[ThomasZloch]

Are you sure it's the BSEG select? Did you trace it with ST05? Your BKPF select doesn't look good due to the "BLDAT < s_bldat-low" condition. Also, since you seem to select for a single G/L account ("AND HKONT = SKB1-SAKNR"), I would investigate if selecting from BSIS and BSAS is the better option. All fields you are selecting are contained in these tables, so you might not even need to select from BKPF and BSEG. Prerequisite is that line item display is active for the G/L account (SKB1-XKRES = "X").

Thomas

[Former Member]

Hi Thomas,

Yes I have to select from the BSEG table. The requirement is that we have to modify the standard program -J_1GTBGL0. This program retrieves G/L accounts based on posting dates but as per the requirement specific to a particular country I need to exract the data on the basis of document date and not posting date .So for a particular date range and a given company code I need to extract all the G/L account numbers, irrespective of whether they are open or closed and sum the credit and debit and display it

[Former Member]

How does it work if there is a single company code in SD_BUKRS?

Rob

[Former Member]

Hi,

BSEG is cluster table. A cluster table has no. of records. So keep avoid to extract data from this table. Even there are no primary index of his table so create the secondary index on this table to extract the data if compulsory to extract from this table. Performance wise it's always create problem.

Second while taking field name take in sequence and same sequence keep in your internal table. There are no other way to solve this issue if you are extracting data from this this table. So try to find out some alternate table instead of BSEG table.

Regds,

Rakesh

[ThomasZloch]

I beg your pardon?

[Former Member]

Interesting.

Rob

[Former Member]

Hi Rob,

The requirement was that for a given company code, example say 1036 I need to extract all the G/L accounts for a given range of document date. The reason I am using BSEG is that when you select the radio button - Fiscal Period Selection in the standard program J_1GTBGL0, what happens is that SAP retrieves the G/L account documents from GLT0 table in which the documents are stored based on posting period, but we have a special requirement to modify the program such that we extract all the documents based on document date and not posting date.For this I have used BSEG which is giving performance issue. Now as per your blog if we have company code and document number available BSEG does not give performance issue, but I am having both of them and yet it is giving performance issue for BSEG table

[christine_evans]

I don't know what SAP release you are on, but now that we're on ECC6 I'm getting the impression (no, I haven't done any proper timing tests) that using FAE on large amounts of data is slower than it used to be.

I did try looking at SAP note 48230 which talks about database parameters that can affect FAE, but the idea that trying any of these might affect performance elsewhere put me off.

You could try using a SELECT....ENDSELECT with RANGES and a package size within a WHILE loop (making sure that on each loop pass the contents of the RANGES tables don't exceed the limit which will make it dump) and see if that makes any difference

WHILE l_subrc EQ 0.

    SELECT
           bukrs belnr buzei bltxt
           objnr
           kstar  "Cost Element
     FROM covp  PACKAGE SIZE 10000
     APPENDING TABLE i_covp
     WHERE lednr EQ c_ledger
       AND objnr IN r_objnr_sel
       AND gjahr IN r_gjahr
       AND versn EQ pa_versn
      ADD sy-dbcnt TO l_record_counter.
    ENDSELECT. "Select from COVP..

  ENDWHILE. "WHILE l_subrc EQ 0 ..

[Former Member]

Christine - the PACKAGE SIZE addition will help with memory management but not for overall performance. After all, it may introduce multiple ENDSELECTs.

Rob

[christine_evans]

>

> Christine - the PACKAGE SIZE addition will help with memory management but not for overall performance. After all, it may introduce multiple ENDSELECTs.
> 
> Rob

I know that people say that using SELECT....ENDSELECT is always the wrong thing, but I have seen situations where it seems to work surprisingly efficiently when selecting large amounts of data ...... especially when compared with FAE. Might not make any difference but I think worth a try - in this sort of situation anything is worth a try. I had a really large select from COVP to try to optimise and changing the package size did seem to have an beneficial effect.

[Former Member]

@Christine - I wasn't really concerned with the additional SELECT/ENDSELECT blocks. It will add a small performance heit, but not enough to worry about. But I really haven't seen a case where the PACKAGE SIZE helps speed things up. But if you can provide an example, I'd be happy to change my tune.

@shah kunal - I'd look more closely at what Thomas had to say. The problem with the SELECT from BSEG is that you are retrieving a large amount of data. But you are retrieving almost as much from BKPF.

Try changing the SELECT from BKPF to test for equality on the one company code you are using.

Rob

[Former Member]

Hi Kunal,

Here is my take on your issue.

In your select statement on BKPF you are selecting every BKPF record for a specified company code and blank document status that was created before a specified date. If your company has implemented SAP 10 years ago, and your user enters todays date and leaves the company code field blank you will effectively be retrieving almost all the records from BKPF (excluding the ones created today or those with non-blank document status). This would effectively be a huge amount of data. After that you are looking for the corresponding BSEG records for all the records that you have selected in BKPF.

My question to you is why do you need to look at all the records before a given date? Why not ask the user to enter a smaller date range and make the document date and the company code a mandatory entry? You do not have to look at 10 years worth of data especially if you are running this online (as opposed to in the background).

Your BSEG select looks correct. There is very little that you can do except for adding BUZEI to the field list. If you use for all entries and do not include the entire primary key you could lose data.

TABLES: bkpf,
        skb1.

SELECT-OPTIONS: s_bldat  FOR bkpf-bldat OBLIGATORY,
                sd_bukrs FOR bkpf-bukrs OBLIGATORY.

TYPES: BEGIN OF ty_bkpf,
        bukrs TYPE bkpf-bukrs,
        belnr TYPE bkpf-belnr,
        gjahr TYPE bkpf-gjahr,
        bldat TYPE bkpf-bldat,
        bstat TYPE bkpf-bstat,
      END OF ty_bkpf,

      BEGIN OF ty_bseg,
        bukrs TYPE bseg-bukrs,
        belnr TYPE bseg-belnr,
        gjahr TYPE bseg-gjahr,
        buzei TYPE bseg-buzei,
        shkzg TYPE bseg-shkzg,
        dmbtr TYPE bseg-dmbtr,
        hkont TYPE bseg-hkont,
      END OF ty_bseg.

DATA: t_bkpf_p TYPE TABLE OF ty_bkpf,
      t_bseg_c TYPE TABLE OF ty_bseg.


SELECT bukrs
       belnr
       gjahr
       bldat
       bstat
FROM bkpf
INTO TABLE t_bkpf_p
WHERE bukrs IN sd_bukrs
AND   bldat IN s_bldat
AND   bstat EQ space .

IF NOT t_bkpf_p[] IS INITIAL.

  SELECT bukrs
         belnr
         gjahr
         buzei
         shkzg
         dmbtr
         hkont
    FROM bseg
    INTO TABLE t_bseg_c
    FOR ALL ENTRIES IN t_bkpf_p
    WHERE bukrs EQ t_bkpf_p-bukrs
    AND   belnr EQ t_bkpf_p-belnr
    AND   gjahr EQ t_bkpf_p-gjahr
    AND   hkont EQ skb1-saknr.

ENDIF.

[Former Member]

Hi Mark, if you do a select on BSEG and how many times this would hit the Database to fetch the records from BSEG into internal table?

Will this first fetch all the records into internal table and do the processing or will it hit the DB every single time and get single record at a time, that would be an expensive in terms of I/O.

Thanks

SM