This wiki is a DUPLICATE entry. Please refer to wiki ABAP Performance and Tuning instead.
DO NOT MAINTAIN IT ANY MORE
It is quite common in ABAP development the exigency of having programs running as quick as possible, once in most of cases there is a huge data mass to process. Let's try to understand how we can have an effective work on that subject.
SAP R/3 servers work in three layers:
Database Server: responsible for accessing and updating the database.
Application Server: responsible for application processing (ABAP command interpretation).
Frontend/Presentation Server: responsible for graphic interface proccesing (R/3 system middleware and kernel).
Reaching a good performance rating means, in most cases, decreasing the communication between these three levels.
Practices to avoid
To have a good performance you should avoid (very important):
- Database operations
- Nested Select commands (use For All Entries or Join instead)
- Selects within a loop
- Select * (all fields) instead of Select fields needed
- GROUP BY and ORDER BY in SELECT statement (Use sort at internal table)
- Select (without up to 1 rows) and Exit, or Select count to check existence of a record (Use Select single)
- using fields in the WHERE clause which are not part of an index
- Omitting a key from primary key in the WHERE clause for Cluster Tables (use all keys)
- using complex Selects on buffered tables
- Executing a Select multiple times in the program
- updating a complete row, if only one of the fields is to be updated (use UPDATE+SET statement)
- using join statements whileas adequate standard views exist
- Issuing a SELECT Query with the Fields in the Field List not in the Order that they exist in Standard SAP Table
- SELECT ... INTO CORRESPONDING FIELDS OF TABLE (Fields are moved one by one)
- Internal Tables
- Nested Loops (use sorted tables and binary search)
- Sort internal_table instead of Sort internal_table by field1 field2
- MOVEs all fields between 2 work areas, instead move the whole work area
- Linear Search (use BINARY Search with READ TABLE statement)
- LOOP AT ITAB WHERE <> (USE:Loop..Endloop and use IF CONDITION inside loop).
- Move-Corresponding clause (due to field-by-field comparison);
- Move-Corresponding clause (due to field-by-field comparison);
- Use of WHERE clause wherever possible: Instead of going for a select quary without where clause,its always better to give where clause in it so as to retrieve data satisfying the condition.If we dont give where clause then it will retrieve all record of the database table and the we have to filter it at application server level. And in the process we will be using more costly database resource which should be used very judiciously and carefully. When some key is referred, the system will access directly that data provided a database index exists, becoming the query easier. To certify a secondary index is being used, it is possible to use the option "Explain SQL", in transaction ST05.
- Logical Database: Imagine a scenario where you have a logical database defined with four tables. Now you have a program which has to use only three of that tables. If the three first tables are declared on the tables clause, the database will automatically bring all the key fields for the fourth table. Due to that, sometimes it is better to declare all the tables and use a get command only to one of its key fields.
- Database Resources: When possible, use database resources such as sum, avg, min and max commands. This will reduce the traveling information volume, as well as internal processing effort.
- "OR" Operators: Several or operators can be replaced by one in operator: z1 = a1 and ( z2 = y1 or z2 = y2 or z2 = y3) could be replaced by z1 = a1 and z2 in ( y1, y2 , y3).
- Cluster Tables: This kind of table has a particular behavior when being queried with where conditions when the compared fields are not keys; as it has all the non-key fields compacted, comparing them means to have to unpack them, which means extra processing effort. So it is better to make the where comparison using only the key fields, and to delete the unwished ones in a separated delete command.
- Types: When not selecting all the fields of a database table, use TYPES to determine which fields to use. This will grant no unwished data will be selected, thus decreasing the communication flow from database to application layer. It follows a sample:
- Work Areas: For a better performance it is preferible using internal declared structures in your program named work areas, instead of declaring tables with header lines. This will allow you to use sometimes the same one-record structure for more than one internal table. Use work areas for inserting and updating records. It folows a simple code sample, as in complement to last item's code:
- Field-Symbols: Use field-symbols almost exactly the same way than work areas. Use them, however, to read-only record cases. Work areas should still be used for internal tables with a structure less than around 100 bytes. It follows a sample of how is worthy using this resource:
For more than one insertion, prefer using the syntax INSERT db_table FROM TABLE internal_table [ACCEPTING DUPLICATE KEYS]
FOR ALL ENTRIES IN itab clause
Remove duplicated records on the comparison table. They can consume a lot more from your processing.
Also, always make sure that the comparison table is NOT empty, otherwise it will read the whole table.
Always check if you have all the needed keys to perform the reading of an internal table. If read data will only be use for reading, use field-symbol, else choose using a work area. When using this command just with purpose of verifying a record's existence, do not forget using the TRANSPORTING NO FIELDS clause.
Always try to use READ with BINARY SEARCH, but make sure you've sorted the internal table by the key you are trying to READ with.
And in case, you are looking only for values of 2 fields(say f1, f2) from the internal table, use TRANSPORTING f1 f2.
If you are looking to READ with a Contains Pattern, for ex. you want to get the entry which starts for f1 with AB or AB* values, use the following technique:
SORT ITAB1 by f1.
READ ITAB1 with key f1+0(2) EQ AB BINARY SEARCH TRANSPORTING f1.--this illustrates all the tips suggested above w.r.t READ Statement.
Use MODIFY with TRANSPORTING clause, wherever applicable.
Related SAP notes
- Note 332856 - Reading buffered master data : Identical database accesses to master data within a transaction can be avoided by using buffered reading. SAP provides special Function Modules which store the result of the last database request in the memory. This value can be used within accessing the database again.
Solutions to performance issues
There are performance issues encountered in few cases. This page address most of the common performance issues.
1. ORA - 1555
2. SELECT * <table> causes performance issues.
a. Try modifying the query to include maximum keys in the WHERE clause
b. Create secondary indexes on the table. This does improve performance. After creating the secondary indexes, do make sure to update the statistics. If this is not done, the secondary indexes would be used in delete operation and would lead to performance issue during deletion.
c. Use open cursors
3. If you are dropping and re-creating the indexes for the table using the function module call DB_DROP_INDEX and DB_CREATE_INDEX. Re-think on the strategy here. When processing large amount of data and each the index is dropped and re-created would lead to performance issue. A coding improvement would improve the performance here. Error could be with a a message DBIF_RSQL_INVALID_CURSOR.
4. When doing parallel processing of a particular transaction and if there is going to be an update of table/file remember to
a. Lock the table/file during update
b. End of the update unlock the object.
When the locking is not done, then there would be multiple attempts to update the table/file. This would lead to short dump after long processing time.
5. Error message"DBIF_RSQL_INVALID_CURSOR" check out for which database it is occurring. If it is an update to a table write a code modification like below