2007 Mar 07 6:05 PM
Hi ABAPers,
I have a problem with BSEG table performance.
When i run this select :
select bukrs belnr gjahr shkzg dmbtr hkont INTO TABLE itab_bseg_II from BSEG
where GJAHR EQ P_RYEAR
AND HKONT EQ X011Z-BILKT
and PRCTR IN S_PRCTR.
reduces report performans. How can i increase select perormance? Please help.
Best regards.
Munur EBCIOGLU
2007 Mar 07 6:06 PM
One option is to get your DBA's help & create an INDEX for the fields in your WHERE clause.
~Suresh
2007 Mar 07 6:13 PM
Hi Suresh,
BSEG is a Cluster Table. How can i index it?
Thank your advices.
Munur
2007 Mar 07 6:21 PM
Instead of using non-key fields on BSEG (which will result in poor performance), consider using one of the index tables to find your records.
There isn't any index table that would suit your described query, but using BSIS will allow you to get better access by HKONT. Still, in addition to go against BSIS, you may have to revise your SELECT criteria to get better performance.
2007 Mar 07 6:24 PM
Try to use any of these preexisting indexes...
BSAD Accounting: Secondary Index for Customers
BSAK Accounting: Secondary Index for Vendors (C
BSAS Accounting: Secondary Index for G/L Accoun
BSID Accounting: Secondary Index for Customers
BSIK Accounting: Secondary Index for Vendors
BSIM Secondary Index, Documents for Material
BSIS Accounting: Secondary Index for G/L Accoun
2007 Mar 07 7:13 PM
Hi,
BSEG is cluse table and top of that you are not using the full key ( BUKRS, BELNR, GJAHR, BUZEI ) to read BSEG, thats why you have performance problem.
Also, since BSEG is cluster table you can not create index on it. Instead i suggest you to use <b>BSIS ( Accounting: Secondary Index for G/L Accounts )</b>.
You have multiple options here:
1. If you can somehow get the company code also, use the BUKRS ,HKONT , GJAHR ( in this order only ) as a select condition. And then delete the entries where the profit center does not fall in seletion criteria. So you code would be
* select data from BSIS
SELECT bukrs belnr gjahr
shkzg dmbtr hkont FROM BSIS
INTO TABLE itab_bsis
WHERE bukrs in s_bukrs AND
hkont = x011z-bilkt AND
gjahr = p_ryear.
* delete record based on profit centers
DELETE itab_bsis WHERE NOT ( prctr in s_prctr ).
2. If you cannot get the company code but you want to get the data for all company codes then you should use following given code. Lets say the lowest company code you have is "1000' and highest company code number is '7000'.
data: l_min_bukrs like bkpf-bukrs value '1000',
l_max_bukrs like bkpf-bukrs value '7000'.
ranges: r_bukrs for bkpf-bukrs.
r_bukrs-sign = 'I'.
r_bukrs-option = 'BT'.
r_bukrs-low = l_min_bukrs.
r_bukrs-high = l_max_bukrs.
APPEND r_bukrs.
* select data from BSIS
SELECT bukrs belnr gjahr
shkzg dmbtr hkont FROM BSIS
INTO TABLE itab_bsis
WHERE bukrs in r_bukrs AND
hkont = x011z-bilkt AND
gjahr = p_ryear.
* delete record based on profit centers
DELETE itab_bsis WHERE NOT ( prctr in s_prctr ).
3. Create a index on table BSIS with fields HKONT, GJAHR and PRCTR and use following code.
SELECT bukrs belnr gjahr
shkzg dmbtr hkont FROM BSIS
INTO TABLE itab_bsis
WHERE hkont = x011z-bilkt AND
gjahr = p_ryear AND
prctr IN s_prctr.
Let me know if you have any questions.
Regards,
RS
2007 Sep 12 9:22 AM
Hello,
i read your reply. You preconize to access BSIS table instead of BSEG.
But i read previous reply on this thread. Those previous replies are saying : access to BSIS, BSIM, BSIK, BSID, BSAS, BSAK, BSAD instead of BSEG <b>not just BSIS</b>.
So, do you agree with the previous replies ?
2007 Sep 12 10:14 AM
Hi,
If you try to access BSEG without the full key it will anyway take very longer time.Here in your case you are accessing it with GJAHR, HKONT and PRCTR.
I will suggest you to go to BKPF first based on the Year ( GJAHR) and Company Code (BUKRS) and if possible try to give the Posting Period also ( MONAT). in that wau u ll be having specific record numbers ( BELNR) with you. With these header data u access BSEG.
But again - if number of records from BKPF are high - the performance will be slow.( off course better than ur existing logic).
The other options is to use logical database. You can use LDB <b>SDF</b> . The performance in this case will be good - but it has a limitation -
For the GL Accounts ( BSEG-HKONT) , if you have the Line item Management turned on for the GL Account master- then only it will work otherwise it will not. If you can refer to FBL3N transaction. Just check if shows the records based on ur criteria. if it does - you can go for the LDB.