Skip to Content
Technical Articles

Turbocharging SAP Queries & Standard ABAP Reports

Introduction

Many SAP queries and ABAP reports do not perform well when retrieving large volume of data from traditional databases using complex SELECT – JOIN SQL statements often on 10 or more tables containing millions of records and sometimes even hundreds of million records.

If these ABAP reports are standard SAP programs or ABAP programs generated by SAP in applications like, e.g., SAP SQ01 Query, they should not be modified by programmers. However, using them is frustrating because of poor performance and timeouts they cause for broad selection screen criteria.

There are options in the developers’ hands to make them working faster – sometimes an order of magnitude faster if not more.

This blog discusses a high-level approach on how to turbocharge/optimize for performance these programs making them more enjoyable, i.e., working lightning fast, for SAP customers.

 

Small is Beautiful – 10 * 3,000 Is Less Than 1 * 30,000

When executing a complex SELECT – JOIN SQL statement, it usually works inefficiently for broad WHERE clause selection criteria that makes it retrieve thousands of records from multiple database tables. In many cases you might get results faster when executing SELECT – JOIN  SQL statement multiple times for a subset of WHERE clause selection criteria and each time retrieving a smaller number of records. The result would be the same, but the execution time could be shorter.

Additionally, when calling SELECT – JOIN SQL statement multiple times each time for a unique subset of the selection screen criteria, parallel processing techniques could be implemented to get results even faster. The difference in execution time could be even an order of magnitude if not more.

For example, executing SELECT – JOIN SQL statement on 20+ database tables retrieving 30,000 records could take 1,000 seconds to run. Calling the SELECT – JOIN SQL statement 10 times in sequence on 10 subsets of the selection screen criteria could take 700 seconds. Calling it in parallel in 10 parallel processes, could take 100 seconds or less – an order of magnitude improvement in performance.

How to do it in Standard ABAP Program

When a poorly performing program is a standard ABAP report or one generated by a standard SAP Application, e.g., SAP SQ01 Query, it is not advisable to modify it nor create a copy of it and modify a copy.

It might be a good idea to check in ST05 transaction that a proper indexes are used when retrieving data. If proper indexes are not available, you might consider creating secondary index(es) to improve program performance.

If this does not help, the other options are adding some custom ABAP code in User Exits, BAdIs or Explicit Enhancements Options. Since these hooks to standard SAP functionality are explicitly predefined by SAP, you might be out of luck and not able to find one in the program area where you need to add your ABAP code to make the program working faster.

The other option is to use Implicit Enhancement Points that are available at the beginning and at the end of ABAP Forms, Function Modules and few other places in standard ABAP programs.

Typical Structure of ABAP Report

Typical structure of ABAP report program consists of 3 sections:

  • Getting data selection parameters on the program initial screen to choose records from database tables and views
  • Retrieving data from database tables and views for the selection parameters
  • Displaying results, i.e., data retrieved from database tables and views

The above sections of the ABAP report could be quite simple or extremely complex containing multiple SELECT – JOIN SQL statements and plenty of conditional ABAP logic. Nevertheless, the structure of the report remains the same; i.e., getting data selection parameters, retrieving data from database and displaying results.

In case of interactive reports, the user has the further options to drill down and select additional data for specific value from the originally retrieved data.

The following sections of this blog show how to embed the implicit enhancements into the typical structure of standard ABAP programs to make it running faster.

From the end user point of view, nothing will change except significant performance improvements. When before, after clicking on F8 – Execute button, there was plenty of time to take lunch or coffee break, now with the optimized version of the report, it could be hardly any time to do brief starching.

The user would run the same program with the same selection screen and results will be presented as before in standard text-based output or ALV Grid.

Pseudo-Code of Long Running ABAP Program

Let us assume, that we have a long running ABAP Program as shown in the following table:

REPORT rxyz_long_running_report.

SELECT-OPTIONS:
  so_f01 FOR …,
  so_f02 FOR …,
  …,
  so_f20 FOR ….
…

START-OF-SELECTION.
  PERFORM data_get.

END-OF-SELECTION.
  PERFORM results_display.

The DATA_GET subroutine implements a complex SELECT – JOIN statement that retrieves thousands of records from multiple database tables and views containing millions of records.

The ABAP Pseudo-Code of DATA_GET subroutine might look as shown in the following table:

FORM data_get.
  …
*-PSEUDO-CODE: Slow Running SELECT Statement---------------------------
  SELECT f01 f02 f03 f04 f05 f06     f99
    INTO TABLE gt_output_tab
    FROM vtab1
         INNER JOIN vtab2 ON vtab1-f01 = vtab2-f02
         …
         INNER JOIN vtab8 ON vtab6-f11 = vtab8-f24
   WHERE vtab1-f01 IN so_f01
     AND vtab1-f02 IN so_f02
     …
     AND vtab6-f04 IN so_f15.
ENDFORM.   

The ABAP Pseudo-Code of RESULTS_DISPLAY subroutine might look as shown in the following table:

FORM results_display. 
  DATA:
    lo_table TYPE REF TO cl_salv_table.

  cl_salv_table=>factory( IMPORTING r_salv_table = lo_table 
                          CHANGING  t_table      = gt_output_tab ).
  lo_table->display( ).  
ENDFORM.

How to Make it Faster with Implicit Enhancement Points

Let us try to implement Implicit Enhancement Points that would allow us to execute the above program multiple times in parallel, each time for a subset of selection screen criteria. Once all parallel processes are completed, we could concatenate results and display them to the end user. From the end user point of view, nothing would change, except that program would run significantly faster.

Additionally, we would like to be able to run the original version of the program and one with enhancement and compare results to make sure that our implementation did not cause any problem and that the results are exactly the same.

We could implement 2 enhancements – one at the beginning of DATA_GET subroutine and another one at the beginning of RESULTS_DISPLAY subroutine.

DATA_GET Subroutine with ENHANCEMENT ABAP Pseudo-Code

The ABAP Pseudo-Code for the enhancement at the beginning of DATA_GET subroutine might look as follows:

FORM data_get.
ENHANCEMENT….
  CALL FUNCTION 'ZXYZ_GET_RUN_TYPE'
    IMPORTING
      xc_run_type = gc_run_type.
  IF gc_run_type = 'CUSTOM'.
    IMPORT run_mode = gc_run_mode FROM MEMORY ID sy-uname.
    IF gc_run_mode IS INITIAL.
      gc_run_mode = 'PARENT'.
*-----Call FM to split input & call this program for data subsets--------
      CALL FUNCTION 'ZXYZ_RUN'
        TABLES
          xt_output_tab = gt_output_tab.
*-----Export Output Table for display in RESULTS_DISPLAY subroutine------
      EXPORT gt_output_tab …
*-----EXIT, do not execute SELECT statement------------------------------
      EXIT.
    ENDIF.
  ENDIF.
ENDENHANCEMENT.
  …
*-PSEUDO-CODE: Slow Running SELECT Statement---------------------------
  SELECT f01 f02 f03 f04 f05 f06     f99
    INTO TABLE gt_output_tab
    FROM vtab1
         INNER JOIN vtab2 ON vtab1-f01 = vtab2-f02
         …
         INNER JOIN vtab8 ON vtab6-f11 = vtab8-f24
   WHERE vtab1-f01 IN so_f01
     AND vtab1-f02 IN so_f02
     …
     AND vtab6-f04 IN so_f15.
ENDFORM.   

The above enhancement will:

  • Check the run type for the program. If it is not a CUSTOM run type, the enhancement is ignored, and the original program is executed.
  • If it is a CUSTOM run type, it would check the run mode. 2 run modes are possible, i.e., PARENT when the user executes it and CHILD when it is executed in parallel process from custom ABAP code in ZXYZ_RUN function module. The PARENT run mode is set in enhancement if the IMPORT statement returns no value.
  • If it is PARENT run mode, a function module ZXYZ_RUN is executed followed by the EXIT statement to end START-OF-SELECTION event without executing SELECT – JOIN SQL statement

ZXYZ_RUN Function Module ABAP Pseudo-Code

The ZXYZ_RUN function module ABAP Pseudo-Code might look as shown in the following table:

FUNCTION zxyz_run.
*"  TABLES
*"      XT_OUTPUT_TAB STRUCTURE …
*-Get list of parameters-----------------------------------------------
  CALL FUNCTION 'RS_REFRESH_FROM_SELECTOPTIONS'
    EXPORTING
      curr_report     = 'rxyz_long_running_report'
    TABLES
      selection_table = lt_selparam_all[].
  …
*-Execute program in parallel for subset of selection screen parameters
  DO.
    …
*---Get subset of selection screen parameters--------------------------
    CALL FUNCTION 'ZXYZ_SELPARAM_SUBSET_GET'
      TABLES
        sel_param_all    = lt_selparam_all[]
        sel_param_subset = lt_selparam_subset[].
    …
*---Start parallel process with subset of selection screen parameters--
    CALL FUNCTION 'ZXYZ_RUN_FOR_SUBSET'
      STARTING NEW TASK lc_run_id
      DESTINATION IN GROUP 'parallel_generators'
      PERFORMING zxyz_process_completed ON END OF TASK
      EXPORTING
        ut_parameters         = lt_selparam_subset
      TABLES
        xt_output              = xt_output
      EXCEPTIONS
        system_failure        = 1
        communication_failure = 2
        resource_failure      = 3.
    …
    IF gc_all_done IS NOT INITIAL. EXIT. ENDIF.
        …
  ENDDO.
  …
*-Copy GLOBAL results table to Output Paramater Table------------------
  xt_output_tab[] = gt_output_tab[].
ENDFUNCTION.

FORM zxyz_process_completed USING uc_run_id.
  REFRESH lt_output_this.
  RECEIVE RESULTS FROM FUNCTION 'ZXYZ_RUN_FOR_SUBSET'
        TABLES
          xt_output     = lt_output_this.
  APPEND LINES OF lt_output_this TO gt_output_tab.
ENDFORM.                   

First the calling program selection screen parameters are retrieved. Then in the DO-ENDDO loop until all selection screen subsets are retrieved, the ZXYZ_RUN_FOR_SUBSET function module is called in NEW TASK as ASYNC RFC. The subset of selection screen parameters is passed to it.

ZXYZ_RUN_FOR_SUBSET Function Module ABAP Pseudo-Code

Inside the ZXYZ_RUN_FOR_SUBSET function module, the original program is SUBMITTED with subsets of the selection screen parameters as shown in the following ABAP Pseudo-Code:

FUNCTION zxyz_run_for_subset.
*"  IMPORTING
*"      UT_PARAMETERS TYPE rsparams_tt.
*"  TABLES
*"      XT_OUTPUT_TAB STRUCTURE …
*-Set RUN MODE to CHILD--------------------------------------------------
  EXPORT run_mode = 'CHILD' TO MEMORY ID sy-uname.
*-Submit the original program--------------------------------------------
  SUBMIT rxyz_long_running_report 
    WITH SELECTION-TABLE ut_parameters
    AND RETURN.
*-Import results table from original program-----------------------------
  IMPORT gt_output_tab = xt_output_tab …
ENDFUNCTION.

Note that the run mode is set to CHILD so that the original ABAP program could process it accordingly.

The enhancement in DATA_GET in the original program does nothing if run mode is set to CHILD. It allows execution of the SELECT – JOIN SQL statement for subset of selection screen parameters as shown again below:

FORM data_get.
ENHANCEMENT….
  CALL FUNCTION 'ZXYZ_GET_RUN_TYPE'
    IMPORTING
      xc_run_type = gc_run_type.
  IF gc_run_type = 'CUSTOM'.
    IMPORT run_mode = gc_run_mode FROM MEMORY ID sy-uname.
    IF gc_run_mode IS INITIAL.
      gc_run_mode = 'PARENT'.
*-----Call FM to split input & call this program for data subsets--------
      CALL FUNCTION 'ZXYZ_RUN'
        TABLES
          xt_output_tab = gt_output_tab.
*-----Export Output Table for display in RESULTS_DISPLAY subroutine------
      EXPORT gt_output_tab …
*-----EXIT, do not execute SELECT statement------------------------------
      EXIT.
    ENDIF.
  ENDIF.
ENDENHANCEMENT.
  …
*-PSEUDO-CODE: Slow Running SELECT Statement---------------------------
  SELECT f01 f02 f03 f04 f05 f06     f99
    INTO TABLE gt_output_tab
    FROM vtab1
         INNER JOIN vtab2 ON vtab1-f01 = vtab2-f02
         …
         INNER JOIN vtab8 ON vtab6-f11 = vtab8-f24
   WHERE vtab1-f01 IN so_f01
     AND vtab1-f02 IN so_f02
     …
     AND vtab6-f04 IN so_f15.
ENDFORM.   

Note that the call to the ZXYZ_RUN function module that starts all parallel processes, waits until they are completed. Then, it returns the output table that contains concatenated results from all processes.

This table is exported to be picked up in RESULTS_DISPLAY subroutine implicit enhancement point and the EXIT statement prevents the execution of the SELECT – JOIN SQL statement. We already have results coming from the ZXYZ_RUN function module call.

RESULTS_DISPLAY Subroutine with ENHANCEMENT ABAP Pseudo-Code

Let us now look at the enhancement ABAP Pseudo-Code in RESULTS_DISPLAY subroutine shown in the following table:

FORM results_display. 
ENHANCEMENT.
  CALL FUNCTION 'ZXYZ_GET_RUN_TYPE'
    IMPORTING
      xc_run_type = gc_run_type.
  IF gc_run_type = 'CUSTOM'.
    IMPORT run_mode = gc_run_mode FROM MEMORY ID sy-uname.
*-We are in PARENT mode – bring output table from DATA_GET Enhancement--
    IF gc_run_mode = 'PARENT'.
      IMPORT gt_output_tab …
*-We are in CHILD Mode – do not display output. Export it to calling FM-
    ELSEIF gc_run_mode = 'CHILD'.
      EXPORT gt_output_tab …
      EXIT.
    ENDIF.
  ENDIF.
ENDENHANCEMENT.
  …  
  DATA:
    lo_table TYPE REF TO cl_salv_table.

  cl_salv_table=>factory( IMPORTING r_salv_table = lo_table 
                          CHANGING  t_table      = gt_output_tab ).
  lo_table->display( ).  
ENDFORM.

For the PARENT run mode, the output table is imported from the implicit enhancement point in DATA_GET subroutine and it is displayed with ALV Grid factory method call.

For the CHILD run mode, the output table is exported to calling ASYNC RFC call. It is further passed for collection of all output tables returned by ASYNC RFC calls that are concatenated and the complete output table is returned in DATA_GET enhancement by call to ZXYZ_RUN function module.

This closes the loop and rather than running ABAP Report once for a large selection screen parameter set, the implicit enhancement point in DATA_GET subroutine in the original ABAP code calls ZXYZ_RUN function module that dissects selection screen parameters to multiple subsets and calls the original program many times in ASYNC mode collecting subset of results to the final output table in receiving ABAP subroutine executed on END OF TASK event.

Using this technique, you could often improve performance of standard ABAP reports by order of magnitude.

Normalizing Selection Screen Subsets

To make this approach optimal, it is important to split selection screen criteria, the way that each parallel call will produce a similar number of output records and this could be a tricky proposition. We do not want one parallel execution to return 100 records while another will return 20,000 records. This would caus ethe first process execute very fast and the second one very slow.

If input contains a long list of documents, it might be easy to do it, e.g., splitting list of 10,000 documents in the selection screen to 10 runs each with 1,000 documents.

If there is no obvious division into normalized subsets of input parameters, you would have to implement some pre-processing logic to be able to normalize input parameters the way that input subsets would produce similar number of output records.

For instance, if date range parameters are used, you might be able to evaluate how to split the date range to produce similar number of output records. Splitting one year date range into 12 month periods might not produce balanced outputs. One month might produce 2% of output records and another one might produce 40% of output records and performance will not improve dramatically. To get similar number of output records in all parallel runs, you could apply binary search logic to approximate date ranges yielding similar number of output records and use them when starting parallel processes.

Verifying Results

Even so, the same SELECT – JOIN SQL statements are executed, it is important to verify results between standard and custom runs. You could build a workbench that would collect input and output information as well as execution time so you could verify not only that results match but also get performance gain as shown in STD/CUST column below.

A sample Workbench Screen shows the results for Standard and Custom run of SAP generated ABAP program for user defined SAP Query:

The custom run as shown above was over 85 times faster than the standard run; i.e., 4 seconds versus 342 seconds – each one producing 1,621 output records.

Clicking on hotspot Compare Output icon, could allow comparing output from 2 different runs to the cell level:

What if It Does Not Work Faster

The solution presented in the preceding sections of this blog, describes how to improve the performance of standard SAP programs preserving, its selection screen, data retrieval logic and results display. For the user, nothing changes except getting results faster. If you upgrade to a new SAP version, your enhancement-based solution should continue working.

Sometimes, you might find that this approach does not improve performance and SELECT – JOIN logic continues causing performance problems.

Then you could implement SELECT – JOIN logic on your own in ZXYZ_RUN_FOR_SUBSET function module, rather than executing SUBMIT call to the original program (commented below), as shown in the following ABAP Pseudo-Code:

FUNCTION zxyz_run_for_subset.
*"  IMPORTING
*"      UT_PARAMETERS TYPE rsparams_tt.
*"  TABLES
*"      XT_OUTPUT_TAB STRUCTURE …
*-Set RUN MODE to CHILD--------------------------------------------------
  EXPORT run_mode = 'CHILD' TO MEMORY ID sy-uname.
*-Submit the original program--------------------------------------------
*  SUBMIT rxyz_long_running_report 
*    WITH SELECTION-TABLE ut_parameters
*    AND RETURN.
*-PSEUDO-CODE: Your Own MUCH FASTER SELECT – JOIN Logic------------------
  SELECT f01 f02 f03 f04 f05 f06     f99
    INTO TABLE gt_output_tab
    FROM vtab1
         INNER JOIN vtab2 ON vtab1-f01 = vtab2-f02
         …
         INNER JOIN vtab8 ON vtab6-f11 = vtab8-f24
   WHERE vtab1-f01 IN so_f01
     AND vtab1-f02 IN so_f02
     …
     AND vtab6-f04 IN so_f15.
*-Import results table from original program-----------------------------
  IMPORT gt_output_tab = xt_output_tab …
ENDFUNCTION.

Feel free to divide SELECT – JOIN logic to several statements on a few tables each rather than execute one big SELECT – JOIN statement on 20 plus tables. This could improve dramatically the performance.

Beside change in the ZXYZ_RUN_FOR_SUBSET function module, everything else stays the same. The CHILD logic in enhancements will not be executed because there will be no SUBMIT calls to the original program.

In case of SAP upgrade, it is more likely that you would have to modify the above function module.

Both approaches use the original application’s selection screen, and output list. Both approaches partition selection screen parameters to subsets and execute data retrieval logic multiple times on subsets of parameters.

The first approach executes original data retrieval procedure, the second one implements its own data retrieval procedure.

With one of the above discussed approaches you could improve performance by order of magnitude if not more and in some cases even over 100 times.

10 Comments
You must be Logged on to comment or reply to a post.
  • Hi,
    Thanks for sharing your suggestion, but I have a few comments:

    1. These are quite heavy modifications done here.
      I highly recommend to avoid of implicit enhancement points. Basically, these are just “moderated” modifications with a license to kill.
    2. This approach handles only a simple scenario in which the main report includes a single selection from DB (in data_get). I’m not sure this is the most common scenario.
    3. Although I agree that parallel processing may improve the performance times of the report (but may affect the overall system performance, on the other hand), I’m not familiar with the claim that “In many cases you might get results faster when executing SELECT – JOIN  SQL statement multiple times for a subset of WHERE clause selection criteria and each time retrieving a smaller number of records. “. Do you have any reference for it?
    • Hi Shai:

      Thank you for your comments. Let me reply one point at a time:

      1. My point was to allow customer to use standard ABAP report or generated by SAP ABAP query as before, i.e., same program name/transaction, same selection screen, same output screen with the same results. The only difference is execution time that quite often is 10 or more times faster and sometimes even more than 100 times faster.

        The only way you could achieve it is with “hooks” to standard SAP ABAP code. Unfortunately, SMOD/CMOD user exits, BAdIs and Implicit Enhancement are sparse in ABAP code and usually not in program areas where you would need them to implement what I explained in the above blog.

      2. In the blog above, I simplified ABAP Pseudo-Code to a single SELECT – JOIN statement. But the same applies to complex DATA_GET logic that involves multiple SELECT statements, conditional ABAP logic and LOOPs. The trick is in creating multiple subsets of “normalized” selection screen parameters and executing original program in parallel as described in blog above.

        I acknowledge that sometimes original DATA_GET logic is so inefficient that it is better to write your own data retrieval ABAP code as I described in the last section of blog above. Even if you do this, the customer has benefits of using the original program with the original selection screen and get results as before but much faster.

        Customers do not like changes and the implementation described in blog above allows them to use system as before with benefits of running it significantly faster.

      1. Parallel processing will improve performance of programs significantly without having adverse effects on overall system performance if implemented carefully. You have to make sure that you limit maximum number of parallel processes and additionally that you maintain minimum number of idle processes. This way you will not bring the system to its knees and if resources permit you will improve performance significantly – quite often over 100 times. The beset way to control maximum number of parallel processes and minimum number of idle processes is through configuration parameters.

        I worked on many projects where I used over last 15 years this technique; i.e., the subsets of selection screen parameters and executing the original program multiple times in parallel either with ASYNC RFC calls or background jobs. On projects I implemented, running program multiple times for subsets of selection screen parameters in sequence, was faster than running it once for large datasets. Often when running once for large datasets, program was just timing out.

      I hope that it helps to explain my point of view.

      Best regards,

      Adam Baryla

      • Hi Adam,

        Thanks for your (very) detailed response.

         

        Regarding point 3,

        I agree with you about the improvement which can be achieved via parallel processing.

        My question was regarding the claim that an improvement can be achieved even in standard selection just by splitting the SQL queries into multiple ones without any parallelism (““I many cases you might get results faster when executing SELECT – JOIN  SQL statement multiple times for a subset of WHERE clause selection criteria and each time retrieving a smaller number of records. “).

         

        Best regards,

        Shai

        • Hi Shai:

          I have often experienced that having subset selection screen criteria that retrieves N times fewer output records runs more than N times faster. It is not the rule, but I have experienced it in more than 80% of cases. This applies to foreground runs that take a lot of time, i.e., at least minutes and of course to runs that time out, and without splitting them into multiple sequential or parallel runs you cannot get results.

          Sometimes when splitting long running program to multiple sequential runs, I was able to get results several times faster – I guess due to single run choking on large SELECT statement with long list of SELECT-OPTIONS IN values in WHERE clause, not to mentioned program failing if SELECT-OPTION list is too long.

          Of course, implementing it in parallel processes brings additional significant improvements in performance. It is not uncommon to get improvements in performance over 100 times with parallel processing.

          Best regards,

          Adam

  • Even three months ago if you had told me doing 12 selections on subsets of the selection criteria would be better than doing one big read I would not have believed you.

    Then a helpdesk call came to me that a (custom) report was timing out all the time and it was half year end and the sky was falling.

    The SELECT was on GLPCA and they were running it for one cost centre, one GL account, for a three year period.As far as I could see from ST05 the correct index was being used, but it still timed out all the time.

    As an experiment, I changed it do do a loop reading one month at a time. I was flabbergasted by the improvement, it was ten billion zillion times faster. I think that was because for one month all the queries are EQUALS as opposed to BETWEEN and that seemed to make a huge difference.

    BTW I also don’t advocate messing with standard SAP reports. If you write your own you have total control and because of the ALV they look the same as standard reports.

    Bear in mind that SAP has to cater for all industries and all countries so the standard reports will tend to bring up far more information than any given organization (unless they are a multinational conglomerate) would need.

    Cheersy Cheers

    Paul

    • Hi Paul:

      Thank you for confirming “SMALL IS BEAUTIFUL” section of my blog with your experiment on SELECT on GLPCA table one month at a time. I experience improvement in performance most of the time when I narrow selection criteria and run it in loop. But this is the first step to making your programs lightning fast. Implementing each loop in parallel process with safeguards on maximum number of parallel processes and minimum number of idle processes makes it often 100+ times faster.

      Regarding “… messing with standard SAP reports …”, it is trade-off. Customers often prefer to stick with standard SAP, i.e., the same program/transaction, same selection screen and the same output screen. On the developer side, copying the standard code is often questionable, especially if a lot of program logic is in large function groups with multiple function modules. Using CMOD/SMOD User Exits, BAdIs and Explicit Enhancement often is not an option because you cannot find one where you need to place your ABAP code.

      On the other hand, Implicit Enhancements are flexible enough to place ABAP code in program area you need. Over the years, I have been using Implicit Enhancement technique as the last resort solving customer problems that are not possible to resolve otherwise. And they work for many years surviving most system upgrades without modifications to my ABAP code there.

      Best regards,

      Adam

      • I quite agree with never copying (cloning) standard SAP reports.

        It’s a really bad thing to do for loads of reasons, and also for the very reason I said above – they (standard reports) tend to do far too much as they have to cater for all countries and all business lies in the world and to be honest they can never cater 100% to all of them.

        A standard SAP report might get all the columns in VBAP for example, just to try and cater for everybody, but nobody needs them all, and a custom report would just get the columns the customer actually needed. This makes a big difference in a HANA environment.

        If you have a really small change then an implicit enhancement is the way to go. However small changes have a habit of mutating into huge changes as in “give them an inch, they will ask for a mile”.

         

        • Hi Paul:

          Thank you for your comments.

          The solution I described is indeed a small change to standard ABAP report created by SAP or generated by SAP from SQ01 query.

          There are only 2 small incision points with few lines of ABAP code each in DATA_GET subroutine

          FORM data_get.
          ENHANCEMENT….
            CALL FUNCTION 'ZXYZ_GET_RUN_TYPE'
              IMPORTING
                xc_run_type = gc_run_type.
            IF gc_run_type = 'CUSTOM'.
              IMPORT run_mode = gc_run_mode FROM MEMORY ID sy-uname.
              IF gc_run_mode IS INITIAL.
                gc_run_mode = 'PARENT'.
          *-----Call FM to split input & call this program for data subsets--------
                CALL FUNCTION 'ZXYZ_RUN'
                  TABLES
                    xt_output_tab = gt_output_tab.
          *-----Export Output Table for display in RESULTS_DISPLAY subroutine------
                         EXPORT gt_output_tab …
          *-----EXIT, do not execute SELECT statement------------------------------
                EXIT.
              ENDIF.
            ENDIF.
          ENDENHANCEMENT.
            …
          ENDFORM.   
          

          and RESULTS_DISPLAY subroutine

          FORM results_display. 
          ENHANCEMENT.
            CALL FUNCTION 'ZXYZ_GET_RUN_TYPE'
              IMPORTING
                xc_run_type = gc_run_type.
            IF gc_run_type = 'CUSTOM'.
              IMPORT run_mode = gc_run_mode FROM MEMORY ID sy-uname.
          *-We are in PARENT mode – bring output table from DATA_GET Enhancement--
              IF gc_run_mode = 'PARENT'.
                IMPORT gt_output_tab …
          *-We are in CHILD Mode – do not display output. Export it to calling FM-
              ELSEIF gc_run_mode = 'CHILD'.
                EXPORT gt_output_tab …
                EXIT.
              ENDIF.
            ENDIF.
          ENDENHANCEMENT.
            …  
           
          ENDFORM.
          

          There are no changes to selection screen, data retrieval and output display logic. So, customers could use what they are accustom to.

          The whole point of this approach keep customer happy and improve performance of long running reports. This is done be redirecting control to a function module that calls the original report multiple times in parallel each time for subset of selection screen criteria. Nothing changes for customer except much better performance. It is just plumbing.

          The function module could be implemented as a generic function module for any Standard ABAP Report you wish to improve performance for, by passing to it the name of the original program to be called in parallel as well as the name of function module that would split selection screen criteria to subsets to be used in parallel calls.

          Those 2 Implicit Enhancements – altogether less than 30 lines of ABAP code, excluding comment lines, do the trick of unplugging data retrieval process.

          😉 It is like laparoscopic heart bypass surgery 😉 

          As blog title suggests, “Turbocharging SAP Queries & Standard ABAP Reports”, this approach is good for both SQ01 queries or any other 4GL entity that SAP generates ABAP report from as well as standard ABAP reports.

          Of course, before implementing it, you have to judge whether benefits outnumber drawbacks.

          Best regards,

          Adam

           

  • I agree completely with the previous comments, especially the points raised by Shai Sinai .

    If a standard report has bad performance then open an incident with SAP Support and let them deal with this. Also, as Paul correctly noted, a standard report might be reading way too much information and in some cases a custom report would be a more efficient solution.

    It’s nice to share your experience and specific use case but the problem is that in most cases the readers perceive any SCN blog as a great idea, practically something endorsed by SAP (because it’s posted on their website). And while this could be a valid solution to a specific problem in a specific system and database, I would hesitate to advertise such methods. Actually I’m a bit concerned by your comment that you’ve been applying this solution casually for years. It reminds me of the good old “when all you have is a hammer then every problem is a nail” (or what was that saying?) and makes me question if you might have overused this solution when there might have been a better alternative.

    Performance problems can be very peculiar and, in my opinion, require more holistic and sometimes unorthodox solutions. This could be just one of them but definitely not the Holy Grail (that simply does not exist). I hope whoever reads this blog does not forget to read the comments.

    Thank you.

    • Hi Jelena:

      Thank you for your comments.

      My blog was focused on ABAP programs generated from, e.g., SQ01 queries, and Standard ABAP reports where due to a large number of records in database tables joined in a complex SELECT statement, as well as broad selection criteria in WHERE clause, the performance is poor and sometimes even prevents use of programs due to timeouts.

      If a standard approach does not help, e.g., making sure that proper indexes are used to retrieve data, defining secondary indexes to improve performance, …; there is not much that can be done if you do not think out of box.

      Over the years I have also noticed that often executing a long running program for a small subset of selection screen criteria makes it work much faster than expected, e.g., when you limit number of records to be returned by 10 times, you might get results back 15, 20, 30 or more times faster. This was also confirmed by Paul Hardy in his comment above.

      With the above observation, you might implement solution where you would call program multiple times – each time for subset of selection screen criteria and you would collect results from all runs. Rather than customer doing it manually, the process could be automated and run in parallel as described in my blog. This technique could improve performance by order of magnitude if not more. The ABAP code added in 2 Implicit Enhancements to achieve this is less than 30 lines if you exclude comment lines and accompanying custom Function Module, called from enhancement, that implements parallel processing

      I do not apply this solution “casually” and only occasionally as the last resort – only on some projects usually no more than once.

      It is also important to notice that my parallel implementation has safeguards to make sure that it would not overload the system, i.e., parameters for maximum number of parallel processes and minimum number of idle processes with appropriate default values if parameters are not defined.

      Best regards,

      Adam