Optimizing ABAP Performance: Best Practices and Parallel Cursors

 We have discussed the Message class and how to create a transaction code for a Executable program in our last blog.

Let’s discuss some best programming practices.

---

Best Performance Practices/Guidelines :-

• Never use * in the query. Fetch the data of only those columns which are required.
• Columns fetching sequence needs to be same as that of data dictionary column sequence.
• Where condition column sequence needs to be same as that of data dictionary column sequence.
• Never use corresponding in the query.
• For traditional databases avoid using JOIN, use FOR ALL ENTRIES IN.
• For fetching data from foreign key tables/dependent tables, Check for sy-subrc or internal table not initial condition.
• Use binary search in read table.
• Use parallel cursor in nested loops.
• For multiple conditions, use case conditional statement rather than if conditional statement.
• Depends upon the requirement, create secondary indexes to improve the performance.
• Never write a select query inside the loop.

---

Q.1 Why Should we not use * for fetching data ?

Ans :- Suppose, I got requirement to fetch 20 columns from the VBAK table and suppose VBAK table has 150 columns. Now, I am using * on the VBAK table to fetch data, Because of using *, I will bring the 150 columns on the application layer which is not being used and It will impact the overall performance of our program. So, It is always necessary that we should use the name of the columns to fetch the data instead of using *.

Q.2 Why Columns Fetching sequence needs to be same as that of data dictionary column sequence ?

Ans :- When we start fetching data column vise, It is relatively easy for the SAP system to fetch data from the database but if we don’t follow the column sequence then SAP system will have to move back and forth for fetching data of the required column, So it is always preferable that Column fetching sequence needs to be same as that of data dictionary sequence otherwise it will impact the performance.

Q.3 Why it is necessary to check sy-subrc or if not initial condition for fetching data from dependent tables ?

Ans :- Suppose, I have created two type structure for header and item table and Suppose for some entry there is no data in the header table and data is present in the item table and Customer wants to see corresponding item details for header, So if we will not check for sy-subrc or if not initial condition, then in that case We will have no data in the header but we will have data in item and overall it will impart the performance of our report. So, It is always necessary to check for sy-subrc or if not initial condition before fetching data from the dependent tables.

Q.4 Why we should not write a select query inside the loop ?

Ans :- Suppose, I have interna table which have 1 lakh items and suppose I am using a select query inside the loop. So, for each iteration of the loop we will see our Select Query getting executed and since Select query is also high cost task and it affects the database as it fetches a large amount of data. Therefore, Executing a select query 1 lakh times might lead to a position where our database will not be able to execute our program. So, we must not use Select query inside our loop.

---

Parallel Cursor :-

• It is one of the most important best practices that needs to be followed while using nested loops.
• No one will accept the code, if we are using the nested loops and not applying the concept of parallel cursor.

• Suppose, in the above image, we have lt_data is the internal table for header and lt_data1 as the internal table for item. and at last we will be using a final table to display our data.
• Now, If we are using loop on header and we ware trying to use Read on item then in that case we will be able to get only the first matching records from the item, So we will have to use nested loop to get the entire details.
• But again there is one problem, For ever data of the header table from the above image, the internal loop will execute for 8 times, however we can see that we don’t need to execute the internal loop 8 times for each header, we just need to iterate the internal loop for 3 times in order to get every item element.
• So, to achieve the above requirement, we will use the concept of Parallel Cursor.

Solution :-

• Step 1 :- Before going for the iteration over the second loop we will use a Read statement with binary search.

• Step 2 :- for every successful SY-SUBRC of the above read operation, assign a local variable with SY-TABIX, it will return the current iteration.

• Step 3 :- Use from Condition on the inner loop rather than using the where condition.

• Step 4 :- Also check the inner loop should execute for only those primary keys which are present in header also.

  
  
  

  
  

Code :-

**************************************************************************
*start of Program
*Declaring the type structure
TYPES: BEGIN OF ty_header,
         order_number	TYPE zar_order_number,
         order_date	  TYPE zar_order_date,
         payment_mode	TYPE zar_payment_mode,
         total_amount	TYPE zar_total_amount,
         currency	    TYPE zar_curency,
       END OF ty_header.

TYPES : BEGIN OF ty_item,
          order_number      TYPE zar_order_number,
          order_item_number	TYPE zar_order_item_number,
          item_cost	        TYPE zar_total_amount,
        END OF ty_item.
*Declaring the final type structure
TYPES: BEGIN OF ty_final,
         order_number	     TYPE zar_order_number,
         order_date	       TYPE zar_order_date,
         payment_mode	     TYPE zar_payment_mode,
         total_amount	     TYPE zar_total_amount,
         currency	         TYPE zar_curency,
         order_item_number TYPE zar_order_item_number,
         item_cost         TYPE zar_total_amount,
       END OF ty_final.
*********************************************************
*Declaring the internal table and work area
DATA : lt_header TYPE TABLE OF ty_header,
       ls_header TYPE ty_header,
       lt_item   TYPE TABLE OF ty_item,
       ls_item   TYPE ty_item,
       lt_final  TYPE TABLE OF ty_final,
       ls_final  TYPE ty_final.
DATA : lv_index TYPE i.
********************************************************
****************************************
*Defining Selection Screen
DATA : lv_order TYPE zar_order_number.
SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : s_order FOR lv_order.

SELECTION-SCREEN : END OF BLOCK b1.
*end of Selection Screen
***************************************************************

****************************************
*Writing Select queries
SELECT * FROM zar_header INTO CORRESPONDING FIELDS OF
  TABLE lt_header WHERE order_number IN s_order.
IF lt_header IS NOT INITIAL.
  SELECT * FROM zar_item INTO CORRESPONDING FIELDS OF
    TABLE lt_item FOR ALL ENTRIES IN lt_header
    WHERE order_number = lt_header-order_number.
*****************************************************
*Looping to store data in final table
  IF lt_item IS NOT INITIAL.
    SORT lt_item BY order_number.
    LOOP AT lt_header INTO ls_header.
*Performing Read operation
      READ TABLE lt_item INTO ls_item WITH KEY
      order_number = ls_header-order_number BINARY SEARCH.
      IF sy-subrc EQ 0.
        lv_index = sy-tabix.
      ENDIF.
      LOOP AT lt_item INTO ls_item FROM lv_index."WHERE order_number = ls_header-order_number.
        IF ls_header-order_number <> ls_item-order_number.
          EXIT.
        ELSE.
          ls_final-order_item_number = ls_item-order_item_number.
          ls_final-item_cost = ls_item-item_cost.
          ls_final-order_number = ls_header-order_number.
          ls_final-order_date = ls_header-order_date.
          ls_final-payment_mode = ls_header-payment_mode.
          ls_final-total_amount = ls_header-total_amount.
          ls_final-currency = ls_header-currency.
        ENDIF.
      ENDLOOP.
      APPEND ls_final TO lt_final.
      CLEAR : ls_final, ls_header, ls_item.
    ENDLOOP.
***End of Loop

*************************************************
*Displaying the output.
    LOOP AT lt_final INTO ls_final.
      WRITE :/ ls_final-order_number,
               ls_final-order_date,
               ls_final-payment_mode,
               ls_final-total_amount,
               ls_final-currency,
               ls_final-order_item_number,
               ls_final-item_cost.
    ENDLOOP.
  ENDIF.

ENDIF.
*end of Program
******************************************************************************

Output :-

---

So, that’s enough for today, we will continue with the Classical Report events in our next blog.

Thanx alot for being a part of this wonderful journey.