How to query from BSEG table efficiently

[Former Member]

Hello community,

Well I know this question 'How to read BSEG efficiently' has been asked several times here. I have found some suggestions though but it still didn't optimize in a considerable way my ABAP program. So I am here to search for some new advices. First, here are some good answers I've found:

"AS BSEG is cluster table, BSEG Table consists of data from BSIS, BSAS, BSID, BSAD, BSIK, and BSAK tables.

BSIS: G/L Open Items

BSAS: G/L Closed Items

BSID: Customer Open Items(Account Receivables)

BSAD: Customer Closed Items)(Account Receivables)

BSIK: Vendor Open Items(Account Payables)

BSAK: Vendor Closed Items(Account Payables)

It is better to retrieve data from above tables based up on the data(vendor,customer,G/L account).Otherwise it will give performance issue if u use BSEG in production and may give run time error. So please use above tables to avoid run time error in Production." (No, we actually need to retrieve data from BSEG table)

Other suggestion:

" So instead of selecting from bseg by vbel2 = 'xxx' and posn2 = 'yyy' I should: a. get a list of all the bseg keys that qulify my search condition in table BSID: select burks,belnr,gjahr,buzel from BSID where vbel2 = 'xxx' and posn2 = 'yyy' b. select all the data I need from bseg using its key fields: select * from BSEG where burks = … and belnr = … and gjahr = .. and buzel = … Since BSID is a normal database table, I can define an index on columns vbel2, posn2, and since I select from bseg by its key columns ( on which there is a good unique database index) this bit of code will run pretty fast." (we used this)

Some suggest to read it as a sorted table (or even a hash table!).. I can't add an index there since it's a cluster table -I know there is a note that describe how to transform this cluster table into a transparent one (so you can add indexes) but this is not recommended and it could generate many dumps- and so on...

All the same, our program still takes a long time. I wonder if you have another solutions that could optimize the querying process?

Many Thanks!

[Former Member]

I see you have poor performance of the report. Since there are many reason out there, make sure report takes long time by BSEG query when asking this question.

Also, can you provide more detail about your current query, BSEG size, which method did you try before, did your query gain better performance then (even some micro seconds).

[BaerbelWinkler]

Hi Hasnae,

here is what I did when I needed to select a huge amount of data from BSEG:

1. Select relevant data from BKPF (or another suitable table to give you a list of needed BSEG-keys) into an internal table (itab1) holding only the required keys for subsequent retrieval of BSEG-data
2. Define a parameter on the selection-screen in which to enter a "package size", like e.g. 1000
3. Copy the first "package" into another internal table (APPEND LINES OF itab1 FROM 1 TO 1000 TO itab2)
4. Make use of a DO-Loop
5. Select and process BSEG-data within the loop for just (e.g.) 1000 entries
6. Determine the from- and to-index for the next package by adding the package-size value (make sure to set the to-value to the max-number of lines in itab1 if the calculated value is higher than that)
7. Leave the loop if the from-index is higher than max-number of lines in itab1
8. Copy the next "package" from itab1 into itab2
9. Select the next "package" from BSEG

Doing it with "packages" only makes sense if you really need to process a lot of data. Specifying the size via the selection-screen gives you the option to test the logic with a fairly low number in development and then increasing it in QA or PROD to see how many entries can be processed in one go while still being performant.

Disclaimer: As I didn't quickly find an actual example for the code I used some time ago, the write-up may not be 100% complete. You'll need to be especially careful to not create an endless-loop situation. I do however remember that retrieving the data in packages like described above, made it surprisingly quick to retrieve BSEG-data for a whole year (if not more).

Hope this helps!

Cheers

Baerbel

[Former Member]

Hello Barbel,

Well Actually the idea of retrieving the data in packages did really make a huge difference! I didn't follow the exact steps you've described but I am now able to load in 2days what i used to load in a week.

Thank you very much you have been very helpful.

Cheers

[chaouki_akir]

There is main point that is missing in your program: the criteria s of selection

Can you show us your selection screen ?

PS: your can refer to standard transactions that are doing such queries (for example FBL3N)

[Jelena_Perfiljeva]

As correctly pointed out in the comments, it's not clear what exactly is the selection criteria.

Personally, I've been able to use either the index tables (there is also BSIS/BSAS) or started with BKPF and then FAE to BSEG. Of course, the latter is not good either if you're looking for tons of records. What Baerbel suggested is a good option if there is no other choice.

Make sure to run a performance check on your code though to confirm that BSEG is indeed the real issue.

And, of course, the best solution is to migrate to S/4HANA where this problem has been resolved by dumping BSEG & Co altogether. 🙂