In this recipe, we will see a few examples of problems of the SELECT statements that the code inspector may highlight. This may help in improving performance of the program at the development stage. The performance checks are available in both the transactions SCI and SCII. We will emphasize on the selection of the database-specific performance checks during variant creation, and some of the typical statements/constructs that may be caught using code inspector.
- Within the checks, expand the Performance Checks category.
- From the list of checks that appear, select the checks shown in the following screenshot:
The inspection checks the various program and highlights as warning statements that are performance statements. During this, the code inspector checks the syntax of the program and refers to the attributes and technical settings of the tables involved.
CHECKorEXITstatements used within aSELECTstatement instead of highlighting aWHEREclause.SELECTstatements included in a loop such as aDOloop or within a loop atITAB.- Any
SELECTstatements that use theBYPASSING BUFFERaddition. SELECTstatements that are without aWHEREclause will be included in the warning. The following is one such example:SELECT * FROM ZMYTAB INTO IT_TAB
- In addition to this, any table that is buffered but is included in a subquery will also result in a warning. This is because the buffering may not be used in this case, since the result of the subquery will be used at the database level in order to determine the selection set of the main
SELECTstatement.SELECT FIELD1 INTO TABLE IT_FIELDS FROM ZTAB1 WHERE FIELD2 EQ 'ABC' AND FIELD3 IN ( SELECT FIELD1 FROM ZTAB2 WHERE FIELD2 EQ 'Z1' AND FIELD3 EQ'A'.) - If the
ZTAB2table is buffered, a warning message will appear in the result. - Also, if the coding includes a
SELECTstatement on any table that is single-record buffered and aSELECT * ENDSELECTstatement is used in the program, the corresponding statement will be highlighted in the results. - Any query that may cause problems as far as the index selection is concerned will result in a message. In case, the
WHEREclause of the query does not correspond to any index in the database for the table in question, a message appears in the inspection results. This hints that there is a possibility that a full sequential scan will be run for the query that could be very time-consuming. - Also for indexes, if a particular field is not included in the
WHEREclause—for example, if the index in the database comprise of fieldsA1,B1, andC1, and theWHEREclause of the query includesB1andC1but notA1, a warning is generated.
Though the Code Inspector aid in writing better code, they have certain limitations. It is possible that a program generates no messages during the inspection, but may be slow when actually executed in QAS or production system.
The code is only analyzed and scanned along with the information pertaining to attributes of the dictionary tables used. The code is not executed (with actual data). The true performance test and analysis must be based on the actual execution tools such as transaction SAT and SQL trace. The checks of code inspector do not replace these tools.