Code Inspector Check

[Former Member]

Hi,

While doing Code Inspector Check, I am getting this error.

"No field of table index in where condition."

Can anyone tell the solution for it.

Regards,

Priyanka.

[amit_khare]

Double click on that line, it contains a select statement.

For that select statement there is an Index exisiting in your client and you are not using Fields of that Index in your WHERE clause.

Regards,

Amit

Reward all helpful replies.

[Former Member]

What if my requirement doesn't include the fields of that index.

Like, if i want to get the values of vbeln of likp where lfart = 'NL'

This simple query doesn't require any field of index, then what can be the solution of it.

Regards,

Priyanka.

[Former Member]

Nothing will happen and it just says it could be performance issue..

if ur requirement not required ,then keep what ever you want,

[Former Member]

HI,

<b>Analysis of the WHERE clause for SELECT, UPDATE and DELETE</b>

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:

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.

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.

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 fields also 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.

follow this link for more details.

/people/randolf.eilenberger/blog/2007/03/12/code-inspector146s-performance-checks-i

regards,

Ashokreddy.

[Former Member]

hi, if you feel it's boring, you can add #EC to off it, in your code.