Get Example source ABAP code based on a different SAP table
ARTICLE ABAP Code Snippet
Open SQL - Performance Notes
Keep the number of hits low
Wherever possible, all selection conditions should be included in the WHERE clause, using AND and checking for equality. Do not select a large dataset and then check it with CHECK. If all data is to be read, no WHERE condition needs to be specified.
Transfer small amounts of data
If only a few fields are used, use SELECT with a structure, not SELECT *. Alternatively, use one of the views in ABAP Dictionary to select data.
Use the aggregate functions rather than selecting data and grouping it independently. SAP buffering is switched off when aggregate functions are used.
When UPDATEs are used on a database record, only update those columns should be updated that were modified.
Use a small number of database accesses
If INSERT, UPDATE, or DELETE are used, sets of data should be used instead of individual table entries. This ensures that the index only has to be edited once, which relieves the load on the database.
Use nested SELECT loops only when the hit list in the outermost level is very small. There are various ways of avoiding nested SELECT loops: RESET N1 Building a JOIN in the FROM clause Joins as views defined in ABAP Dictionary. SELECT ... FOR ALL ENTRIES
In the outermost loop, the database table (PACKAGE SIZE) is read section-by-section into an internal table, sorted by its primary key (SORT on the internal table, or read in using ORDER BY PRIMARY KEY). For each data record in the internal table, all associated, dependent records are read into a further internal table (using SELECT ... FOR ALL ENTRIES). This is also sorted. Processing can then be continued using a nested LOOP. The advantage of SELECT ... FOR ALL ENTRIES is that it provides good performance regardless of the selectivity of the condition on the outermost table, since, in contrast to the nested SELECT, it works in a data-oriented way in the database, but still only picks out the relevant database entries (different to parallel cursor processing). The addition FOR ALL ENTRIES should be used if a JOIN is not possible for syntactical reasons or if a JOIN would result in high redundancy due to the constantly repeated fields from the left table. Explicit cursor handling (OPEN CURSOR [WITH HOLD]...)
In this processing type, a separate cursor is opened for each table involved. These are processed in parallel. In order for the system to recognize control breaks, the tables must be sorted (ORDER BY PRIMARY KEY) before being read. Parallel cursor processing should be used only if the outermost table is to be processed completely or to a large extent, since WHERE conditions for the outermost table cannot be passed on to other tables (in other words, more data might be read than is necessary). Warning: RANGES tables Explicit cursor handling should be used for large quantities of data and logical databases.
Search small quantities of data RESET N1 In WHERE conditions, EQ comparisons linked with AND as often as possible. This means that the system can use indexes in the search. NOT, OR and IN are not supported by indexes unless all of the fields in the SELECT clause and WHERE condition are also contained in the index.
Reduce the database load wherever possible RESET N1 Saving database in local buffers (see SAP buffering) can save considerable time in client-server environments, as the access time via the network is considerably higher than access time via a locally buffered table.
SAP buffering is switched off if the following is specified: SELECT FOR UPDATE or SELECT DISTINCT in the SELECT clause, BYPASSING BUFFER in the FROM clause , JOINs and subqueries (subqueries), ORDER BY f1 f2 ... in the ORDER-BY clause. aggregate functions in the SELECT clause. IS [NOT] NULL in the WHERE condition.
