In other BLOGs, I showed some general techniques that you can use to improve the performance of your ABAP code. In this one, I will show some specific techniques that you can use to retrieve accounting document data.
BSEG is a cluster table and in the forums it is often said that you should avoid selecting directly against this table. This is simply incorrect. While BSEG is large and the non-key fields are not known to the underlying database, it is simply a table and if you take proper care in designing a SELECT statement, you will have no performance problems accessing BSEG.
So the trick is in designing the SELECT statement. Because BSEG is a cluster table, the only key fields allowed are those of the primary key:
- BUKRS - Company Code
- BELNR - Accounting Document Number
- GJAHR - Fiscal Year
- BUZEI - Line Item Number
But do you need to know values for all of these fields in order to maximize performance? The short answer is "yes" if performance is truly to be "maximized". But in normal circumstances, you may not have values for all of these fields immediately at hand. There are two factors that you have to take into account:
The most important fields to fill are the leading primary key fields. If you know the document number, fiscal year and line item number but not the company code, a SELECT using only these fields will give very poor performance. Imagine trying to find an entry in a large dictionary when you know only the second through fourth letters of a word. You would have to scan the entire dictionary. It's the same sort of situation here.
The SELECT should return as little data as possible. If you know only the company code but not the other key fields, performance will likely still be very bad. I say "likely" because in your system you may have some company codes that have very few documents posted to them while others have many.
In my experience, I have found that when retrieving data from BSEG, it is necessary and sufficient to have the company code and document number. Anything more is icing that you can use if you have it.
But also in my experience, users don't usually ask for reports based on document numbers. They need to see data based on customers or cost centers or some other similar criteria. If you try to retrieve data from BSEG using the customer number only, performance will be about as bad is possible.
But many standard SAP programs retrieve accounting data based on these sorts of fields. What magic tricks do SAP programmers have at their disposal that we don't?
No magic, but there are some tricks that you can use. I've written a program that shows different ways that you can use to find a document number. In some cases, you need to know beforehand the company code (or controlling area or funds management area). But, reports usually have this as one of the selection criteria. If not, you can use the technique I have shown in of my other BLOGs.
Before I present the program, there are some caveats:
- Most importantly, this is not complete. I'm showing some techniques that I have learned, but there are many more. This is really just to get you started.
- I've used FOR ALL ENTRIES rather than JOINs. There is probably a small performance hit as I've shown in another blog, but I find that hit to be generally small and FOR ALL ENTRIES easier to read. Feel free to use JOINs instead.
- Configuration may be an issue. I don't think it should be, but it's possible some of these techniques will not work in all systems.
- On the other hand, configuration may work to your advantage. It's possible (at least in earlier versions) to configure FI documents to have the same number as for example, the corresponding billing document.
- Archiving may also be an issue. If you have archived financial data you may find that you have to read the archive rather than BKPF or BSEG.
- I have not done any actual SELECTS against BSEG in this program. I have simply handed control to transaction FB03 which does this for me (based on the primary key).
Anyway, here it is:
You may have noticed that the program is incomplete. There is only a stub where the logic to retrieve FI documents for a cheque. This is a challenge to you to figure out how to do this. (Hint - table PAYR may be useful). If you try this, please post your results in the comments section of this BLOG. In a week or so, I will post the code that I created to do this. I am assuming that we are using an accounts payable cheque as an example.