Code Inspector’s Performance Checks (I)

[former_member184455]

Code Inspector is SAP’s tool for the analysis of static ABAP code, data dictionary objects and other repository objects. The tool (transaction SCI) has a set of predefined performance checks that can help you to improve your code so as to optimize the performance of your program. In this weblog series I want to present some of these checks, their theoretical background, and what you can do to remedy the problem when you get a message from the Code Inspector (don’t panic!). When working with SCI you should always be aware that a simple, static check tool cannot cure all the performance or scalability problems your applications may have. These problems often only become visible at runtime – especially when the code becomes more complex. This can be, for example, identical database accesses within one dialog step, or frequently called routines that, after having gone down several call stack levels, realize that there is nothing to do, or that do identical executions over and over again. Such problems cannot be detected by a check tool that works on the static definition of repository objects. But such a tool can help you to realize some quick wins, by means of detecting shortcomings in the implementation. So, running the Code Inspector is just one step on the way to better code, but only a fool would do without it. The performance checks I want to discuss in this weblog series in some more detail are:

     Articles of this series:

1. Analysis of the WHERE clause for SELECT, UPDATE and DELETE (discussed in this article)
2. Code Inspector’s Performance Checks (II)
3. Code Inspector’s Performance Checks (III)
4. Code Inspector’s Performance Checks (IIII)

Analysis of the WHERE clause for SELECT, UPDATE and DELETE

An inefficient database access can significantly slow down the whole application. As a rule, all frequently executed database accesses should be supported by an appropriate database index. Even if there is an appropriate index, database accesses can fail to use this index at runtime, for example, because the database table statistics are not up-to-date. But these are exceptions and they are outside the scope of a static check tool such as the Code Inspector. Code Inspector only analyzes the fields of the WHERE clause and compares them to the indexes defined in the data dictionary. This, of course, only works as long as neither the database name nor the WHERE clause is defined in the code dynamically. Note: Remember that business configuration tables, which are normally small, often read but rarely modified, should be buffered within SAP’s table buffer on the application server. How to access such tables without loosing the advantage of buffering will be described in a subsequent weblog (SELECT statements that bypass the table buffer).

Check details

This is what the check does in detail:

1. A first check determines whether there is a WHERE clause at all. A SELECT without a WHERE clause does not restrict the result set (or only restricts the result set to all entries in one client for a client dependent table) – which is a real no-no with respect to performance.
2. Then, for all normalized sub-clauses of a WHERE clause the Code Inspector checks whether they contain a field that is also the (first) field in a database table index. Normally, databases have difficulties to fill gaps in an index, especially if the first index field is concerned. Thus, if the database index contains the fields A, B, C, it does not help to have a WHERE clause with only the condition "B = ‘SHERLOCK’ AND C = ‘DETECTIVE’". Since the first field A is missing, the database will do a full table (or full index) scan. Only if field A has just a few values, some database platforms might be able to fill the gap – but you should not rely on this. Another thing that the Code Inspector checks is whether the (index) fields in the WHERE clause are compared against the current parameters with 'positive' conditions like ‘EQ’, ‘>=’, or ‘BT’. This check is necessary because, to the database, it is of no help to have a WHERE clause of the Type "A NE ‘HOLMES’" (NE stands for ‘not equal’) or "A IS NOT NULL", or the like. Such negations always lead to a full scan.
3. Is the accessed database table large or small? Clearly, a full table scan is more harmful when it is done on a large table with thousands of entries than for a small table. The answer to this question influences the prioritization of the check messages by the Code Inspector. To determine the table size, the Code Inspector examines the value for the ‘size category’ in the technical settings of the accessed table in the data dictionary. Accessing large tables (size category >=2) is seen more critical than accessing small ones. Counting the real number of database entries in the table would be far too slow, and so the Code Inspector does without.

How to proceed with a Code Inspector message

Now what should you do when the Code Inspector tells you that a WHERE clause cannot use any of the existing indexes of a database table (or that there is no WHERE clause at all)?
First of all: Do not create new indexes for statements which are rarely used!
Instead investigate the following:

• If the statement, routine, or program with the inefficient database access is not used or needed anymore: => Delete it! You would not believe how many unused scraps of source code accumulate in the repository over the years. So use the opportunity provided by these messages to get rid of them
• If the statement, routine, or program is rarely used (for example, because it is a test program, or a tool that is only used in exceptional cases) => Mark the statement with a pseudo-comment. As you may know, the Code Inspector allows you to declare exceptions to its rules in the form of (pseudo-)comments in the code. You will find the appropriate comment in the documentation on the checks. Using the pseudo-comment you indicate that you have noticed the Code Inspector message, but think that it is not relevant in this case. But under no circumstances set pseudo-comments ‘automatically’ without first using your common sense!
• If the statement is frequently executed => Try to re-write the WHERE clause so that an existing index can be used. Maybe you can read a missing index field ‘cheaply’ from a buffered table to fill the gap in the WHERE clause. Or maybe you even have the required information at hand, but forgot to add it to the WHERE clause. Always formulate the WHERE clause as complete as possible. Do not read entries from the database just to throw them away shortly afterwards (as it is sometimes done in SELECT … ENDSELECT loops with a CHECK statement inside). Most check conditions on fields of the accessed database table can be incorporated into the WHERE clause. Side remark: try to formulate the fields in the WHERE clause always in the same order (preferably according to the primary key order). This will save space in the databases statement cache.
• If the statement is frequently executed and cannot be rewritten => Adjust one of the existing indexes However, be careful, changes on an index can affect other statements! => Alternatively, create a new index Be careful here, too, as every additional index stresses the database. The reason for this is that every modification of the indexed database table that touches one or more of the index fieldsalso leads to a modification of the corresponding indexes. Now, if you think of an index as being a small table in its own right, you can imagine that an update on a table with many indexes can become as ‘heavy’ as several updates. Another reason why a multitude of indexes can be harmful is that the database optimizer can be puzzled by too many choices and make less-than-optimal decisions. Data and index design itself is an interesting topic that could fill further weblogs …

This first part of my weblog series about Code Inspector’s performance checks showed you how the analysis of database accesses is done by the tool and what possibilities you have to resolve the messages that are returned. In the next weblog we will discuss the access to buffered tables.