Open SQL - Performance Notes

The performance of a program is often determined by the efficiency of its database reads. In a client/server environment, each database read places a load on both the database system and the connection between the database system and the application server. This load must be kept as low as possible if programs are to demonstrate a good level of performance.

Generally speaking, the following rules must be followed. The overall performance of a program is related to the data being edited, any evaluations required, and the database system itself, which means that a different combination of rules and priorities can apply from case to case.

• Keep the number of hits low

The set of rows selected should be kept as small as possible by using specific conditions to restrict the set to those rows actually needed. Superfluous rows should never be transported from the database system to the application server and then evaluated there.

• Keep the data volume low

The volume of data transported should always be restricted to the columns required. The columns can be specified explicitly or an appropriate view can be used. Further more, aggregate expressions can be combined with appropriately grouped data or SQL expressions to reduce the volume of data, since here the data is aggregated before the transport in the database system.

• Keep the number of reads low

To keep the number of database reads low, mass operations should always be used instead of single operations. More specifically, Open SQL statements should not be used within loops. Instead, joins, views, or subqueries can be used when reading multiple database tables.

• Use local buffers

The same data should be not be read more than once. Saving database tables to the local SAP buffer (and also saving prepared data in Shared Objects) can produce significant time savings, since it takes much longer for the application server to read the database system than a locally buffered table. Database tables should always be buffered if they are read frequently and modified rarely. If you want the same data to sorted multiple times in different orders, then the data should be sorted in the ABAP program.

• Efficient search using indexes

In all cases where secondary indexes of database tables improve selection performance, these indexes should be created and used.