I am trying to create an ALV format report for SAP Query with doucment header and line item tables of SAP (BSEG) & (BKPF).
In order to join them, I have used table BSEG (to adopt few fields from there) and added new fields with coding to populate data from BKPF.
Query is populating data just fine. The issue is it's reponse time; it takes minutes to populate data.
As a result i performed a runtime analysis from SE30. I found out that database is taking enormous % of time (69%). Further, it was revealed that most of the time in the transaction is being taken by "Fetch BSEG" call.
BSEG is s cluster table able based on your company's Transaction volume, it can be very heavy table.
Your query needs to be tuned to reduce the database time. First, if possible, select the Document # (BELNR) & GJAHR from BKPF and with than information fetch the details from BSEG. Alternatively, there are other tables like BSIK/BSAK, BSID/BSAD BSIS/BSAS where you can find same information. But you have to check what information you have with you before fetching the data from these tables.
@ Suhas,I just checked the blog you referred, it was very helpful. Unfortunately, I lack ABAPing skills. I am SAP FI Functional.
@ everyone, for your understanding I am attaching a screenshot of how have I done things.
I have used single table BSEG for some fields from there (Like profit Center);
For fields from other tables, I have added new fields, example “ZCURR” (attached screenshot)
Then I have declared data from other tables in the info set, like this:
I am populating data from these tables into additional fields with simple select statements:
No. My issue hasn’t been resolved yet.
My Select Statements are like:
SELECT single BLART into ZBLART FROM BKPF
WHERE BUKRS EQ BSEG-BUKRS AND BELNR EQ BSEG-BELNR
AND GJAHR EQ BSEG-GJAHR.
There are about 5 additional fields with such select statements.
Any help will be greatly appreciated.
in SQ02, I am creating a Single Table infoset Query. I am unable to use BSEG & BKPF in a table join.
The Single table that I have selected is BSEG.
For fields from BKPF, I have created additional fields. For each additional field, I have used SELECT Statement (similar to the one above).
In query, I have declared data as:
That's it !!
So you are trying to use BSEG in Join statment. This is not possible as mentioned earlier by Suhas because it is a cluster table and field names etc in ABAP Dictionary and Database are different. You need to take an ABAPers help in replacing the Join with For All Entries. I do not know whether it is possible in Infosets or not beacuse i have not worked on infosets.
A nice read is -
which expalins difference between all kinds of data dictionary table in sap.
Since your query is based on table BSEG, and I bet your query's selection criteria does not leverage the BSEG primary key, you should go back and look at Harshad's recommendation above and find an alternative access path. Otherwise there is no way to speed this up.
We can maybe help if you describe the functional requirement behind the query, e.g. what sort of items are relevant (G/L, A/P, A/R), only open items or all, selection criteria etc.
Thanks Thomas & Suhas.
Thomas, You are correct. Selection Criteria is usually limited. For Example, data for a range of documents or for a range of GL Accounts.
My report is for Expense GL Account. I need document Header and Line Item details for All Items(open or closed) for specific range of Posting Dates which have been posted in that GL, including vendor Number.
I also need relevant details from vendor master record, but that is not an issue.
My report is for Expense GL Account. I need document Header and Line Item details for All Items(open or closed
If this is the case i think you can leverage the index tables for GL accounts - BSIS(open) & BSAS(closed/cleared).
But bear in mind that the setting "Line item display" must be turned on in the GL master for the records to be stored in the above mentioned tables.