03-08-2013 7:10 AM
Hey All,
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.
I need to make few reports with these tables. Please advise; is there anything i can do to reduce this reponse time.
04-08-2013 12:23 PM
04-08-2013 8:04 AM
Any one please...answer..or atleast suggest something...
04-08-2013 12:08 PM
Hi Arslan,
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.
- Harshad
04-08-2013 8:11 AM
Hi Arslan,
BSEG and BKPF are very heavy table.
Avoid the use of join and for all entries on them.
Just use simple select statement with where clause, this would be fater.
Regards,
Amit
04-08-2013 12:16 PM
Avoid the use of join
Fyi, you cannot use JOINs on BSEG since it is a cluster table!
Just use simple select statement with where clause, this would be fater.
Can you please enlighten us on this statement?
- Suhas
04-08-2013 12:23 PM
04-09-2013 6:51 AM
Thanks everyone,
@ 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:
04-09-2013 6:53 AM
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.
05-31-2013 5:38 PM
Hi Arslan,
Hope yuor issue has been resolved, if not then please post the exact Select statement.
Regards,
Ankit.
06-03-2013 5:09 AM
Hey Ankit,
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.
Arslan
06-03-2013 8:32 AM
Hi Arslan,
This Select Query is perfectly fine, It uses all the primary keys in their proper sequence so this will not cause performance issues. Please post other heavy queries.
BR,
Ankit.
06-03-2013 11:00 AM
Hi Ankit,
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:
TABLES: BKPF.
That's it !!
Regards,
Arslan
06-03-2013 1:04 PM
Hi Arslan,
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 -
http://scn.sap.com/thread/940285
which expalins difference between all kinds of data dictionary table in sap.
BR,
Ankit.
06-03-2013 2:01 PM
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.
Thomas
06-05-2013 7:08 AM
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.
Regards,
Arslan Qureshi
06-05-2013 9:05 AM
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.
BR,
Suhas
05-30-2013 6:40 AM