Performance issue with data extraction

[former_member187488]

Performance issue during data extraction is a kind of frequently asked question by customers.

Recently I have worked on such an issue, and I'd like to document it down, to share some basic ways in our analysis of such issues.

Customer reported issue is: Data extraction for SNP planning area takes over 10 hours without ending.

1) BW or APO?

     The time consuming process (bottleneck process) during data extraction may lie in BW side or APO side.

     So first thing to know is whether the issue lies in APO side.

     This could be easily decide by comparing the run time of the data extraction in RSA3 and by BW infopackage run.

     For the same datasource and the same selection conditions, if the run time in RSA3 is much better than infopackage run, the performance issue is mainly at BW side, and BW-WHM-DST consultants will help to analyze the issue from BW side. If there's no significant difference between the run time of the two processes, then APO consultants will check it from APO side. If the datasource is for DP planning area, it goes to component SCM-APO-FCS-EXT; if the datasource is for SNP planning area, it goes to component SCM-APO-SNP-BF. There's some difference between extraction in DP and SNP.

     Things to notice here:

     - It's hard to compare a process with no end -- like reported by the customer.

       So it is suggested to test the extraction process with more conditions like restrictions on the characteristic values, or shorter time periods.

       It is also suggested to test both -- limitations on characteristic values and shorter time periods, which would be helpful for further analysis.

    - Set proper parameters in RSA3.

      RSA3 is a data extraction test for a certain datasource. No real extraction to infocube will happen.

      There're two parameters, among others, in this transaction:

        - Data Records / Calls

        - Display Extr. Calls

      The number of records extracted by the extraction test is up to the multiplies of the two parameters ( = Data Records / Calls * Display Extr. Calls).

      So in order to make sure it extracts the same number of records in RSA3 and inforpackage run, you should make the result of (Data Records / Calls * Display Extr. Calls) bigger than the number of records to be extracted.

      I myself would prefer setting them to some bigger values.

     

     - Make sure the conditions for the characteristics set here are the same as in the infopackage setting.

     Customer said it makes no big difference between RSA3 and infopackage run. So I check it at APO side.

2) ABAP or DB?

    The general way to check performance issue is taking ST12 trace. So I started the below steps.

    - I found the process could finish in around half an hour if I only extract data for one or several days. So I started RSA3 with limitation on calendar day.

    

    - Then in another session, I started transaction ST12, and trace work processes.

        

     - In the list of processes, find out the process for my data extraction in RSA3 (according to user id), and start the trace for it.

         

     - Some icons may be yellow at the beginning, but they'll become green after a while, if you push 'Refresh' button.

          

     - After some time (better longer than 30 minutes) or until the process ends, push 'End all traces and collect' button to end the trace.

     - Back to the first screen by the green arrow, and collected trace could be found at the lower right part of the screen.

       The round icon with red and while color in status column means the system is collecting the trace.

       Wait till the 'Status' column becomes to a green tick. (Need to refresh the screen to see the status change.) 

         

    - Make sure the selected line is the trace just taken, and press the 'ABAP Trace' button to display the trace result.

      At the top right part, it shows whether ABAP took much time, or DB took much time.

        

    - The overall analysis shows that DB took much time during the process.

     ----------------------------

      There's a component SV-BO, which is an specialized  area focusing on system performance. The consultants in this component are good at analyzing below issues:

         - Entire system performance problem

         - Database performance issue

         - Expensive SQL statements

      So we often ask SV-BO-SCM colleagues for help when DB is the bottleneck of the process.

      ----------------------------

      However in some situations, issue lies in ABAP side though most time is consumed at DB side in ST12 trace, and sometimes issue lies in DB side though most time is consumed at ABAP side in ST12 trace.

      To check further, I sort the column 'Net time in %' in a descending order.

       

    - Here I could see that, though selection of DB table /SAPAPO/PEGKEY is the time consuming time, but the number of calls is very big, which shows a suspecting incorrect call at ABAP side.

     So I navigate to the source code from ST12. Then I set a break point at the concerned coding and start RSA3 again to check the issue by debugging.

        

             

3) Are there any documented solution?

    The search terms I use is "performance" together with the concerned function modules or routines in the call stack, which could be found in the debugger when it stops at the break point.

    I found the below two notes:   

    - 1799541    Performance improvement in SNP extraction

    - 1791662    Extraction from Cube - performance improvement

    I hope the notes could help here. In case not, I have to perform further analysis :sad:

4) Other information

    - In this particular case, since table /SAPAPO/PEGKEY is concerned, I also checked the number of entries in this table in SE16, and I got a big number.

      So I also suggested customer to run report /SAPAPO/DM_PEGKEY_REORG to try to reduce the number of entries in this table.

    - There're also some general check points in transaction /SAPAPO/SDP_EXTR for data extraction performance issue.

       If you push 'Datasource Properties' button, you'll see the below settings

       1. No Extraction of Data Records without Key Fig Value

            ->This setting will reduce the number of data records to be extracted very much if you have many cells with zero/initial value.

       2. Parallel Processing  Profile

            ->This setting enables parallel processing during data extraction. But it seems it does not help much in SNP cases ... 

       If you push 'Change Datasource' button, you'll get a list of key figures and characteristics.  

       -> Check 'Hide field' column for the unwanted characteristics and key figures will also improve the extraction performance.

            Especially in case there's any navigational attributes in the list, removing them by checking 'Hide filed' column will improve the performance a lot.

    - Sometimes when we see the most time is consumed at ABAP, we can still find some expensive SQL statements in the ST12 trace.

      Use 'SQL summary' button to view the summarized information and 'Performance traces' button to view detailed information.