on 09-18-2008 2:43 PM
Hi guys ,
Both extractors have very very poor performance during extraction to BW (7.0 but using 3.x DS). RSA3 has the same issue. without selection it takes about 8min to retrieve 90 records.
Typical package size during load is 5000-8000. AFAIK this size is set by SAP and can't be changed.
I think the problem is the r/3 AFVC table which is about 1.500.000 records big. SQL trace shows that a query on this table runs the longest.
An index is created to meet these selections but the costs for this query shrinks only by a small amount.
The query that takes the longest:
SELECT
"OBJNR" , "VORNR" , "WERKS" , "WAERS" , "AUFPL" , "APLZL" , "PROJN" , "SUMNR" , "OWAER"
FROM
"AFVC"
WHERE
"MANDT" = :A0 AND "AUFPL" IN ( :A1 , :A2 , :A3 , :A4 , :A5 , :A6 , :A7 , :A8 , :A9 , :A10 , :A11
, :A12 , :A13 , :A14 , :A15 , :A16 , :A17 , :A18 , :A19 , :A20 , :A21 , :A22 , :A23 , :A24 , :A25
, :A26 , :A27 , :A28 , :A29 , :A30 , :A31 , :A32 , :A33 , :A34 , :A35 , :A36 , :A37 , :A38 , :A39
, :A40 , :A41 , :A42 , :A43 , :A44 , :A45 , :A46 , :A47 , :A48 , :A49 , :A50 , :A51 , :A52 , :A53
, :A54 , :A55 , :A56 , :A57 , :A58 , :A59 , :A60 , :A61 , :A62 , :A63 , :A64 , :A65 , :A66 , :A67
, :A68 , :A69 , :A70 , :A71 , :A72 , :A73 , :A74 , :A75 , :A76 , :A77 , :A78 , :A79 , :A80 , :A81
, :A82 , :A83 , :A84 , :A85 , :A86 , :A87 , :A88 , :A89 , :A90 , :A91 , :A92 , :A93 , :A94 , :A95
, :A96 , :A97 , :A98 , :A99 , :A100 ) AND "SUMNR" = :A101
SELECT STATEMENT ( Estimated Costs = 105 , Estimated #Rows = 6 )
5 3 INLIST ITERATOR
5 2 TABLE ACCESS BY INDEX ROWID AFVC
( Estim. Costs = 105 , Estim. #Rows = 6 )
Estim. CPU-Costs = 2.188.228 Estim. IO-Costs = 105
1 INDEX RANGE SCAN AFVC~Z01
( Estim. Costs = 101 , Estim. #Rows = 6 )
Search Columns: 3
Estim. CPU-Costs = 2.137.832 Estim. IO-Costs = 101
Access Predicates
Setting in table BWOM_SETTINGS are set to NOOBJPRESEL.
Does anyone has an idea what else I could try?
Edited by: Matthias Gamsjager on Sep 19, 2008 10:30 AM
Hello,
I was working on simillar cases. recommendation provided in SAP Note 821763, mostly resolve the issue. Please have a look into the following SAP Notes as well. Apply the notes if any of the this note is applicable in your case.
721476
413992
592366
448693
651382
365762
821763
410130
Test it in your test environment and let us know the result. If you are still facing performance issue with these loading process create OSS message in 'SV-BO-BI' for further analysis.
Hope this helps
Yours Sincerely
Dileep
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.