FAGLL03 performance issue

[Former Member]

Hi Experts,

We are facing performance issues with FAGLL03 when multiple profit centers are specified in dynamic selection

It is working fine for single profit center. The values for GL Account, Company Code, Posting date, Ledger and

Profit center (In dynamic selection) are provided in selection screen. On analysing the SQL trace, it is seen

that the select queries (join) on bsis and faglflexa are taking long time. The from and where part of the query

is given below.

FROM

"BSIS" T_00 INNER JOIN "FAGLFLEXA" T_01 ON T_01 . "RBUKRS" = T_00 . "BUKRS" AN

D T_01 . "BELNR" = T_00 . "BELNR" AND T_01 . "GJAHR" = T_00 . "GJAHR" AND T_01

. "BUZEI" = T_00 . "BUZEI" AND T_01 . "RCLNT" = T_00 . "MANDT"

WHERE

T_00 . "BUKRS" = :A0 AND T_00 . "HKONT" = :A1 AND T_00 . "XARCH" = :A2 AND

T_00 . "MANDT" = :A3 AND T_01 . "RACCT" = :A4 AND T_01 . "RLDNR" = :A5 AND (

T_01 . "BSTAT" = :A6 OR T_01 . "BSTAT" = :A7 ) AND T_01 . "BUDAT" BETWEEN :A8

AND :A9 AND ( T_01 . "PRCTR" = :A10 OR T_01 . "PRCTR" = :A11 OR T_01 .

"PRCTR" = :A12 ) AND T_01 . "RCLNT" = :A13

Please let me know if a secondary index would help. If so, is it required for BSIS and FAGLFLEXA? What fields are required to be put in the index? I tried creating one for BSIS with fields (BUKRS, HKONT, XARCH, MANDT) and one for FAGLFLEXA with fields (RACCT, RLDNR, BSTAT, BUDAT, PRCTR, RCLNT) and ran SE14 (Create index) for both, but still FAGLFLEXA shows that the index does not exist in database. On running FAGLFLEXA after creating these, these indexes were not being used.

Thanks,

Leena

[yuri_ziryukin]

Hello Leena,

if you decided to create an index for the FAGLFLEXA, please consider moving RCLNT as the first field.

The index for BSIS is not needed, as the access to BSIS will happen with the primary key when corresponding entries are selected from FAGLFLEXA.

Next, check how many entries from the FAGLFLEXA are selected for your criteria. Maybe you use profit centers where a lot of records are fetched from FAGLFLEXA. In that case even a new index probably will not improve the situation significantly.

Please note that index creation for very large tables make take some time. If you properly created it and triggered a creation of the DB index, it should work.

Yuri

[stefan_kolev4]

Hi,

In addition to mentioned by Yuri, please have in mind that if you create a secondary index on a table which / obviously FLAGFLEXA is big table / has a lot of records and is frequently updated you will face another performance issue. My advice is to use existing index or to try to split the selection if possible, try to put the data into internal table/s, try to use FOR ALL ENTRIES, but only if the driver table has no more than 500-800 records.

Cheers,

Stefan

[Pawan_Kesari]

Do you use SQL server as database?

I had similar issue last year and was able to resolve by updating index statistics on SQL server. This might be worth looking at before you go for anything else.

Regards,

Pawan.

[Former Member]

Hi Experts,

On further analysis, it was seen that the same query works fine in another system (ECM) which is a copy of Prod (where we have the issue). In this system, on analysing the SQL trace, we noticed that for the same variant, two different index are being used in both the systems. In ECM, FAGLFLEXA_{2 is used while in EFP (Prod) FAGLFLEXA}4 is used. Any idea, why both systems use different indices for the same variant? Will recreation of database statistics solve the issue, even though these indices are not newly created ones?

Thanks,

Leena

[Former Member]

Hi Leena,

if you have identical active indexes on both systems, the index statistics may be the reason for that. From which date are the index statistics for those indexes?

The database access (e.g. by Oracle Cost Basaed Optimizer or some matching tool for your database) may choose different indexes depending on the index statistics results. If these statistic values are too old or not good eneough (because of the wrong analysis method) there may be taken different indexes for database access, the right one on one and the wrong one on the other system.

Regards,

Klaus

[yuri_ziryukin]

Will recreation of database statistics solve the issue, even though these indices are not newly created ones?
> 
> Thanks,
> Leena

Maybe not, but it definitely worth trying.

[Former Member]

HI Leena,

If the test system is a recent copy or the production tables have not grown and the stats are updated it could be that the hardware cost differ if different CPU's, etc or Oracle settings not the same. Check Earlywatch reports to see if recommended changes on both are the same or check config manually. Try using Oracle hint to use the same index as in test that works fast in production and see if performance is same as in test. If Z code then all set, if SAP than can open message and SAP will often create a note or give option B.

Good Luck.

Allan Stone

SAP Solutions Architect

EMC Solutions Design Center

Personal information removed

Edited by: Rob Burbank on Nov 16, 2011 3:26 PM