1. Skip to Menu
  2. Skip to Content
  3. Skip to Footer>

Expert Sessions!

SQL Statement Tuning - Part 3

Tuesday, 31 July 2012 10:51

Written by Prakash Palani

Print E-mail

In this article, we will be discussing the so called "unsuitable access path" that may get chosen during the SQL Statement Execution.

Sample SQL Statement goes like this : select * from LIPS where MANDT='001' and VGBEL='00009869'.


 

In this example, the application requests the records of table LIPS where MANDT (the client) equals '001' and the field VGBEL (the name of person who created the record or object) is '00009869'. Many records are read, although only two are requested. Therefore, for some reason, this statement chooses an unsuitable access path.

As a result of unsuitable access path, it will be forced to scan through large amount of the data from the table to get the expected results, execution of the statement can be accelerated by:

Update the Optimizer Statistics

  • Change of the ABAP Code
  • Optimizing the user input
  • Create/extending/dropping an index to suit the where clause

In our case, there is only one index available on table LIPS, in order to improve the response time, we should probably create an index to suit the where clause. (Please note that the performance of other statements may suffer if the index is created/extended/dropped.)

Before creating an index, we should know how selectivity the index would be, you can use DB05 to identify the index requirement. Please be aware the selectivity analysis is expensive, hence recommended to perform when the system load is low.

 

 

{fcomment} {flike}

Share

SQL Statement Tuning - Part 3