The performance of your program is largely determined by the efficiency of its database accesses. It is therefore worth analyzing your SQL statements closely. To help you to do this, you should use the Performance Trace ST05 and Runtime Analysis tools SE30. In particular, the SQL Trace in the Performance Trace shows you, which parts of Open SQL statements are processed where, and how long they take.
Keep the result small
This reduces both the amount of memory used in the database system and the network load when transferring data to the application server. To reduce the size of your result sets, use the WHERE and HAVING clauses.
use the WHERE and HAVING clauses correctly:
- There are no unwanted physical I/Os in the database
- No unwanted data is stored in the database cache
- The CPU usage of the database host is minimize
- Network load is reduced, by transferring only the data that is required by the application.
Minimize the amount of data transferred
Data is transferred between the database system and the application server in blocks. Each block is approximately up to 32 KB in size. Administration information is transported in the blocks as well as the data.
To accomplish this and minimize the network load:
- Restrict the number of lines, by using effective where clause, use of distinct when fetch gets more than one row, using select single.
- Restrict the number of columns, by specifying the exact columns instead of using select *
- Use of Aggregate functions like Sum, Average, Maximum and Minimum
- In an update clause using the correct where clause along with the set to update only the requisite columns.
Minimize the number of Data Transfers
In every Open SQL statement, data is transferred between the application server and the database system. Furthermore, the database system has to construct or reopen the appropriate administration data for each database access. You can therefore minimize the load on the network and the database system by minimizing the number of times you access the database.
- When you change data using INSERT, UPDATE, and DELETE, use internal tables instead of single entries.
- As a rule you should read a given set of data once only in your program, and using a single access. Avoid accessing the same data more than once.
- Avoid using nested select loops.
- Use Joins in the from clause where its efficient
- Use sub-queries using the WHERE and HAVING clause
- Using Internal tables
- Using a cursor to read data
Minimize the search Overhead
You minimize the size of the result set by using the WHERE and HAVING clauses. To increase the efficiency of these clauses, you should formulate them to fit with the database table indexes.
- Use Database indexes include columns in your WHERE clause.
- Formulate conditions to optimise use of the available database indexes
- Check for equality in the WHERE clause and link it with AND conditions.
- Use positive condition and check for equality and do not use not equals and NOT operator.
- Avoid using the OR expression, it disables the database optimiser to be used.
- Avoid checking for Null values.
- Avoid complex conditions.
Reduce the Database load
There is only one database server in your system. You should therefore aim to reduce the database load as much as possible. I would strongly recommend developers to read more about this from your ABAP documentation.
You can use the following methods:
- Buffer tables on application server
- Avoid reading data repeatedly
- Sort data in your ABAP program not using the ORDER clause
- Use Logical databases but exercise caution in choosing the right database, since the wrong hierarchy chosen can cause negative impact on performance.
Database Accesses
The majority of improvements that can be made in an ABAP program are in the area of database accesses. As mentioned earlier, every effort should be made to reduce the number of database accesses. Additionally, for those accesses that are required, every effort should be made to optimize them. For more information regarding SQL performance, refer to Efficient_SQL.pdf and ABAP10.pdf, located in the standards directory in the Toolbox.
See Also
ABAP Tricks and Tips
