Skip to Content

Recently i met a case of ABAP performance issue on cluster table.  i think blog is a good place to

share this topic.  Your comments are really appreciate.

Issue description and analysis

Database perspective:  SQL trace showed a table cluster  KOCLU:

SQL_Trace.gif

ExecutionPlan.gif

the Full table scan on table cluster KOCLU is done , which is the reason of this exepensive SQL Statement.

but why DBI ignore the values of the primary key fields as these values are already provied in the ABAP Source code.

ABAP perspective

source Code.gif

Cluster table KONV is only existing ABAP Dictionary and stored in the table cluster KOCLU on the database level.

KONV Primary key fields:         

MANDT
KNUMV
KPOSN
STUNR
ZAEHK

From the fields list above, the key field KNUMV was specified but not transfered to database side.

the reason behind this is the OR operation. After this is removed, the key fields were transfered to the

database side and the Index range scan was chosen instead of the full table scan.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Randolf Eilenberger

    Hi Brian,

    AND binds stronger than OR.
    BTW, a static code check with the Code Inspector would have found both the ‘bad’ WHERE-clause branch that cannot make use of an index as well as the ‘strange’ OR condition (the latter with the check ‘Complex WHERE Condition in SELECT Statement’ in category ‘Robust Programming’).

    Regards, Randolf

    (0) 
  2. Muthukumar Pasupathy

    Hi Brian,

    Check out – http://wiki.sdn.sap.com/wiki/display/ABAP/Performance+Tuning . What it says is – For transparent and pooled tables, try to qualify the SELECT statement as fully as possible in the WHERE clause. On the other hand, when working with cluster tables, qualify the SELECT statement with fields that are a part of the primary key. The other conditions (to be imposed on non-key fields) may be specified subsequent to the SELECT, in a DELETE or CHECK statement.

    Going by this, NOT specifying KSCHL in the WHERE of SELECT may be a good idea.

    (0) 

Leave a Reply