Skip to Content

Hi all, after several projects for ABAP DB access coding, I learned a lot from that. For high quality coding, you need to care about how to fetch the data from database as soon as quickly and as much as possible at the same time.To achieve this, basic understanding of DB access is essential. Here, I want to share with you some overview of DB access and some experience about it.

For each SQL statement, the database optimizer determines the strategy for accessing data records. Access can be with database indexes (index access), or without database indexes (full table scan).

The cost-based database optimizer determines the access strategy on the basis of:

  • Conditions in the WHERE clause of the SQL statement
  • Database indexes of the table(s) affected
  • Selectivity of the table fields contained in the database indexes
  • Size of the affected table(s)

Before a database access is performed, the database optimizer cannot calculate the exact cost of a database access. The optimizer uses the information described above to estimate the cost of the database access.

As the database index act as a significant roles in database access, I want to emphasize the use of index more.

/wp-content/uploads/2008/01/index_51840.gif

From the graphic above, we could see the index is organized in B-tree, the leaf index block contains the row ID of the corresponding table records. If the field of one record is traced in index block, then the whole record could be traced using the row ID contained in this index block. This prevents the possibility to full scan the DB table for the search of this record, lessening the time needed to fetch the record.

In general, there are three kinds of DB access:

  • Index unique scan: The index selected is unique (primary index or unique secondary index) and fully specified. One or no table record is returned. This type of access is very effective, because a maximum of four data blocks needs to be read.
  • Index range scan: The index selected is unique or non-unique. For a non-unique index, this means that not all index fields are specified in the WHERE clause. A range of the index is read and checked. An index range scan may not be as effective as a full table scan. The table records returned can range from none to all.
  • Full table scan: The whole table is read sequentially. Each table block is read once. Since no index is used, no index blocks are read. The table records returned can range from none to all.

When you create a database table in the ABAP Dictionary, you must specify the combination of fields that enable an entry within the table to be clearly identified.

Position these fields at the top of the table field list, and define them as key fields. After activating the table, an index is created (for Oracle, Informix, and DB2) that consists of all key fields. This index is called a primary index. The primary index is unique by definition.

As well as the primary index, you can define one or more secondary indexes for a table in the ABAP Dictionary, and create them on the database. Secondary indexes can be unique or non-unique.

When you create secondary index, there are some rules you need to adhere to:

  • As few fields as possible in index
  • Fields that are as selective as possible
  • An index only makes sense if SQL statements that use the index return less than 5% of the table records.
  • As a general rule, an index should consist of a maximum of 4 fields.

Here I will give an example of index scan analysis. To trace the SQL, we use t-code ‘ST05′.

/wp-content/uploads/2008/01/st05_51841.gif

On bottom right of the above graphic is the source coding of SQL, here it’s an inner join of DB table and internal table using ‘Select for all entries’. On bottom left of the graphic, we could see the SQL is divided into several groups, each group contain 10 material number from internal table LDT_MATNR. On top of graphic, we see the duration of ZMARC read is very long (more than 50 seconds), which drives us to have a look at how the SQL is executed and then optimize it.

execution

 

Click on ‘Execution Plan’ button, we could see the actual execution plan by system, it describe how the system execute the SQL. From the graphic above, it shows that first, index unique scan is used to fetch ROWID from table ZMARC, then table access by ROWID to get table record. Finally, an iteration is executed to process all 10 material number in this group.

At last, I summarize some steps to do when we want to make a performance optimizing for DB access.

Steps for performance tuning

  • Overview of DB access in source code
  • Use tools to analyze the performance (ST05)
  • Identify various kinds of index scan
  • Creation of secondary index
  • Using DB hints is alternative

Hope all above will help you some in future work and welcome to comment.                                                                                                                                                                                                                                                                                                                                                                                                                                                    

To report this post you need to login first.

4 Comments

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

  1. Lars Breddemann
    Hi Jiaxiang,

    it’s always good to spread knowledge about what to check and keep in mind, when developing DB-Queries in ABAP (or any other tool).

    Anyhow, I strongly disagree with the rule-of-thumb methodology you propose:

    “When you create secondary index, there are some rules you need to adhere to:

    “* As few fields as possible in index”

    No! The index should consist of exactly the necessary columns. It may well be that by adding a field to an index (thus making it bigger) performance will increase since the table access can be omitted.

    “* Fields that are as selective as possible”

    Selectivity depends on data distribution – so although a specific index may have only few distinct key it may be a good choice to have it to get the few rows that are not like the rest (think of state-fields).

    “* An index only makes sense if SQL statements that use the index return less than 5% of the table records.”

    Oh, well – looking at past Oracle documentations this threshold was changed for every version. Again, it depends on the data distribution and what references you like to get from the index.
    Where is the proof for that statement and this value?


    “* As a general rule, an index should consist of a maximum of 4 fields.”

    Any reasoning behind this? There are hundreds of indexes with more than 4 fields in SAP systems… indexes should contain exactly the columns they need. Wether this may mean one or ten columns doesn’t matter – as long as the columns are there for a good reason.

    Maybe it’s best to point everybody to the less-known but quite informative note:
    #825653 – Oracle: Common errors

    There many of these ‘myths’ are explained and busted.

    Rules-of-thumb may be nice and easy to comprehend by developers but all SAP databases today make use of very complex query optimizsation methods. For every rule-of-thumb there will be at least a counter example where the cost-based approach works against intuition and performs better.

    KR Lars

    (0) 
    1. Jiaxiang Huang Post author
      Hi Lars,
      Thanks for your comment.Actually,these rules are just theoretical recommendations for reference,e.g.,”* As few fields as possible in index”.
      And for some other rule,”* An index only makes sense if SQL statements that use the index return less than 5% of the table records.”,I get this sentence from BC training document.Just as you said,maybe some of points from documents are outdated now comparing to up-to-date database version.
      Anyway,in real project,we need to set up practical rules for DB access,with reference to the guidance from BC materials or other documents.The experience may be updated each time with regards to real situation.
      Hope we could talk more about this topic later as performance tuning is a task never ended.

      BR,
      Huang Jiaxiang

      (0) 
  2. Fidel Vales
    Hello,

    A bit late, just read this and I can see that you fall on the same mistake than a lot of ABAP developers.

    You look at the ST05 trace and you say:
    On top of graphic, we see the duration of ZMARC read is very long (more than 50 seconds)

    Yes, 50 seconds but
    * 2664 executions (perhaps due to the “FOR ALL ENTRIES)
    this means that each execution is 19th miliseconds
    AFAIK, this cannot be considered slow
    * 26640 records
    Do you really need so many records??? the average time per record is 1 milisecond. Is this slow?

    I’ve had this discussion with sooo many developers and you are helping to spread it.

    Perhaps you can take a look at the following coming from a SAP developer:
    The SQL Trace (ST05) – Quick and Easy

    (0) 

Leave a Reply