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