SAP ABAP Performance Tuning/Enhancement Key Steps.


Basic steps to be looked into for optimized performance are:
  • Database Server workload must be optimized.
  • Application Server should be more fully utilized.
  • Reduce the network traffic (Like, Commands that retrieve data must be optimized).
  • Minimize use of global resources. (Like, Spool usage should be minimum).


Here's a list of golden rules from performance optimization point of view:

1. Use Parallel Cursor technique in case of Nested Loop. Binary Search is used when parallel cursor is not feasible and the number of entries in the internal table is greater than 20.

2. Before using Select for all entries we need to check whether Driver internal table is empty or not.

3. After using one Select statement check sy-subrc value. If sy-subrc is 0, then only use next select statement.

4. During using Select for all entries use all the Primary key fields in the Select statement in WHERE clause in order to retrieve all records and to remove adjacent duplicates.

5. For selecting single row from a database table, use “SELECT SINGLE” instead of “SELECT *” if the full primary key is known. Otherwise, use “UP to 1 Rows”.

6. The sequence of fields selected in the Select query should be in the same sequential order as per the fields situating in the corresponding database table.

7. Avoid nested select statements. Use “Inner Join” and/or “For all Entries” / View instead.
  • When creating joins over database tables there should be an index at least on the inner table for the fields in the join condition. Else go for For-all-entries.
  • You cannot make an Inner Join good or bad by the order in which the tables are specified in the FROM and JOIN clause. The database optimizer ignores your order and ensures that the data is accessed in the most efficient manner. 
  • If Outer Joins are coded in the wrong order can cause problems for the Phoenix system AND give you the wrong result. 

8. Do not use select statements inside loop.

9. Do not use SORT statements inside loop. Sorting must be done outside loop.
  • Always SORT in ASCENDING order, if you SORT in descending order then the “BINARY SEARCH” addition will not work properly.
  • If field names &/or sequence got changed in the corresponding SORT prior to using “BINARY
  • SEARCH” addition in existing code, then again you need to SORT that specific internal table just outside the loop prior to using “BINARY SEARCH” addition in READ.
  • You can not use “BINARY SEARCH” addition in READ for SORTED and HASHED table. There use “WITH TABLE KEY” addition in READ statement where exactly the full key is provided.
  • Always check sy-subrc values after using “BINARY SEARCH” addition in READ. 

10.
  • Use all key fields in where conditions during using SELECT QUERY.
  • In SELECT statement, only the fields which are needed are selected in the order that they reside on the database, avoid usage of “SELECT *”.
  • Use “Select into table” rather than “Select into corresponding fields”.

11. Suppose, I have a huge table containing many records. During running I am getting Short Dump telling records out of page area or Time Out error. BIF_RSQL_INVALID_REQUEST. How to solve this performance issue?

We need to use Package size in the SELECT query. Using Package size we can restrict the data flow so that at a particular time some certain number of records can be retrieved and so on. For efficiency, always do the inserts in key sequence order.

The package size you should use depends on the structure size of the table you are inserting and the size of the rollback area. Your DBAs should be able to help you in this.

You cannot simply restart this program if it fails. You either have to empty the ztable first or skip the records you have already inserted.

NOTE: 
  • Internal tables are freed when no longer needed (either by the free command or a subroutine exit), especially when the tables are large or the program is a batch program.If Internal table is used again, then don’t use FREE i_xxx. Instead here use CLEAR i_xxx. *) i_xxx has no header line.
  • Use LOOP…WHERE is used instead of LOOP/CHECK.
  • MOVE-CORRESPONDING is used only when two internal tables have identical fields and you only want to move a few records from one to the other. Otherwise, MOVE should be used.
  • SORT ITAB BY f1 f2 is preferred over SORT ITAB.
  • Unwanted / redundant internal tables and global variables to be removed . Global internal tables / data variables should also be freed after use.
  • Header line of Internal Table is cleared before moving data into it.

12. Select into table is used instead of select append and then endselect.

13. Check whether the tables used or accessed require indices. Table access sequence optimized with buffer internal tables or table access sequence structure.

14. Filter checks should be provided early in the code.

15. Avoid SELECT…ORDER BY. Preferred to put the records into an internal table and then perform a SORT.

16. Fields specified in the WHERE condition with the critical operators NOT and <> (negative SQL statements) cannot be used for a search using database indexes. Whenever possible formulate SQL statements positively.

17. Make sure the first field (s) of the WHERE clause are stated with 'EQ'.

18. Do not define Internal Table with “OCCURS” option. Do not use Table with Header Line. Use work area or Field-Symbols to transfer data from/to internal tables.
  • Working with field-symbols increases the performance by 30%-50% when processing internal tables.
  • Working with field-symbols increases the performance when updating internal tables. Also no modify is required.  

19. LOOP AT ITAB WHERE K = ‘XXX’ should be used instead of LOOP AT ITAB / CHECK ITAB-K = ‘XXX’.

20. CASE statement preferred to multiple ELSEIFs.

21. When using the AND or OR operator, the most likely elimination criteria is specified first.

22. When COLLECT semantics are needed, the COLLECT command is used.

23. For internal tables:
  • Unnecessary sorting should be removed.
  • Read table should be used with binary search.
  • Should be cleared/refreshed wherever necessary.
  • Use of import and export should be minimal and memory ids used should be unique.
  • Memory freed after use.

24. Avoid CHECK within user-exits
CHECK sy-subrc = 0. --> Avoid
IF sy-subrc = 0. --> Better
PERFORM XXX.
ENDIF.

25. Check return code after inserts, updates, delete and modify. Avoid implicit commit.

UPDATE sflight
SET seatsocc = seatsocc + 1
WHERE carrid = c_lufthansa.
   IF sy-subrc = 0.
      COMMIT WORK.
      MESSAGE s009(bc_bor). * Change successful
   ELSE.
     ROLLBACK WORK.
     MESSAGE i008(bc_bor). * Error when changing
   ENDIF.




Comments

Also Read...