PERFORMANCE TUNING IN SAP


Few points that should be noted by a developer while writing a piece of code to
Develop a good quality object with high performance.

For checking the performance of any object the transaction that should be used are:-
a) ST05
b) SE30.


SE30: ABAP Runtime Analysis

1. On running the transaction, the object name or the transaction associated to it must be specified as follows and then execute the same.

2. The screen below (Fig 1.0.1) shall appear once the code is executed and when you come back to the SE30 transaction screen by clicking “Back” button in the menu. Once you reach the Fig 1.0 screen, click on the “Analyze” button.

This screen shows a bar diagram that indicates the system time utilized by the code (in microseconds) which is classified under three categories:-
a) ABAP Time
b) Database Time
c) R/3 System Time

Out of the three mentioned time’s the ABAP & Database Time can be controlled by proper usage of the ABAP & SQL statements respectively which is mentioned in the later half of this document.

3. Now to get a detail analysis of the time utilized by each and every ABAP statement in your code click on the button at the top left most corner of the
Fig 1.0.1. The detail analysis is as follows:-

ST05: Trace Request

• Under this transaction there are several Trace Modes of which SQL trace checkbox needs to be checked to know the performance of each and every select query written in a piece of code.

• After checking the check box against the SQL Trace one has to switch on the trace request which can be done in two ways:-

1. Trace on: The SQL trace shall be on for the particular object that needs to be tested under the developers Username.

2. Trace on for user: The SQL trace shall be on for the particular object that needs to be tested under the developers Username with filter.


The difference between the two buttons can be explained by the following example:-

If a code is been executed by several users at the same time and if “Trace on” button is clicked then the performance analysis that shall be shown will be a effect of execution of that code for more than one scenario by different users at the same time.

Else if the same code is been executed by many users at the same time and if “Trace on for user button” is clicked then the performance analysis that shall be shown will be only for the scenario under that particular user for which the code is executed.

Now if you are clicking the “Trace On” button to ON the trace the only care that need to be taken before doing it is you need to be ready with all your inputs in the selection screen. Because if you ON the trace and then go to execute the object & fill the input fields, the time lapse between the Trace ON & Executing the code does effect the performance time.

On clicking the “Trace on for user” button a popup window shall be flashed which shall ask for the object name or the transaction associated to it of which the performance needs to be tested.

On entering the object name the trace status shall be on as shown in the Fig 2.0.1 / Fig 2.0.2.

Now the object needs to be executed with the desired inputs. Once it is executed then the trace need to be put off by clicking the “Trace off” button as shown in the Fig 2.0.

To view the performance analysis of the object, click on the “List Trace” button as shown in the Fig 2.0. A popup window shall be flashed which is as follows:-

Now on clicking enter a Basic Trace List screen shall appear which shall possess the analysis of all the select queries in that particular object.

Now place the cursor on the Object name against the Op. = REOPEN and click the “Explain” button as shown in the Fig 2.0.5. On clicking the same the Execution plan for SQL statement shall be displayed.

In the Fig 2.0.6, it is seen that the estimated cost = 1 and the estimated rows = 1.
For a SQL statement to be performance tuned the Estimated Cost of it should be tending to 1 and the Estimated Rows must be equal to the no. of desired rows that need to be fetched from the DB Table.

So to make the SQL statement in your code performance tuned we need to check its Estimated Cost and Estimated Rows values. To make it tend to the desired values mentioned above the following points need to be taken care by the developer while writing the code:-

i. Complete conditions are specified in the where clause taking care of all the primary keys and the order of it in the DB table.
ii. SELECT INTO TABLE is used instead of SELECT & APPEND.
iii. SELECT SINGLE is used instead of SELECT….ENDSELECT.
iv. SELECT * need to be avoided in case of fetching only few fields from the DB table. Instead mention the field names that need to be fetched.
v. Usage of AGGREGATE functions (MAX, MIN, SUM, COUNT, etc) must be minimum.
vi. SELECT into CORRESPONDING-FIELDS should be avoided in case of few records being selected
vii. Internal table is not empty check must be done before using FOR ALL ENTRIES in a SELECT query.
viii. SELECT queries using FOR ALL ENTRIES must use the key fields.
ix. Multiple identical SELECTs must be avoided on the same DB table. Instead the data must be fetched at one go into a global internal table and later on this internal table should be used for further processing.
x. Avoid using JOINS in SELECT queries for more than three DB tables.
xi. Avoid using SELECT inside a LOOP ….ENDLOOP statement.
xii. MOVE statement must be used instead of MOVE-CORRESPONDING.
xiii. Usage of IS/IS NOT INITIAL instead of hard code values.
xiv. Already existing INDEXES associated with the DB tables must be used as they help in making the search faster.


INDEXES
• Which indexes are actually available
• How they are denoted
• Which index can be used with a given SELECT statement?
To tell the optimizer which index to use, you need to:
• Start transaction SE11.
• On the initial screen of the ABAP Dictionary, enter SPFLI in the Database table field and choose Display.
• On the maintenance screen of this table, choose Indexes to get a list of all identifiers of SPFLI's secondary indexes. To obtain a description of the related index, position the cursor on the selected line and click Choose. (I know this sounds ridiculous, but don't blame me!)
For example, the identifier 001 represents a non-unique secondary index comprising the table columns CITYFROM and CITYTO. The index name on the database adheres to the convention ~ (SPFLI~001, in our example). Because the convention for defining the index name in the database has changed several times, I strongly recommended you check out the valid name in this way. To find appropriate candidates for index access, start with the set of columns referenced in the WHERE clause of the SELECT statement (in our example, MANDT CARRID CITYFROM). Candidates are indexes that match up some of their leading columns. I'd like to clear up a common misunderstanding: The sequence of the fields in the WHERE clause doesn't influence the optimizer's index selection. Regarding the list of secondary indexes, SPFLI~001 clearly is the only candidate. The situation becomes a little more complicated if you use comparison operators other than "=" or logical operators other than AND.
Another candidate for index access is the primary index consisting of the table fields MANDT CARRID CONNID. For the primary index, 0 is reserved as the index identifier. But again, always check out the valid name:
• On the maintenance screen of the table SPFLI, choose Database Utility in the Utilities menu.
• On the utility screen of this table choose Indexes to get a list of the identifiers of all SPFLI indexes. To obtain the primary index's database name, position the cursor into the selected line and choose Ok.

To sum up, the database system can alternatively use FULL TABLE SCAN, the primary key index SPFLI_0, or the secondary index SPFLI~001 to access the requested data. But how can you tell the optimizer which one to use?
GIVE ME A HINT
Since release 4.5, you can provide optimizer hints for SELECT statements using the %_HINTS parameter that enables emergency repairs on the spot. Of course, this parameter only works for those database systems that support optimizer hints. Because optimizer hints are not covered by SQL standards, each database vendor is free to provide them. Note that parameters starting with "%_" are never published in the documentation and should be used with special care only in an emergency. This introduction is very simplified; for a detailed description, please refer to Note 129385 in OSS.
Using the %_HINTS parameter, the example becomes:
SELECT carrid connid cityfrom
FROM spfli INTO (xcarrid, xconnid, xcityfrom)
WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'
%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.
WRITE: / xcarrid, xconnid, xcityfrom.
ENDSELECT.

If you specify hints incorrectly, ABAP ignores them but doesn't return a syntax or runtime error. This lack of notification is the reason why you have to make sure that you specified the index's correct denotation.
Now you should know how to perform optimizer hints. If you aren't careful to use them sparingly, you'll only have yourself to blame when things go wrong. o




Now if you click on ECMCT~1 in the Fig 2.0.6 the detailed information of the index 1 defined for the ECMCT table shall be displayed as in the
Fig 2.0.7(below). It indicates the usage of the Index of ECMCT table in your
SQL statement. If in the Execution Plan you find ECMCT~0 it means that the
basic primary keys of the table are used.


Clicking on ECMCT in the execution plan displays the following information of the indexes associated with the ECMCT table. To get the detailed Index Statistics click on the “Index Statistics” Button in the Fig 2.0.8 (below).


xv. Performance tips specifically with Internal Tables:-
Unnecessary sorting must be avoided.
READ TABLE must be used with BINARY SEARCH.
It must be Cleared / Refreshed wherever necessary.
APPEND to be used instead of COLLECT.
Usage of Memory ID should be minimal & freed immediately after use.
CLEAR the Header Line / Work Area after the APPEND statement.
CLEAR Header Line after DELETE statement.