Access Optimization in Queries 

You can get considerably improved performance from ABAP reports thanks to enhancements made in OPEN SQL. SAP Query exploits these enhancements in order to achieve an improved runtime for query reports.

One important enhancement is that it is now possible in all SELECT statements to specify a list of fields that should be read from the database table. If ‘*’ is used for the field list then all the fields are read. Since time-consuming conversions are often applied to fields when they are read from the database into program fields, this procedure is particularly ineffective if the database table contains several fields of which just a few are really needed in the program. This is in fact the usual case for query reports.

The GET statement has been enhanced, which also affects field lists in SELECT statements. It is also possible to specify here a list of fields that are required in a report. Only these fields are read from the logical database. The connection between the GET and SELECT statements is that in a database program the GET statement is ultimately implemented using the SELECT statement. However, it is possible to specify a field list in a GET statement only if the logical database being used supports these.

The term access optimization refers to the way that Query supports full usage of field lists in SELECT and GET statements. The procedure is as follows:

When an InfoSet is generated a list is created containing all the fields that are needed when the InfoSet is used in a query (the reference list). Additional tables and sections of code (additional fields, code that is called at GET, GET LATE and record processing) are particularly significant here, since other fields are accessed at these points. The purpose of the reference list is to ensure that the report generator of the query can determine all the fields that are implicitly required as a result of the use of an additional table or code from the directly needed fields contained in the list. Given this information, the report generator is then able to specify field lists for all SELECT and GET statements that are generated.

If an InfoSet has no additional fields and no sections of coding, the reference list can be created completely and without errors when the InfoSet is generated. Problems could arise if you use ABAP code. It is then possible that the InfoSet does not contain all the information needed for determining the required fields, making the reference list incomplete. This can mean that when query reports are processed some of the fields needed only ever get given their initial values.

If an InfoSet contains sections of code that could cause problems, a warning is output each time that it is generated. A check should then be made on whether any of the cases described below has occurred and the InfoSet must be corrected as appropriate.

It is necessary to know which fields are required for generating query reports in order to be able to draw up the reference list. These can be the following fields:

The freedom to use any ABAP language construct at any point in a section of code means that it is possible to access fields without explicitly referring to these fields (field symbols, external Perform, DO... VARYING, ADD... THEN... UNTIL, etc.). However, in order to ensure that query reports can be generated without errors, it is essential that all fields accessed can be determined in EVERY section of code (additional fields, GET / GET LATE / record processing). This means that every field used in every section of code has to be named explicitly. If a section of code contains ABAP statements that access certain fields implicitly, then the ABAP FIELDS statement must be used in the code to ensure that all database fields, table fields and additional fields that are used are named explicitly.

The KNC1 table contains the UM01U, UM02U and UM03U fields. These fields contain the monthly sales figures for the first three months of a year. An additional field, Q1, is to be used for the sales figure for the first quarter. This field calculates the sum of the other three fields with an external Perform.

PERFORM QUARTAL1(pppppppp) USING Q1.

The KNC1-UM01U, KNC1-UM02U and KNC1-UM03U fields are accessed via the common memory for the KNC1 table in the query report and in the program pppppppp that gets called. However, it is not evident from the code fragment above that these fields are actually required. Therefore this code fragment needs to be modified as follows:

PERFORM QUARTAL1(pppppppp) USING Q1.

FIELDS: KNC1-UM01U, KNC1-UM02U, KNC1-UM03U.

It is important to note that all fields required must be explicitly named in every section of code. This is because each section of code is included in the query report only if it is really used, and therefore it has to be clear from individual section of code which fields are needed in that particular section of code.

Note also that only those fields that are directly used in a section of code need to be named there.

The additional fields F1 and F2 are defined in the following code:

F1: F1 = TAB-FELD. "TAB-FELD is a database field

F2: F2 = F1 + 2.

Even though F2 indirectly accesses TAB-FELD, it is not necessary to list TAB-FELD in the code of F2 as being one of the fields used. This kind of indirect reference is automatically resolved when the InfoSet is generated. The sections of code for both additional fields is therefore correct exactly as given above.

In exceptional cases the following statement can be used in code:

FIELDS TAB.

where TAB is a database table or an additional table. This has the effect of using all the fields of the TAB table in the query report. However, this means that the access to the TAB table is no longer optimized and that therefore a considerable loss of performance can be expected when processing queries.

If the advice given here on maintaining an InfoSet is carefully followed, it will always be possible to generate a complete reference list. However, the generation process itself cannot check whether the InfoSet has been maintained correctly, and therefore it is always possible for a faulty case to "slip through the net", resulting in an incomplete reference list being generated. If a reference list is incomplete for a query report, this could lead to fields that are needed not being read from the database and therefore always retaining their initial values. Therefore there is a special test available for queries.

If a query returns incorrect results and the suspected cause is an incomplete reference list, then a query report can be generated without access optimization. This is done by calling the Extras ® Optimization on/off function on the Title, Format screen of the query maintenance component. After this function has been called the first time, a check box appears on the screen and is checked. This means that access optimization is no longer active for this query. All fields of all tables are then read. The access optimization can be reactivated either by calling the Extras ® Optimization on/off function again or by deselecting the check box.

The access optimization should be deactivated only for test purposes. If the query works correctly when the access optimization is deactivated then the InfoSet must be investigated and corrected so that a complete reference list gets generated (see above). If the query does not work correctly even with the access optimization deactivated then the problem is being caused by something else.

Following a test without access optimization and any necessary processing of the InfoSet, you should always switch access optimization on again. If you do not do this you will see a considerable drop in performance.