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.
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′.
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.
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.