Performance issue in fething records from BSEG

[Former Member]

Dear All

I am fetching few fields from BSEG table for all entries I get from BKPF. I get around 8000 records from BKPF for select options.

When fetching records from BSEG table, its taking long time to execute and finally resulting in time out error.

I tried checking secondary tables for BSEG:

GL Secondary table

BSAS

BSIS

Vendor Table

BSAK

BSIK

Customer Table

BSAD

BSID

but couldnt find this field : BSEG-KOART in any of these tables.

Is there any other way to get the query resolved? Is this table BSE_CLR of use in this case?

Kindly help. Thanks in advance...

Regards

Dinesh

[ThomasZloch]

Selecting BKPF then selecting FAE on BSEG normally works fine when done correctly (full primary key used, only relevant fields are read, etc.).

8000 documents is not that many, it depends how many items each document has.

How do your actual select statements look like?

KOART is not in the index tables, since it is implicitly known, KOART = last letter of table name.

Thomas

[Former Member]

Hi Thomas

Thanks for your inputs!!

My select query is :

SELECT bukrs belnr gjahr

augbl bschl koart

dmbtr zuonr vbeln kunnr

FROM bseg

INTO TABLE lt_bseg

FOR ALL ENTRIES IN t_bkpf_dz

WHERE bukrs = t_bkpf_dz-bukrs

AND gjahr = t_bkpf_dz-gjahr

AND augbl = t_bkpf_dz-belnr

AND bschl IN (c_01, c_15, c_11)

AND koart = c_d

AND kunnr IN s_kunag[].

Here c_d is 'D', c_01 is 01 c_15 is 15 and c_11 is 11. s_kunag is empty.

Regards...

Dinesh

Kindly clarify on 'KOART = last letter of table name' and "selecting FAE from BKPF".

[Former Member]

Hey!

Where is BELNR

belnr is key field and would improve your run time a lot

[Former Member]

Check the change in ur code...

SELECT bukrs belnr gjahr
augbl bschl koart
dmbtr zuonr vbeln kunnr
FROM bseg
INTO TABLE lt_bseg
FOR ALL ENTRIES IN t_bkpf_dz
WHERE bukrs = t_bkpf_dz-bukrs
*********Added BELNR START
AND belnr = t_bkpf_dz-belnr
*********Added BELNR END
AND gjahr = t_bkpf_dz-gjahr
AND augbl = t_bkpf_dz-belnr
AND bschl IN (c_01, c_15, c_11)
AND koart = c_d
AND kunnr IN s_kunag[].

[Former Member]

Hi Tripat

Thanks for helping!!

I had given this thing a thought before, but issue is that i am fetching clearing documents:

AND augbl = t_bkpf_dz-belnr

If this change is done:

*********Added BELNR START

AND belnr = t_bkpf_dz-belnr

*********Added BELNR END

then wouldnt it affect the actual purpose?

Regards

Dinesh

[Former Member]

Have you see table AGKO

[ThomasZloch]

BELNR is missing in WHERE-condition for BSEG, that why it is slow, see other replies.

Table BSIS -> KOART of all items is "S". Table BSIK -> KOART of all items is "K", and so on.

FAE = For All Entries.

Thomas

[Former Member]

Hi Thomas

I have to select cleared items, so i am putting augbl = belnr.

Can you tell which of tables is Koart = "D". This will solve my purpose.

Many thanks!!!

Regards

Dinesh

[Former Member]

>

> Hi Tripat
> 
> Thanks for helping!!
> 
> I had given this thing a thought before, but issue is that i am fetching clearing documents:
> 
> AND augbl = t_bkpf_dz-belnr
> 
> If this change is done: 
> *********Added BELNR START
> AND belnr = t_bkpf_dz-belnr
> *********Added BELNR END
> 
> then wouldnt it affect the actual purpose?
> 
> Regards
> Dinesh

No - that is what you are trying to do - fetch the clearing document knowing the original document.

Rob

[former_member182566]

>

> No - that is what you are trying to do - fetch the clearing document knowing the original document.

Rob, based on his initial coding, I am assuming he is getting clearing documents from BKPF and then he wants to know the items cleared by that document. That's why I suggested function GET_CLEARED_ITEMS.

Rui Dantas

[Former Member]

Fine - I was just commenting on the code he posted.

Rob

[Former Member]

Thanks to all for valuable inputs.

Points awarded!!!

[Former Member]

Can u write the bkpf and bseg selection part of code here

[Former Member]

Hi!

My BKPF query:

SELECT bukrs belnr gjahr blart bldat budat xblnr

INTO TABLE lt_bkpf_dz

FROM bkpf

WHERE bukrs IN s_bukrs[]

AND blart = c_dz

AND budat IN s_budat[]

AND bstat = space

AND stblg = space.

Here around 7000 -8000 records are fetched. Now records from BSEG are fetched:

SELECT bukrs belnr gjahr

augbl bschl koart

dmbtr zuonr vbeln kunnr

FROM bseg

INTO TABLE lt_bseg

FOR ALL ENTRIES IN t_bkpf_dz

WHERE bukrs = t_bkpf_dz-bukrs

AND gjahr = t_bkpf_dz-gjahr

AND augbl = t_bkpf_dz-belnr

AND bschl IN (c_01, c_15, c_11)

AND koart = c_d

AND kunnr IN s_kunag[].

The report is not able to process further.

Regards

Dinesh

[matt]

Tip: performance issues go in the ABAP Performance and Tuning forum. I've fixed that for you. Please be more careful next time.

matt

[former_member182566]

Hi Dinash,

As someone mentioned earlier, table AGKO should solve your problem, but the logic using that table might not be that straightforward.

In the past I have used function GET_CLEARED_ITEMS (that's what the standard uses) with good performance results (though you'll have to call it once for every document). This function will not work in SE37, but you should be able to make it work in your program.

Example of usage:

* postab declaration copied from RFEPOSC1
DATA:    BEGIN OF postab OCCURS 50,
           xauth(1)      TYPE c,                 " Berechtigung?
           xhell(1)      TYPE c.                 " Hell anzeigen?
           INCLUDE STRUCTURE rfpos.              " Listanzeigen-Struktur
           INCLUDE rfeposc9.                     " Kunden-Sonderfelder
DATA:      xbkpf(1)      TYPE c,                 " BKPF nachgelesen?
           xbseg(1)      TYPE c,                 " BSEG nachgelesen?
           xbsec(1)      TYPE c,                 " BSEC nachgelesen?
           xbsed(1)      TYPE c,                 " BSED nachgelesen?
           xpayr(1)      TYPE c,                 " PAYR nachgelesen?
           xbsegc(1)     TYPE c,                 " BSEGC nachgelesen?
           xbsbv(1)      TYPE c,                 " BSBV nachgelesen?
           xmod(1)       TYPE c,                 " POSTAB modifiziert?
         END OF postab.

DATA lt_items LIKE postab OCCURS 1 WITH HEADER LINE.

* call the function for every clearing document
CALL FUNCTION 'GET_CLEARED_ITEMS'
  EXPORTING
    i_belnr                      = '...'
    i_bukrs                      = '...'
    i_gjahr                      = '...'
    i_bvorg                      = ''
  tables
    t_items                      = lt_items
 EXCEPTIONS
   NOT_FOUND                    = 1
   ERROR_CLEARED_ACCOUNTS       = 2
   OTHERS                       = 3.

Hope this helps,

Rui Dantas

[former_member182566]

Ah, and by the way, D = Debitor (customers), so you should find the data in BSAD (notice the D in the end).

Using the above function, though, would make your code more generic (the function goes to the correct table depending on each case).

Rui Dantas