Category Archives: SAP development

Application performance control in SAP development

SAP ST12 Trace – SQL performance analysis

This post is to continue my writing on how to tune a SAP program/application performance based on SAP ST12 traces. My previous post is writing about overall SAP program performance tuning process and how to analyze ST12 ABAP trace to improve SAP program/application performance. Here, I would cover how to analyze SAP ST12 SQL trace to tune SAP program/application performance from development point view.

1 SAP ST12 trace – SQL analysis

You might wonder where you should start to analyze SQL statement performance. My choice is to use “Summarized SQL Statements” of SAP ST12 performance trace and focus on top expensive SQL statements. Please click here for how to navigate to “Summarized SQL Statements” screen from SAP ST12 performance trace.

1.1 Understand data in “Summarized SQL Statements” screen

Following is a portion of SAP “Summarized SQL Statements” screen from a SAP ST12 performance traces. The displayed is sorted by total execution time or duration of a SQL spends during tracing period in default.

 

Figure 1 – SAP-PERF.CA

Before you analyze SQL performance based on “Summarized SQL Statements” screen, you need to make sure that you understand the data presented in the screen:

– Number of times which a SQL is executed by the program during the trace.

– Number is in percentage which of all table read are identical. Two reads on database table are identical, this means that the same program read the same record two times. So as long as the SQL statement where clause is the same on the same table even they are at different location of the program and different fields are returned, they are still identical. In the figure-1, the second line has a value of “1,200” and “58” in column “execution” and “identical”, this means that 696 of 1,200 times when SQL are executed is identical. 1,200 X 58 / 100 = 696.

– Duration is sum of each individual SQL execution time for number of times under “executions” column. Records are total # of records which database server returns to application server where your program is running.

– Average time per execution for the SQL = duration/Number_of_execution. Average number of records retrieved from database table per execution of the SQL = Records/executions.

– SAP table buffer type. There are no buffer, single record buffer, generic area buffer and full table buffered. Blank means that table is not buffered.

-SAP transparent table/pool/cluster/view name. When it is a cluster table or pool table in source code, “Summarized SQL Statements” would show cluster or pool name instead of the table name. For example, in SQL, you can get change header or item information from table “CDPOS”, in ST12, it would show CDCLS under Obj. name column instead of “CDPOS”.

– This is a “shorten” SQL statement from database SQL cache. You can see full SQL statement by double clicking the SQL statement. You can select the SQL followed by clicking on the in figure-1, SAP would then show you the SQL execution plan which contains full SQL. Several ABAP SQLs can be mapped into one SQL statement in the summary windows. You review the related ABAP source code by clicking in above figure-1 after you place cursor on the line you are interested.

At this moment, I would like to clarify several things further to avoid possible confusion on data displayed in “Summarized SQL Statements” screen showed in figure-1.

Number of execution, identical selection and Number of records returned in “Summarized SQL Statements” is from database server point view not application/program logic point view. For example, Select-for-all SQL statement is just executed once from ABAP program logic point view, but number of execution in “Summarized SQL Statements” screen might be more than 1 depends on number of records in the ABAP internal table and system setting. Also, number of records returned by the database for SELECT-FOR-ALL statement could be more than number of records which the program see since the database interface in the application side would remove duplicated records before data is passed to the program.

Number of records which a program sees could be different from what database server actually processes as well. For example, when database server does a “SUM” operation, it just returns one value/record but database might need to get all needed records to get the total result.

All time here is measured in Microseconds and measured from application server point view, so the database time includes impact from network traffic time and IO system when applicable. There are general guidelines to see whether a database data operation( sequential read, direct read, insert and commit ) is impacted by database performance issue(including network and IO) based on average response time. However network and IO normally have a broad impact on a SAP system performance instead of cherry-picking one specific program.

1.2 Analyze Top expensive SQL operation.

Following is the steps I used to analyze Top expensive SQL statement based on SAP ST12 SQL trace for tuning SAP program performance.

  • Check database table access strategy.
  • Review identical access.
  • Review Table buffer strategy.
  • Review number of SQL execution.
  • Review data returned.
  • Review table data volume management strategy.

All above checks/tests, if you do not like term “steps”, can be needed for analyzing one SQL statement. Or only one check is needed for a particular SQL statement. All depend – your knowledge on the business solution, the program, testing case, and the SQL.

1.2.1 Check database table access strategy

This step is to review SQL execution plan to see whether an index or right index is used to read the table. So we can tune SAP program performance by speeding up data retrieval via an index or more appropriate index.

First, you got the SQL execution plan from “Summarized SQL Statements” screen and index details

Figure 2 – SAP-PERF.CA

You can get index details by double clicking on the index name or get all indexes by double-clicking the table name in the execution plan.

Then, you review and analyze execution plan by cross-checking between SQL where-clause and execution plan. You also need to check SQL statement in execution plan and corresponding SQL code in the ABAP program. You can have following result

1. Index is not used and database is using “full table scan” to read database data.

This could be where-clause of ABAP SQL code has no selections fields matching fields of any existing index. Or ABAP SQL where-clause(selection criteria) has one or more index fields but during the execution, the program passes no data to those index fields. Please notice that field sequence in where clause makes no difference on execution plan for Oracle CBO engine.

To tune SAP program performance in this case, possible solution can be:

  • Use alternative table for the information.
  • Change fields of where-clause to match existing index.
  • Change program to make sure that data is passed to index field(s) in where-clause.

2. Index is used but not correct.

This could be due to complex where-clause in SQL statement. I am not talking about wrong index choice related to table or system statistics or database setting, which requires no ABAP effort to tune SAP program performance.

To tune SAP program performance, you need to simplify the where-clause. Or use corresponding database hints in ABAP code to influence database CBO index choice for this particular SQL.

3. Index is used but other indexes might be better.

This could be due to the fact that table has several indexes and those indexes have common fields where it is referenced in SQL statement.

You need to use corresponding database hints to tell database that what index should be used, you can review and change table index design or change where-clause to let database to use preferred index.

4. Index is used but index itself is not selective.

This is normally due to index design. This happens usually to local table or local developed index.

To fix this, you need to redesign the index using “selective” table fields and arrange index fields properly with consideration of query request and clustering factor. What is selectivity of a field? It is a measurement against two data: Number of distinctive value for a field and number of record in that table. The higher number of distinctive value for a field, the more selective the field is.

Also selectivity of index and selectivity of where-clause might not be necessary the same. Sometimes, an index is not selective but the where clause can be very selective – data histogram can play a difference. For example, if a table has 1,000 records, one field has only two distinctive A and B. If B has 999 entries and A has only 1 entry, then it is very selective to select entry from the table with condition of values equal to “A” assuming the field is the only field of an index. Querying the table using “B” value is not selective.

Now I would like to mention “estimated cost” in the execution plan, the higher it is, the more expensive the SQL is. For CBO (Cost Based Optimizer), the database server always uses an execution plan which has a lower “COST”. But Lower cost does not mean an index is always better than another index. Discussion on this is not focus of this posting.

1.2.2 Review identical access

This is to understand why there are identical accesses in “Summarized SQL Statements” to see whether we can eliminate identical access to tune SAP program performance. You review identical access by reviewing the SQL source code and SQL statement in the execution plan. It is possible one SQL statement in “Summarized SQL Statements” screen can mapped to more than one ABAP SQL statements at different program code location.

1. More than one SQL statements from the ABAP program are mapped to the SQL statement.

2. Only one SQL statement from the ABAP program is mapped to the SQL statement.

To tune SAP program performance, you can consider following options

  • Consolidate the similar SQL statements. If you need two fields from a record at different program location, it is better to retrieve two fields at one location instead of doing it separately at different program location from program performance point view.
  • Move SQL statement to more appropriate location – like move it outside of loop, put it at document head-level instead of line-item level and implement at expected higher organization level like company instead of repeating it at every lower organization level like plant.
  • Use program level buffer – retrieve data then store it for later reference in the same transaction to avoid database table access. There are several SAP ABAP techniques to achieve this.
  • For SELECT-FOR-ALL SQL statement, you can sort the internal table and remove “duplicated records” before the internal table is used in the SQL statement to retrieve data from a table.

Identical access sometimes can represent a significant performance tuning opportunity – because related program unit (or business function ) might only need to be executed once but executed many times due to improper design or coding. In Figure-1, if we assume that time for each access is same, we can make about 58% performance tuning on 2nd SQL by just removing those identical access.

1.2.3 Review Table buffer strategy

This is to review table buffer status based on “Summarized SQL Statements” screen to see whether we can enable buffer or deactivate buffer to tune SAP program performance. You also can review whether the SQL statement has bypassed SAP buffer based on code and whether the business logic needs to bypass SAP buffer.

Which SQL statement would bypass SAP buffer? Following is the list:

  • Any SQL select statement with a keyword “bypassing buffer”.
  • Any Select with a sub query or with joins
  • Any “count” function ( Count, Min, MAX, SUM, AVG)
  • Group by
  • Select DISTINCT
  • Order By
  • Select for update

Click here for SAP document and examples on ABAP SQL statements bypassing buffer.

If SAP program should not bypass buffer when it access table, then you need to change SAP SQL code so data requested is from application buffer instead of database table.

Only SAP configuration table and parameter table are appropriate candidates for buffering if the table is “seldom” changed and has an “appropriate” size. What is “seldom” and what is “appropriate” table size, there is really no hard cut-off line. The general guideline is we can buffer record if record change rate is under 1% and table size does not exceed 5MB. You might need to select “right” buffer mode based on table size, changes and type of query.

If a buffered table is now changed more frequently, this can impact your program performance as well due to buffer synchronization. In this case, you need to disable the buffer.

I might write a post to share my experience on dealing with table buffering. You can click SAP table buffering to know more in this area.

1.2.4 Review number of SQL execution

Here we review Number of execution of SQL in the summary screen to see whether we can tune SAP program performance by reducing number of execution on the SQL. Further by reviewing number of execution, it might be discovered that a subroutine might be executed more than what is really needed! To reduce number of SQL execution, you can consider similar solutions mentioned in 1.2.2 section of this post.

If there are individual Select, “Insert” or “update” on a table, it would help to tune program performance by using mass operation like mass insert and mass update.

1.2.5 Review data returned

This is to review what information each record contains and how many records are returned by the database server to ensure that only data needed by business operation is returned from database not more and not less. Eliminating un-needed fields of each record can result new execution plan like getting data only from an index and reduce round trips between application server and database servers. Reducing number of records returned by database by building more specific retrieval condition into SQL where-clause is better than retrieving records from table then filtering records at program level.

1.2.6 Review table data volume management strategy

Number of entry in a table has a big impact on database time used to search a record when the table primary key is not being used to search a record. It is clear that index or not index would make no performance difference if the table has only several records in the same way that using a calculator or not make no time difference to calculate 1+1. So we need to understand how long a data should be kept in a table to meet business requirement and how long the data is actually kept in the table. If this understanding would result in “significant number” of records being removed from the table, then the SAP program performance would be improved greatly even there is no code change.

Last but not least, you also can review top expensive SQL statement to see whether they are related and can be combined SQL on different tables via join and/or database view – this can help on the SAP program performance as well.

2 Clarification

When I talk about improving SAP program performance, I mainly cover this from application side on how we can improve ABAP program code (ABAP and SQL) and ABAP program/business solution design.

ST12 has combined features from SAP transaction SE30( ABAP trace ) and ST05(SQL trace). So what I stated here is applicable to ST05 trace(SQL) analysis.

Whether you should analyze both ABAP and SQL traces or just one of two traces, this depends on your situation. If your program spends 99% of time on database side, then you should focus on SQL performance analysis to tune SAP program performance.

I am not talking about ABAP program performance from system/network point view like table statistics, table/index storage status(fragment, allocation) etc. So you need to make sure that performance issue is not due to system(Capacity, OS, DBMS, IO, Network etc). Please refer to performance introduction for details on what influences program performance. If all other programs are working fine in a system except your program and performance of your program is “consistent”, this can normally means a code/design issue. If your program performance is becoming worse due to volume increase over a long period, the performance issue should be related to design and/or code of the program. There are “general” criteria to say whether database/storage performance can be a concern.

I am not talking on how to deal with a one-time SAP program performance incident where program runtime is deviated greatly from normal range –That is a performance incident trouble-shooting.

Last not least , you might need to search SAP OSS note for possible solution especially when a standard SAP program has performance issue which is not due to system resource or database decision(like Oracle CBO etc).

Frequent operations On SAP tables in SAP performance analysis

In performance analysis, we often use different sap transactions to get information related to a SAP table like table attribute, table structure, index, table size, storage quality, column statistics, table content and etc. involving SAP transactions SE11, SE16, SE14, SE13, DB02, DB05, db20, TAANA, RSANAORA, RSORAISQN and /SDF/RSORADLD_NEW. In this post, I would talk about frequent used SAP transactions and operation related to SAP table based on my experience.

  1. SAP SE11 – SAP data object definition

    SE11 can be used to display ABAP dictionary objects definitions like tables (transparent, clustered/pooled table), views, data type, application lock objects and etc.. Here, I would list some frequent checks

1.1 How to check whether a table is buffered or not and type of buffer?

Buffer is one of SAP table technical setting. It is normally for small and seldom changed configuration/parameter tables not for transactional table.

If you have a table and would like to know whether the table is buffered and buffering type, please follow the path : Execute transaction SE11-> Enter table name and then click display “button” -> Click “Technical Settings” button

Figure 1 SAP table technical settings

You can access table technical settings screen (Figure 1) as well via SAP transaction SE13 -> enter table name -> click display key. From SAP table technical setting screen, you know whether data change logs has been enabled for a table.

Search ST10 table call statistics, you can know whether a table is buffered or not.

1.2 How to check table technical setting change history

SAP provides version management for technical settings, so you can know who, when, what changes are made to technical settings by version comparison.

Technical setting version management can be accessed from technical setting screen via menu path: Goto ->Version management.

Figure 2 SAP table Technical settings – change history

In my customer system, we did have a job performance issue due to technical setting changes – turning on buffering option.

SAP has version management for other SE11 changes as well like table structure change history and index change history. Depends on your need, you need to switch to related SAP SE11 screen to see it’s change history.

1.3 How to find out a pool or cluster which a pooled/clustered table belong to?

Run SE11 -> enter name of the pooled/clustered table -> click the display button -> delivery and maintenance tab

Figure 3 SE11 Delivery and maintenance – Pool/cluster name

A001 is a pooled pricing table in Pool “KAPOL”.

1.4 How to find out existing table indexes?

Run SE11 -> enter the name of expected table ->click “display” button -> click “index” button (or menu path Goto -> indexes). When there is no index, the menu path or “Index” button might be disabled by SAP.

Figure 4 SAP SE11 – a list of ABAP indexes for a table

If you would like to know details of a specific index, you can double click the entry.

Figure 5 SAP SE11 an index of a table

Please notice an index defined in SAP ABAP dictionary might not exist at database level. How to know whether an ABAP index exists at database side? You can tell this by the text under “status” field in Figure 5. A text, “Index does not exist in database system”, indicates that the ABAP index does not exist in database system otherwise index exists in database side. Normally an index defined in SAP ABAP level exists in database level.

1.5 How to find where a SAP table is used

Run Se11 -> click radio button “database table” -> Enter table name -> click “where-use list”. Following is a where-use list sample for SAP A871 table.

Figure 6 where-use list of a SAP table

Where-use list is showing all program which is referring to a table in the system. This is different from the questions – what sqls are executed to access a specific table since system is started. This question would be answered bellowed.

 

1.6 How to identify SAP table relationship/linkage

Run SAP SE11 -> Enter table name -> click display -> click “graphic” command in icon bar (ctrl+shift+. SAP has built-in tool to show table relation in graphic

Figure 7 SE11 – SAP Table relationship

I personally find this difficult to navigate. Having no idea on the criteria which SAP used to select tables. There are many online documents on SAP table relationship which is much easier to read. Just google it.

Knowing SAP table relationship is important so you can use right table when same information exists in several table. I have seen cases where inappropriate table is chosen and resulted in poor application performance.

2 SAP SE14 & ST04 –Database object consistence check

Consistency checks whether a table is defined exactly in the same way in both SAP ABAP data dictionary and database schema. If it is the same, then it is consistent. From performance point view, we focus on index definition. In my experience, missing index in database did happen due to different reasons and it did impact SAP job/program performance when needed index is missing.

2.1 How to check individual table consistency?

SAP transaction SE14 is used to check single object consistency. TO check, you run SE14 -> enter table name (make sure radio button for table is select) -> Extras(menu) -> database object ->check. Figure 7 and Figure 8 are two sample screens showing two typical inconsistent scenarios – index in SAP but not in database and index in database but not in SAP.

Figure 8 Table inconsistency – index not in Database

Figure 9 Table consistency check – index in database but not in SAP ABAP

I had seen performance case where an index defined at SAP ABAP level did not exist at database level.

2.2 How to check table/index consistency in a SAP system?

If you would like to know overall picture on table/index consistency picture in a SAP system, you can get Figure 9 screen via path: SAP transaction ST04 -> Diagnostics -> Missing tables and indexes…

Figure 10 SAP consistency check – Missing tables and Indexes

If you have access to SAP transaction DBACockpit or DB02, you can get a report related to data object consistency at database level, if your table is not in this list, then the table is in consistency – database object exists both in ABAP level and database level. If your individual table does not show up in Figure 9, your table is consistent as well.

3 SAP SE16/DB02/DB20 – How to check number of entries in a table?

There are different SAP transactions SE16, DB20 and DB02 which can tell number of table entries. I would recommend to use DB20 or DB02 for such check, they would give a good enough information in this regard with minimal additional system cost.

3.1 Use SAP transaction SE16 to check number of entries

Run SE16 -> enter table -> hit return key -> enter a value for a specific fields -> click “number of entries” button

Figure 11 SE16 – number of SAP table entries

If you do not enter data to any field, you would get total number of table entries. SE16 gives you up-to-date information but it is the most expensive method to get number of table entries and it might take long for a big table since database needs to scan through whole table to count the number of entry.

3.2 Use SAP DB02 to get number of table entries.

Run DB02 -> click Segments ->double click Detailed Analysis -> Enter table name under “Segments/Object” field -> storage tabe

Figure 12 DB02 – number of rows in a SAP table

Figure 12 shows that material master table MARA has 2,667,535 entries. This number might be different from latest number which you got via SE16 depends on when the table statistics is updated and how frequent the table is changed.

3.3 Use SAP DB20 to get number of table entries

Path: Run DB20 -> Enter Table name like “VBAK” in field Table -> click “update info” command.

Figure 13 DB20 – number of SAP table entries

Figure 13 shows table row change activities since last statistics calculation. It shows how many new rows are inserted to the table and how many rows are changes since then.

4 SAP DB05 – How to check number of distinct value and their distribution?

Run SAP transaction DB05 –> Enter table name -> Enter specified fields,

Figure 14 DB05 – distinct column values and their distribution

When you create an index and evaluate an index performance, you often need to know how selective an index field is. DB05 can be used to check number of distinct fields and their distribution

 

5 SAP TAANA – how table entries are exactly distributed across values of specific fields

SAP DB05 can tell number of distinct fields and their distribution of a table field. Figure 14 told you that there are 1 -10 entries for 4 distinct VKORG field. But what are those sales organizations and how many rows/entries for each of sales organization, those questions can be addressed by TAANA transaction.

Figure 15 SAP TAANA – table content analysis

Figure 15 can help to answer questions – why would SQL selection can be executed much faster sometime using the same field. For example, If SOrg is an index field, selecting a specific “US16” entry should be much faster than selecting a specific “US51” entry.

6 ST04 – how to find out all SQL statements executed to access a specific table?

You can use ST04 -> SQL cache -> filter display by table name. But this would be slow. In Oracle environment, there is another way to do this: Run /SDF/RSORADLD_NEW -> click “table name” and enter specific table name under “Restriction Rules” section -> click “execute” button. Please note /SDF/RSORADLD_NEW have many other features. For example, /SDF/RSORADLD_NEW is more efficient to check top expensive SQL operation than ST04 in an Oracle environment.

7 SAP ST05/ST12 – How to find out what tables are accessed by a SAP program?

Similar questions are how to find out where a SAP program/transaction is getting data from and which table are updated.

You can do a SQL trace via SAP transaction ST05/ST12. SQL trace would show what tables are accessed during the program execution.

There is a standard SAP table D010TAB which is showing transactions and their associated tables and structures. A structure showed can be a part of showed table.

You can get which tables are accessed by a particular program via SQL cache analysis tool like ST04 transaction or /sdf/RSORADLD_NEW program.

8 ST10 – how to find out SQL data operation activities on a table?

This is called as table call statistics

Figure 16 ST10 – SAP Table call statistics

9 SAP transaction DB02 – Table and index monitor

SAP DB02 can be used to display many information related to a SAP table and its’ indexes like size, storage quality, number of distinct values for table column, growth history. Those information can be used in program and system performance analysis.

9.1 SAP DB02 – how to check size of a table/index

Path: Run SAP DB02 -> Click “Segments” -> Double Click “Detailed Analysis” -> Enter “table/index name” -> Return.

Figure 17 DB02 – table size

Figure 18 DB02 – Index size

Figure 19 DB02 – table and index size in one screen

9.2 SAP DB02 – how to check index storage quality

Path: DB02 Index size screen -> Click “Storage” -> Click “Storage Quality”.

Figure 20 DB02 – index storage quality

9.3 SAP DB02 – How to check table size growth history

Path: Figure 14 -> click “History” tab.

Figure 21 DB02 – table size growth history

9.4 How to check number of distinct value of a specific SAP table fields

Path: Figure 14 -> Table columns

Figure 22 DB02 – number of distinct values for table fields

10 SAP RSANAORA – How to update table statistics or rebuild index?

You can use SAP program RSANAORA to update table statistics and rebuild table/index in an Oracle Environment. This program offers no parallel solution but it can allow you to specify sample size during the statistics updating as well as histogram option during the runtime.

11 SAP RSORAISQN – How to update table statistics, check storage quality and rebuild index?

You can use RSORAISQN to update table statistics, review index storage quality and rebuild table/index as well in an Oracle environment. To enhance performance, it introduces parallel processing capabilities and allows you to specify number of parallel processes in index/table rebuilding process or analyzing process.

Figure 23 RSORAISQN – check index storage quality

ABAP programming – performance tips and tricks

When you are coding an ABAP program, you often find that the ABAP program can be coded in different ways to do the same job, you are wondering which way would give a better performance. When you are tuning the ABAP program performance, you encounter an expensive ABAP statement in SAP ST12/SE30 trace, you are wondering why it is so expensive and what are other BAP coding alternatives to replace the expensive one to improve the ABAP program performance. Tips and tricks utility built in standard SAP transaction SAT/SE30 might help you with those questions. SAP SAT/SE30 is comparing performance of common ABAP techniques/statements used by ABAP developers via ABAP performance examples. So understanding those trips and tricks can help ABAP developers to avoid common performance pitfalls during ABAP program development phase. It can also help you when you need to tune ABAP program performance. This post would cover

  1. Introduction of ABAP performance tips and tricks,
  2. How to access ABAP performance tips and tricks examples,
    1. Preferred coding techniques for SAP SQL interface – database table access,
    2. Preferred coding techniques for ABAP internal table access,
  3. How to use performance tips and tricks examples in ABAP program development and performance tuning.

1 Introduction of SAP ABAP performance tips and tricks

SAP SE30/SAT organizes ABAP coding performance tips and tricks into following groups or categories to make it easier for you to identify specific ABAP code techniques. See Table 1 for groups and their explanation.

Table 1 – ABAP performance Tips and Tricks Group

Performance Tips/Tricks Group Explanation
SQL interface Explain which database access technique would give better performance by comparing pairs of Database table access techniques: Select+ check vs Select + Where, Select and Exit vs Select … Up to 1 Rows, Find , Select Array select vs single select, read without index vs read with index, individual operation and mass operation etc.
Context Obsoleted. If applicable in your version, using context is better than select since common accessed data is stored in the memory and shared across SAP transactions.
Internal Tables Explain which internal table access technique would give better performance by comparing pairs of kills like binary read vs key read, individual table operation vs mass internal table operation etc.
Typing Explain that parameters/field-symbols whose types are specified is more efficient than parameters whose types are not specified.
If, Case… Explain that case statement is more efficient than IF statement
Field Conversion Explain that we should avoid un-necessary field conversion by using the same data type.
Character / String Manipulation Explain that using string is better than character from performance point view
ABAP Objects Explain that call method and call subroutine has no material performance difference but calling FM is slower than Method.

If you need to read a record from big internal table, you can read it via key or binary search, then you can go to “internal table” group to which one is better to your situation.

Two groups – inefficient SQL statements and inefficient ABAP internal table operation are highlighted based on my experience. They are often culprit of bad ABAP program performance related to coding. So familiar with performance coding techniques under these two groups should have priority over other groups.

2 How to access ABAP performance Tips and Tricks

To access SAP ABAP performance tips and tricks, you need to execute SAP transaction SAT/SE30, then click on button “Tips & Tricks”.

Figure 1 SE30/SAT initial screen

If you click button “Tips and Tricks” button highlighted in Figure 1, performance tips and tricks for ABAP objects screen like below Figure 2 would be displayed.

Figure 2 Se30/SAT Performance Tips and Trick Initial screen

I would cover more details related to highlighted groups “SQL interface” and “Internal table” below.

2.1 Preferred Performance coding techniques for SAP SQL interface / database table access

This group would explain preferred coding technique out of two different ways of table access. Please refer to Table 2 for details

Table 2 SQL interface – preferred SQL coding strategy

ITEM Performance preferred Explanation
Select + check VS. Select with where conditions Select with where conditions Use as much fields as possible in where clause: index might be possible with the fields and filter earlier to avoid unnecessary data traffic between DB server and application server
Select + exit Vs. Select with Up to 1 rows Select with up to 1 rows Select with up to 1 rows would incur less trips/traffic between DB and Application server.
Select + check VS. Select using Aggregate function Select using Aggregate function Avoid round trip between DB server and application server, reduce traffic
Select * Vs. Select with a list of fields Select with a list of fields Reduce round trip and traffic between application server and database server
Update one row VS update specified field Update with specified fields Reduce network traffic
Select without index VS Select with index Select with index Using an Index would reduce number of logical DB reads, so it would be faster than a full table scan when limited # of records are fetched.
Select without buffer support VS select with buffer support Select with buffer Reading buffer could be up to 100 time faster than read from db disc.
Select with Append statement VS select into table Select into table Reduce round trips between DB and application server
Many individual inserts VS Array insert Array insert Reduce round trips between DB and application server
Select + Endselect VS Select into table + loop table Select into table + loop table Reduce round trips between db server and application servers as well as network traffic
Nested Select Statement VS Select with view Select with view Reduce round trips between db server and application servers as well as network traffic
Nested Select statement VS Select with Join Select with join Reduce round trips between db server and application servers as well as network traffic
Using two Selects VS Select using a subquery Select with subquery Reduce round trips between db server and application servers as well as network traffic

In following section, I would go through some SQL performance examples.

2.1.1 Select + Check VS Select with where conditions

I clicked SQL interface group and all child groups under SQL interface, then I clicked “Select… Where” Vs. “Select + Check”, I got screen like Figure 3.

Figure 3 ABAP performance example – SELECT + Check Vs Select + Where clause

The display is divided into 3 portions in Figure 3, left panel – navigation to different group and ABAP code performance techniques, Right upper window is showing two sample ABAP codes using two code solutions which achieve same result. Right bottom window explains what is the performance-preferred ABAP coding technique.

Based on explanation in Figure 3, it is clear that “select with where conditions” has better performance than “select + check”. In another word, the sample code at right in figure 3 should have better performance than ABAP sample code in the right. Is that true? You can actually test this to get a feeling by clicking “Measure Runtime” button in Figure 3. Upon clicking it, I got Figure 4 screen.

Figure 4 ABAP performance example- Select + check vs select with where conditions

In this particular care, time spent by “Select + check” example code is 10 times longer than time spent by “select with where clause” example code.

In the same fashion, you can navigate to other SQL coding techniques to get a feeling of performance difference created by different ABAP coding techniques via sample codes.

2.1.2 Select without buffer support VS Select with buffer support

Figure 5 shows that Reading data from application buffer is 10 times faster than reading data from database system.

Figure 5 ABAP performance example – Select without buffer support vs Select with buffer support

2.1.3 Select into table + loop at table VS. Select Endselect

Select into table is much more efficient than Select … Endselect. Select into table needs more “memory” to hold the result set. In normal situation, this is not a concern. When memory is a concern, you can divide the result set into smaller sets.

Figure 6 ABAP performance example – Select Into table + loop at table VS Select.. ENDSelect.

2.1.4 Array/Mass Insert VS single-row Insert

Array/Mass Insert is always better from performance point view since it can reduce unnecessary round trips between database and application server by utilizing full capacity of data communication bus between database and application server. It can also allow database

Figure 7 ABAP performance example – Single-line insert VS Array Insert

2.1.5 Nested selects VS Select with view

Select with view always performances better than “Nested Select”.

Figure 8 ABAP performance example – Nested Select VS Select with View

2.1.6 Nested select VS Select with Join

Select with join always performances better than Nested selected.

Figure 9 ABAP performance example – Nested Select Vs Select join

2.2 Preferred Performance coding techniques for ABAP internal table operation

Please refer to Table-3 for some ABAP code techniques which are important to application performance. They are selected from what offered by SAP SAT/SE30 tips and tricks based on my performance experience.

Table 3 SAT/SE30 ABAP internal table handling performance tips and tricks

ITEM Performance preferred Explanation
Linear search VS Binary search Binary search If internal tables are assumed to have many (>20) entries, a linear search through all entries is very time-consuming. Try to keep the table ordered and use binary search or used a table of type SORTED TABLE. If TAB has n entries, linear search runs in O(n) time, whereas binary search takes only O( log2( n ) ).
Secondary index Vs No Secondary Using secondary index for repeated accesses When an internal table is accessed with different keys repeatedly, create your own secondary index. With a secondary index, you can replace a linear search with a binary search plus an index access.
LOOP internal table and CHECK VS LOOP … WHERE Loop… where LOOP … WHERE is faster than LOOP/CHECK because LOOP … WHERE evaluates the specified condition internally. As with any logical expressions, the performance is better if the operands of a comparison share a common type. The performance can be further enhanced if LOOP … WHERE is combined with FROM i1 and/or TO i2, if possible.
Sorted table VS Hashed Table Sorted table for repeated accesses. Hashed table for single access Hashed tables are optimized for single entry access. The entries have no specific order. Therefore, a partial sequential access cannot be optimized. Every entry must be checked to match the condition (full table scan). Sorted tables are ordered ascendingly by their key components. If a partial key of the form “k1 = v1 AND … AND kn = vn” where k1 .. kn matches a left part of the table key, the access is optimized by the kernel. In that case, only the matching entries are visited.
Copying a table via loop VS equal Equal operation better Internal tables can be copied by MOVE just like any other data object.
If an internal table itab has a header line, the table itself is accessed by itab[].
Join table – Loop table 1 then read table 2 VS loop table1 then read table 2 with index Loop table 1 then read 2nd table with an index If ITAB1 has n1 entries and ITAB2 has n2 entries, the time needed for joining ITAB1 and ITAB2 with the straightforward algorithm is O( n1 * log2( n2 ) ), whereas the parallel cursor approach takes only O( n1 + n2 ) time. The above parallel cursor algorithm assumes that ITAB2 is a secondary table containing only entries also contained in primary table ITAB1. If this assumption does not hold, the parallel cursor algorithm gets slightly more complicated, but its performance characteristics remain the same.

In following sections, performance code examples from SAT/SE30 are covered for selected topics.

2.2.1 Read internal table via key VS Read Internal table via binary search

Figure 10 show It is more efficient to read an internal table via binary search if the table entries is huge. This benefit might be more significant if you need to read the table many times.

Figure 10 ABAP performance example – Linear against Binary search in an internal table

2.2.2 Read internal table without secondary index VS secondary index

Reading an internal table using a secondary index could be more efficient than read via a key.

Figure 11 ABAP performance example – Read internal table using index VS no-index

2.2.3 Read internal table via combination of Loop and Check VS read internal table via Loop… Where

Reading an internal table with loop… where statements is more efficient than loop + check statements.

Figure 12 ABAP performance example – Loop and Check VS Loop where

2.2.4 Read internal table – Hashed table VS sorted table

When you need to read the internal table many times, using sorted table is more efficient than using hashed table.

Figure 13 ABAP performance example – Sorted table VS Hashed table

2.2.5 Copy internal table – Loop + append and copy VS “=”

Using “=” to copy table is more efficient than using Loop + append to copy an internal table.

Figure 14 ABAP performance example – copy internal table

2.2.6 Join internal tables – Loop table1 and read table2 against loop table 1 and read table2 via Index

Using method of looping table 1 than reading table 2 via INDEX is more efficient than method of looping table 1 then reading table 2 without index.

Figure 15 ABAP performance example – join internal tables

3 Testing performance of your own ABAP codes

I have seen ABAP developer to compare performance of two different sets of ABAP code which achieve the same purpose via SE30. It looks like that this can be done via SAT/SE30 menu path EDIT-> Editing -> LEFT-hand /Right-Hand as showed in Figure 16. I do not have authorization to do this in the system I owned.. Readers like you can explore this more should you be interested in it.

Figure 16 SAT/SE30 – your own sample codes

4 Further Information

A SAP ABAP program performance can have many contributing factors like solution design, functional configuration and program code from application performance point view. An application performance issue can be rooted from database performance, system capacity, network, IO design as well. SAP performance tips and tricks cannot help to improve ABAP program performance issue which is rooted from solution design, functional configuration, database performance issue and etc. It can help to verify whether a program performance issue is rooted from programming techniques. In my experience, over 50% of ABAP program performance issues are related to program code. So it is important for a SAP developer to become familiar with those performance examples and performance-preferred ABAP coding techniques.

Again, not all performance examples from SAP SAT/SE30 are covered here. My intention is to cover most common/critical examples based on my experiences. You can run SAP SAT/SE30 online to review the remaining ABAP performance examples. Also, SAT/SE30 covers “common” performances examples. There are many ways to put ABAP statement together for the same purpose. It is not the expectation that SAT/SE30 provides a examples for each situation.

SE30 allows you to do ABAP runtime analysis but it would not provide much data to support SQL performance. My preferred tool to do ABAP program runtime analysis is SAP transaction ST12 which I find efficient to do performance analysis on ABAP logic operation and database access. I might come out a post on how to use SE30 to do ABAP runtime analysis in the future.

I have following posts related to SAP program performance: how to improve a SAP ABAP program performance; how to run SAP ST12 to do a performance trace; how to analyze ABAP trace; how to analyze SQL trace;

SAP ABAP program performance tuning process

You are asked to work on a SAP ABAP program to make it run faster. It is a big program with thousands lines of ABAP source codes, so you are wondering what is starting point and the efficient process to tune the SAP program performance. This is what this post is written for. This post covers:

  1. Overview on SAP ABAP program performance tuning process and
  2. Understand steps in SAP ABAP program performance tuning process.

I am talking about tuning a SAP ABAP program performance due to “persistent” performance issue which is rooted from program code/design here not one time performance incident due to sporadic resource contention in program operating environment.

1 Overview on performance improvement process

Performance improvement is normally an iterative process until performance tuning goal has been met. The overall process is illustrated as what showed in figure 1.

Figure 1 SAP program performance improvement process

SAP ABAP program performance tuning can be an iterative process. When top performance concerns are addressed, other performance concerns which are under shadow of the addressed performance concerns can be surfaced as major improvement opportunity for next cycle. By repeating the cycle, you bring the program performance to a new level until your performance tuning goal is reached.

2 Understand steps in SAP ABAP program performance tuning process

2.1 Understand performance gap

This is the starting point of SAP ABAP program performance tuning process. At this step, you need to find out:

  1. Targeted performance – This helps you to establish performance tuning goal and
  2. Current performance – This helps you to establish performance baseline which can be used to measure the performance improvement.

The difference between targeted performance and current performance is the performance gap which the process is targeted to eliminate.

2.2 Identify performance scenario/test case

 

This is to understand under what condition a program is executed with “poor” performance so performance testing case can be planned. Different business scenario would involve different portions of program code. If performance test case is not related to performance issue, then the “bad” code would not be executed so it would not be captured during trace. So it is critical to performance tuning process that a right performance testing case is identified for each performance scenario.

When performance issue is mainly due to volume, a serial of testing cases with ramp-up volume might need to be defined instead of one testing case with big volume which can run for long time like hours or even days. I normally limit runtime duration for a performance testing case execution under one hour. At the same time, you need to maintain a “meaningful” testing volume so performance issue can appear.

2.3 Execute scenarios with performance tracing

This is to execute performance test case and use performance tool to trace execution of test case. SAP trace can show how much time is spent by a subroutine/function-module/form, an ABAP operation and a SQL operation. So you can find where the time is spent by the program –which helps to locate improvement opportunity in the program code.

There are several SAP performance tracing tools like SAP transaction ST12, ST05 and SE30 etc. I would recommend that we use SAP transaction ST12 to do the trace since it can catch ABAP trace (ABAP logic operation) and performance trace(SQL execution) in one execution. Of course, you can use SAP transaction ST05 if it is a done deal that a program is slow due to database access or SAP transaction SE30 if program is slow due to ABAP logic involving no database operation.

When you do trace, the scenario being traced had better be executed twice one after another and make sure performance trace is turned on for the 2nd execution only – this is to eliminate buffering impact on program performance.

If testing lasts longer, you might want to do several traces at different time during the test execution instead of one trace.

2.4 Analyze trace and identify improvement opportunity

This is to review performance trace to identify major inefficient “ABAP logic” and inefficient SQL operation. Following is the strategy I used to analyze ABAP and SQL traces for ABAP program performance improvement:

Figure 2 ABAP/SQL traces analysis strategy

Inefficient “ABAP logic” would consume application server CPU power. Inefficient ABAP logic is mainly related to internal table handling and loop etc.

Inefficient SQL is putting extra load on database server. Inefficient SQL operation is those SQLs like accessing database table without proper selection criteria etc.

  • Identify silver bullet from performance trace

    This is to review performance trace to identify top expensive ABAP logic/ABAP statement/SQL statements. If performance gap is 20% and performance tracing shows that one ABAP statement takes 40% of running time, then tune the ABAP statement might give you the expected performance gain.

    SAP ST12 traces provide gross time and net time for an operation captured in the trace. I normally sort by “net time” so top expensive ABAP/SQL statement can show on the top – this facilitate analysis.

  • Identify “extra” subroutine call

    No matter how efficient a function is coded – if it is not needed by business, then it should not be executed. So we should try to avoid executing such code instead of improving the code. If one time execution is enough, then it should not be executed for the 2nd time.

  • Review program design and function configuration

    If no enough performance improvement opportunity is identified, then we need to review overall solution design or related SAP configuration to seek performance improvement opportunity.

  • Break down workload and use parallel processing

    After all those effort , if the performance is still a concern, there is no performance concern over the program on ABAP logic and database access, there is no function configuration concern, the design is ideal but performance is still short of the expectation. Now we should consider breaking down the work to smaller tasks which can be handled by multiple processes at the same time – this is called parallel process. Bottom-line is that we should only consider parallel solution when underlying program has a sound design and performance has been well tuned.

In a program, there might a lot of improvement opportunity technically but each improvement opportunity might bring different level of performance improvement to the program. Here, we focus on those improvement opportunities which would bring significant improvement on ABAP program performance.

Following is two sample screens – one is ABAP trace screen and the other is performance/SQL trace screen. Both screens are from the same ST12 tracing on a program execution.

Figure 3 ABAP trace of a program execution

Figure 4 – SQL trace of a program execution

Attention: If your program is interacting with another process executed locally or remotely via RFC( Remote Function Call) and performance trace shows that your program is spending a lot of time in waiting for completion of RFC, then you need to tune the RFC program involved. If the remote executed program is an ABAP program, the same process mentioned here can be applied to tune the RFC program performance.

2.5 Finalize the solution and implement change

This is to take action to implement needed solution to improve the program performance.

2.6 Test and measure performance with the change

This is to repeat testing case again to measure performance improvement with changes.

If testing shows that program performance with the change is still short of expectation, you have to repeat the whole process again.

It is important that we should execute the same test and in the same SAP box before and after the change. Using different testing box is just adding another variable which makes performance measure harder.

SAP transaction SCI known as “SAP Code Inspector” can be used to scan through ABAP code to identify common performance pitfalls like SQL statement without where-clause etc. This is a static performance check. Output from this can be a part of improvement proposal together with what from trace analysis. Static code check is relatively simple and it is not focus of this post. It is a good habit to use SCI to validate change each time after a program is changed as well.

3 Further clarification

There are other performance issues like too much memory used by a program. To tune such performance issue, the overview of process in general is similar to what stated here. But what tools should be used to catch memory offender is different – ST12, SE30 and ST05 would not tell memory usage. I might write another post on how to tune ABAP program memory usage in the future.

Program can suddenly run slower due to many reasons in production box like contention or bug of a system (SAP, OS and database) etc. Program performance at such scenario cannot be improved via the process mentioned here. Solving this type of performance issue normally requests changes on other components of operating environment instead of program itself. I have written a post on how to trouble-shoot production performance incident.

In this post, I have recommended to use SAP ST12 to trace SAP ABAP program execution for performance analysis. You can click here to know how to use ST12 to do performance trace. If you are interested, there are posts on how to analyze SAP ST12 ABAP trace to tune SAP program performance and how to analyze SAP ST12 SQL trace to tune SAP program performance.

You are asked to work on a SAP ABAP program to make it run faster. It is a big program with thousands lines of ABAP source codes, so you are wondering what is starting point and the efficient process to tune the SAP program performance. This is what this post is written for. This post covers:

  1. Overview on SAP ABAP program performance tuning process and
  2. Understand steps in SAP ABAP program performance tuning process.

I am talking about tuning a SAP ABAP program performance due to “persistent” performance issue which is rooted from program code/design here not one time performance incident due to sporadic resource contention in program operating environment.

1 Overview on performance improvement process

Performance improvement is normally an iterative process until performance tuning goal has been met. The overall process is illustrated as what showed in figure 1.

Figure 1 SAP program performance improvement process

SAP ABAP program performance tuning can be an iterative process. When top performance concerns are addressed, other performance concerns which are under shadow of the addressed performance concerns can be surfaced as major improvement opportunity for next cycle. By repeating the cycle, you bring the program performance to a new level until your performance tuning goal is reached.

2 Understand steps in SAP ABAP program performance tuning process

2.1 Understand performance gap

This is the starting point of SAP ABAP program performance tuning process. At this step, you need to find out:

  1. Targeted performance – This helps you to establish performance tuning goal and
  2. Current performance – This helps you to establish performance baseline which can be used to measure the performance improvement.

The difference between targeted performance and current performance is the performance gap which the process is targeted to eliminate.

2.2 Identify performance scenario/test case


This is to understand under what condition a program is executed with “poor” performance so performance testing case can be planned. Different business scenario would involve different portions of program code. If performance test case is not related to performance issue, then the “bad” code would not be executed so it would not be captured during trace. So it is critical to performance tuning process that a right performance testing case is identified for each performance scenario.

When performance issue is mainly due to volume, a serial of testing cases with ramp-up volume might need to be defined instead of one testing case with big volume which can run for long time like hours or even days. I normally limit runtime duration for a performance testing case execution under one hour. At the same time, you need to maintain a “meaningful” testing volume so performance issue can appear.

2.3 Execute scenarios with performance tracing

This is to execute performance test case and use performance tool to trace execution of test case. SAP trace can show how much time is spent by a subroutine/function-module/form, an ABAP operation and a SQL operation. So you can find where the time is spent by the program –which helps to locate improvement opportunity in the program code.

There are several SAP performance tracing tools like SAP transaction ST12, ST05 and SE30 etc. I would recommend that we use SAP transaction ST12 to do the trace since it can catch ABAP trace (ABAP logic operation) and performance trace(SQL execution) in one execution. Of course, you can use SAP transaction ST05 if it is a done deal that a program is slow due to database access or SAP transaction SE30 if program is slow due to ABAP logic involving no database operation.

When you do trace, the scenario being traced had better be executed twice one after another and make sure performance trace is turned on for the 2nd execution only – this is to eliminate buffering impact on program performance.

If testing lasts longer, you might want to do several traces at different time during the test execution instead of one trace.

2.4 Analyze trace and identify improvement opportunity

This is to review performance trace to identify major inefficient “ABAP logic” and inefficient SQL operation. Following is the strategy I used to analyze ABAP and SQL traces for ABAP program performance improvement:

Figure 2 ABAP/SQL traces analysis strategy

Inefficient “ABAP logic” would consume application server CPU power. Inefficient ABAP logic is mainly related to internal table handling and loop etc.

Inefficient SQL is putting extra load on database server. Inefficient SQL operation is those SQLs like accessing database table without proper selection criteria etc.

  • Identify silver bullet from performance trace

    This is to review performance trace to identify top expensive ABAP logic/ABAP statement/SQL statements. If performance gap is 20% and performance tracing shows that one ABAP statement takes 40% of running time, then tune the ABAP statement might give you the expected performance gain.

    SAP ST12 traces provide gross time and net time for an operation captured in the trace. I normally sort by “net time” so top expensive ABAP/SQL statement can show on the top – this facilitate analysis.

  • Identify “extra” subroutine call

    No matter how efficient a function is coded – if it is not needed by business, then it should not be executed. So we should try to avoid executing such code instead of improving the code. If one time execution is enough, then it should not be executed for the 2nd time.

  • Review program design and function configuration

    If no enough performance improvement opportunity is identified, then we need to review overall solution design or related SAP configuration to seek performance improvement opportunity.

  • Break down workload and use parallel processing

    After all those effort , if the performance is still a concern, there is no performance concern over the program on ABAP logic and database access, there is no function configuration concern, the design is ideal but performance is still short of the expectation. Now we should consider breaking down the work to smaller tasks which can be handled by multiple processes at the same time – this is called parallel process. Bottom-line is that we should only consider parallel solution when underlying program has a sound design and performance has been well tuned.

In a program, there might a lot of improvement opportunity technically but each improvement opportunity might bring different level of performance improvement to the program. Here, we focus on those improvement opportunities which would bring significant improvement on ABAP program performance.

Following is two sample screens – one is ABAP trace screen and the other is performance/SQL trace screen. Both screens are from the same ST12 tracing on a program execution.

Figure 3 ABAP trace of a program execution

Figure 4 – SQL trace of a program execution

Attention: If your program is interacting with another process executed locally or remotely via RFC( Remote Function Call) and performance trace shows that your program is spending a lot of time in waiting for completion of RFC, then you need to tune the RFC program involved. If the remote executed program is an ABAP program, the same process mentioned here can be applied to tune the RFC program performance.

2.5 Finalize the solution and implement change

This is to take action to implement needed solution to improve the program performance.

2.6 Test and measure performance with the change

This is to repeat testing case again to measure performance improvement with changes.

If testing shows that program performance with the change is still short of expectation, you have to repeat the whole process again.

It is important that we should execute the same test and in the same SAP box before and after the change. Using different testing box is just adding another variable which makes performance measure harder.

SAP transaction SCI known as “SAP Code Inspector” can be used to scan through ABAP code to identify common performance pitfalls like SQL statement without where-clause etc. This is a static performance check. Output from this can be a part of improvement proposal together with what from trace analysis. Static code check is relatively simple and it is not focus of this post. It is a good habit to use SCI to validate change each time after a program is changed as well.

3 Further clarification

There are other performance issues like too much memory used by a program. To tune such performance issue, the overview of process in general is similar to what stated here. But what tools should be used to catch memory offender is different – ST12, SE30 and ST05 would not tell memory usage. I might write another post on how to tune ABAP program memory usage in the future.

Program can suddenly run slower due to many reasons in production box like contention or bug of a system (SAP, OS and database) etc. Program performance at such scenario cannot be improved via the process mentioned here. Solving this type of performance issue normally requests changes on other components of operating environment instead of program itself. I have written a post on how to trouble-shoot production performance incident.

In this post, I have recommended to use SAP ST12 to trace SAP ABAP program execution for performance analysis. You can click here to know how to use ST12 to do performance trace. If you are interested, there are posts on how to analyze SAP ST12 ABAP trace to tune SAP program performance and how to analyze SAP ST12 SQL trace to tune SAP program performance.

SAP project – Application Development Performance Control

In my previous post – performance control in SAP project, SAP project performance effort is divided into three important performance areas:

  • Capacity planning,
  • Application development performance control and
  • Deployment/Configuration.

In this post, I would talk more about application development performance control. Application development performance control is to make sure that a program or a solution developed in a SAP project runs as faster as possible in addition to meet quantified performance requirement, uses resources as much as it needs and as less as possible and work well with other solutions. I would cover

  • Overview on SAP application development performance effort and
  • Performance activities in different SAP project phases.

Application performance control is to focus on performance issue prevention, and earlier detection and solution.

1 Overview on SAP application development performance effort in a sap project

In my view, performance effort in SAP program/solution development can be classified into following areas as showed in figure 1.

Figure 1 SAP application development – Performance Areas

All effort in application development performance control in a project is to focus on:

  • Prevention – It is always better that a performance issue never exists in performance requirement definition, in application design or configuration, in program development and in solution deployment. Performance training on performance standards, guidelines, tools/resources and best practice can help to reach this goal.
  • Detection – If there is a performance issue, we would like to detect and find this as soon as possible. It saves project cost, effort and synergy to fix a performance concern detected in design phase other than in business acceptance testing or production phase.
  • Solution – So we have detected a performance concern, we need to find a way to fix it.

With all those effort, we can be closer to our ultimate performance goal that all SAP application solutions developed by the project have good performance qualities in terms of time, resource and resident-ship.

1.1 Performance training

Teaching a man how to fish is always better than giving him a fish. That is objective of performance training. To achieve this, Performance training would try to equip all developers and business users with performance standards, performance guidelines, performance best practice and performance tools. So the developers know what is expected as well as tools and resources which help them to meet that expectation. For example, SAP developers and business users should be clear on expected performance of a solution which they are responsible for. If you do not know this, then you could not realize potential performance concern or realize a performance issue is happening.

It is important to give necessary performance training end business users as well. Training on business users with specific objective – facilitate performance works which needs business input like performance requirement and avoid un-necessary performance issue due to business users’ improper interaction with SAP system etc.

Some SAP projects often overlook importance of this step. Time/effort invested in this step has a profound effect in and outside of the project – design and code delivered by project would have less performance concern during performance validation – this could reduce rework in the project and possible performance concern in the solution life cycle. Developers and business users can use the performance knowledge in their future works/projects.

Performance training should be given to the project community (such as developers and key business users) after project is kicked off and before any design and development work is started.

Adequate training can help to achieve one of performance goal: performance issue prevention.

1.2 Collect Performance requirement

This step is to collect and clarify business quantify performance requirement as well as needed performance data on project solution – like how critical it is to performance, volume and pattern, frequency, concurrent usage, data retention period etc.. If your project is going to use SAP quick-size for hardware capacity planning, you can refer to performance data collected in that process for application development control.

1.3 Develop performance validation plan

This would include scope, method, tools, measurement, test, who, when, where and processes. Validations in different project phase would be different. Validation in different stage of a solution development cycle would be different. Subsequent section of this post would talk performance validations based on SAP project phases.

Validation scope specifies what is subject to validation:

  • Technical deliverable like design document, technical specification, SAP configuration specification and program code developed and
  • Function/process scope – all function or selective function.

Validation method specifies how to validate performance:

  • Static validation(like technical design review and code review) which has no involved on program/solution execution like technical design document review, and
  • Dynamic validation through analyzing program behavior by doing performance testing and tracing.

Measurement is the agreement on when a validation is considered as “successful” without performance concern. For example, static evaluation on design document might be considered successful if no concern is found against performance design guidelines; dynamic validation on a program testing case might be considered successful if it meets runtime, resource usage and residence requirement.

Tools include issue tracking tool, test automation tools, performance monitoring tools, tracing and analysis tool ( SAP ST12, SAP ST05) etc. If it is applicable, this should be specified in the validation plan.

Tests here is about what business scenario should be tested in performance validation.

Who is to specify who should be involved in performance validation. In terms of testing, you need to specify who should prepare the testing case, who should execute the case, who should monitor the case and who should trace and analyze the case and who should track and manage all concerns from validation to make sure they are properly addressed to satisfaction.

When is to specify when a performance validation should happen – Design, BAT and etc..

Where is to specify whether a validation should happen in development box or testing box etc.

The process is to integrate all above components together.

For a single solution development, the performance validation cycle is built into each milestone of a solution development as:

Figure 2 Performance validation cycle for single solution

Performance validation is an iterative process. For example, if a performance issue is detected in performance testing, then we might need to verify the performance requirement again, if design is changed to fix the performance issue, then design needs validation. Technical code introduced needs validation. Performance testing needs to repeat to see where we are standing in terms of performance after change.

Performance test is normally done after functionalities of solution has been tested to work fine by business to avoid rework. For critical and complex solution, performance test and code validation could/should be done earlier since it might take significant time and effort to make it performance compliant.

Performance validation plan for SAP development should be worked with “earlier detection” objective in mind. It should be built into each phase of SAP project life cycle and each milestone of a solution development.

1.4 Execute performance validation plan

Predefined performance validation should be executed without delay so a potential performance issue can be detected earlier.

1.5 Validate solution deployment plan

This is to check whether solution developed by a project is best positioned and integrated into production landscape to achieve load balance and avoid contentions.

1.6 Support and validate solution in production operation

This is to verify how solution is performing in terms of meeting our performance goal and address any production performance incident or potential concerns.

2 Performance activities in different SAP project phases

Above, we talk about performance effort/areas in SAP application development. You might wonder how we can map them into each phase of a SAP development project.

2.1 Preparation/Planning phase

In this phase, an initial application development performance control plan is developed based on nature of SAP project, Performance goals and objectives are established. The performance control plan profiled a list of performance areas and activities needed for a SAP project.

Before the end of phase, performance training should be given to project community so they understand benefits which performance control brings to business and are ready to work on or support subsequent performance works in the project.

2.2 Business Blueprint/Match and Mapping

Performance activities in this phase can include:

  1. Collect and validate performance data,
  2. Review and update Performance standards, practice, guideline review etc.
  3. Review and update Performance validation/control processes review and
  4. Update Performance control/validation plan.

This is to understand business requirement and map them into corresponding SAP solutions. In this phase, it might be good to define performance questionnaires to facilitate performance data collection. Meantime, it is important to update/define performance standards, performance guidelines, performance best practice and performance tools which should be applicable to the project. Process for performance validation/control should be defined and communicated to the project community such as table/index creation approval , buffering approval process, parallel solution approval process etc.

Performance plan can be updated based on more input from business. For example, initial performance control/validation plan states that key processes needs to be tested without details, you can add/update those details into the plan based on input from this phase. Project budget cannot afford effort to validate all technical designs, the need to select critical/important technical design and validate them arises. So you work with business users to come out “selection criteria” and use that “selection criteria” to choose a project solution whose design should be validated from performance point view. The selection criteria should be define based on how critical a solution is to business in terms of time and level of risk which a solution can bring into the SAP system. Time sensitive, resource extensive program is normally a critical program. Other factors can include complexity of the solution, number of users, frequency etc.

2.3 Realization/Design and construction phase

Performance activities in this phase can include:

  1. Performance training,
  2. Performance testing plan,
  3. Static performance check on solution technical design document,
  4. Static performance check on technical code and/or configuration,
  5. Dynamic performance check on solution developed – Performance unit testing and
  6. Address any known performance issue.

Before any design, configuration and development is committed, performance training should be given to all developers. In this phase, performance training should be focus on performance standards, guidelines, best practices, tools and related performance processes.

Performance testing plan should be defined or tuned in this phase.

Static performance check on technical design documents would be executed in this stage to ensure that technical design follow the performance standards, performance guideline and best practice. This should be executed before actual development is started. Dependent on effort allocation, you might validate technical design on selected business process based on predefined selection criteria

Static performance check on program code/configuration is to make sure program code is in compliance with performance standards, guidelines and best practice. Dynamic performance check is to check performance of program execution. SAP standard tool – code inspector (SAP transaction code – SCI) can be used to facilitate static performance check on SAP ABAP development.

Developer should be responsible for performance of the application he develops. So it is a reasonable expectation that self-check on performance should be carried out by the developer before the solution is passed on to next phase. Since solution in realization/”design and construction” phase is more likely to subject to change than in business accepting phase, having dedicated performance resources to execute performance validation on a full-scale might be premature.

2.4 Final Preparation/SIT, BAT phases

Performance activities in this case can include:

  1. Performance volume testing based on performance plan,
  2. Static validation(code review) on program code developed by the project,
  3. Dynamic validation(testing) on solution which has local development,
  4. Validation and review system log via SAP workload monitor(SAP transaction code ST03), database monitor(SAP transaction code ST04) and core-dump check(SAP transaction code ST22),
  5. SAP performance training to end-business users,
  6. Solution deployment validation,
  7. Solution readiness check – this is to make sure all performance testing have been completed and all performance concerns have been addressed to satisfactory and
  8. Address any known application performance issue.

If a solution cannot meet business functional requirement, it would be subject to change. Performance validation should be carried out after solution has passed business acceptance testing to avoid rework. As long as a solution is ready and signed off by business, the solution performance qualities should be validated. So any potential performance issue can be addressed earlier – earlier is always better than later in this case.

Validate and review system log should be done regularly. If there is a clear cut between SIT and BAT phase, ST03, ST04 and ST22 check should be done at least once after completion of each phase.

When we validation a solution performance quality, we need to remember performance has three qualities –time, resource usage and resident-ship not just runtime. So a program has “good’ runtime quality but it is using too much resource like memory than what it really needs, it can impact system or other program’s performance. A program has good “runtime” and “resource usage” but it would place locks on a shared resource earlier or hold it longer than it really needs – this can impact other existing solution or potential future solution. Further runtime is not the same as “quantified performance requirement” for a solution. If a SAP solution meets quantified performance requirement, but it is running longer than it really needs, then it is not meeting “Runtime” requirement of good performance.

2.5 Go Live & Support

This is to monitor and validate application performance in production box against what is projected and address any performance concern related to the project in the production environment. As part of validation, you need to analyze production load distribution, work process usage, memory usage, workload analysis, database load analysis and cored-dump reviewed. My personal choice is to schedule SAP snap-shot program /SDF/MON to collect performance snapshot and do the review in addition to ST03, ST04 and ST22 review.

3. Further clarification

This post is based on “true” practice I adopted and executed in several major SAP implementation projects – where I was lucky to see that those projects delivered without single “true” performance issue when they went alive. For smaller/simple project, you might select what is important to you. Your company might use different term for a project phase or different phase for a SAP project, you can map those performance activities based on your situation.

Application performance control is just one area of performance control in SAP project. You can refer to my post – SAP performance control in SAP project for more in this area if you are interested.

In this post, I have mentioned performance review on technical design. I also mentioned performance training and performance testing. What should we check in a technical design review from performance point view? What are those performance guidelines/practices which a SAP developer should be aware of? How do we validate code? How should we select and define performance testing case? How do we validate performance of program via performance testing? I would try to answer those questions by sharing my experience in my future postings. Stay tuned.

SAP table buffering

In a SAP system, there are many layers of data buffering – IO subsystem, database system, application and program. All buffering is to make data available in memory so request for the same data can be fulfilled via memory. This helps data retrieval performance and underlying business transaction or program performance. In this post, I would talk about SAP table buffering at SAP application level. I would talk about

  1. SAP table buffering introduction.
  2. How do we determine whether a SAP table should be buffered or not in SAP development or production support?

1 SAP table buffering introduction

1.1 SAP buffering concept

 

SAP table buffering is to store data of a table in an application server memory. After data is in memory and no data change, any request for data in the buffered table by any program or business application executed in the server would be fulfilled from the memory of local application server without involvement of database server. ABAP trace in my SAP environment shows that getting data from buffer can be up to about several hundred times faster than getting data from database server.

Figure 1 SAP buffering Overview

SAP buffering pros and cons

Pros

Further clarification

Cons

Further clarification

Speed up data retrieval process

This can speed up corresponding related program and transaction.

The same data would store on memory of all application server – redundancy.

Memory demand would be higher

Reduce database load

Database server is a Central Instance

Change synchronization delay.

Data retrieved by program can be out of date

Reduce network traffic

 

Buffer bypassing can happen.

Buffer is not utilized

Reduce process residency time in a SAP work process

Reducing possible contention on work process resources

   

Figure 3 SAP Table buffering pros and cons

1.2 SAP buffering types

SAP allows three type of buffering choices – single record buffering, generic records buffering and full table buffering. All those types of buffering are referring to size of “data entry” which would be managed by the SAP buffering mechanism in buffer loading, swap, data change management.

 

Buffer load during read when data is not in buffer

Buffer change/invalidation

Buffer swap

Single record buffered

Load one record into application buffer at one read. A record would not be in application buffer if it is never read since start of the instance

One record at one time.

Swap would be done at record level.

Generic area buffered

Load all records which share the same partial primary keys. No read means not in buffer

All records which share the same partial primary key of the changed records are invalidated at the same time.

All records shared the same partial keys are either swapped or in memory as a whole.

Full table buffered

Full table is loaded into buffer at 1st read

Whole table are invalidated upon any change on the table.

Whole table would be swapped.

Figure 4 SAP table buffering types

In following section, I would talk about how to select right buffering type for a table.

1.3 SAP buffer for table buffering

The application server memory used for SAP buffering is called SAP buffer. There are two types of SAP buffer used in table buffering – Generic Key buffer and single record buffer. Generic buffered table and fully buffered table would share the same SAP buffer – Generic Area Buffer. Size of SAP buffer is configured by two parameters: one is to specify size of buffer,the other is to specify number of the directory entries in the table buffering.

 

Buffer size parameter

Further info

Number of entry parameters

Further info

Generic area buffer

zcsa/table_buffer_area

Buffer Memory size

zcsa/db_max_buftab

Maximum number of buffer that can be buffered

Single record buffer

rtbb/buffer_length

Size of single record table buffer

rtbb/max_tables

Max. number of buffered tables

Figure 5 SAP tabe buffer parameters

1.4 SAP buffer synchronization

After a SAP table is buffered, the buffered data can be changed, this data changes need to be reflected in generic area buffer or single record buffer of all SAP servers in the system.

Any change by a sap program/user is always initiated from an application server. On the server where change is initiated, the table/data is marked “changed”/”invalid” (Actual state is “pending” in ST10) as when buffered data is changed (Whether change is committed or not makes no difference based on my testing). When buffer table is marked as “changed”/”invalid” ( “pending” status in ST10 ) in a server, the data request from the server would be fulfilled by database until certain number of read operations on the table occurs from the application server. After that number, buffer would be refreshed and marked “valid”. This mechanism is to reduce load from pre-mature buffer reloading or buffered being refreshed too often un-necessary. For example, a program can update a table hundreds or thousands of time in a seconds, if the system is going to reload the data into buffer for each update, this could bring down the systems.

Now what are footsteps used by other servers for buffer synchronization? When a buffered table is changed, a change entry is logged into SAP table DDLOG. SAP buffer has a mechanism to read DDLOG table “PERIODICALLY” to know what buffered table is changed and from which server, based on that information, it would mark the buffered table as “invalid”/”changed” in all remaining SAP application servers. So there is a delay between data is changed in one server and corresponding data in memory is marked as “invalid”/”changed” in remaining instances of the system. Extent of delay is determined by parameter RDISP/BufRefTime, timing of a data change, volume of buffer table changes and instance/server performance. After a table is in “pending” status, data needed by a program from a server would be from database table. The table would be loaded into buffer again after a certain number of read operations on the server. Please click the post how ST10 buffer state is change for details. So remember when table is in “valid” status, the data would be read from buffer for the server even it is already changed in another server!

Apparently, the delay of buffer synchronization only matters for the SAP system with more than one application server/instance.

What are SAP buffer synchronization parameters? They are:

  • RDISP/BufRefMode – when there is more than SAP application server/instance, this parameter should have value of “Sendon, ExeAuto” – this enables buffer synchronization. Value of “sendoff, exeauto” means no buffer synchronization – applicable for sap system with only one SAP application server/instance.
  • Rdisp/bufRefTime – this specify interval between two adjacent buffer synchronization so buffer can be synchronized periodically. It has a default setting “60” seconds.

You can use SAP program RSDBBUFF
to know when latest buffer synchronization is done for an instance/server and how far away the server/instance is from next buffer synchronization. However if you would like to know what table buffered entries are changed in the past, SAP program RSTUNE60 can help.

1.5 how to activate SAP table buffering

You might be wondering how to enable buffering for a selected table? Table buffering is a technical setting of a SAP table. SAP transaction SE13 allows you to display or change technical settings of SAP table directly. On the technical setting screen, you just need to switch on the buffering and check the right buffering type as showed in the following screen. You know you need to save the changes – after that, you are done – your table is buffered as per your instruction.

Figure 6 SAP table buffering setting

When you select buffering type – Generic Area buffered, you need to specify how many key fields should be used according to your buffering design. Of course, number if key fields specified should be less than number of fields which the primary index has… otherwise, it is a single records buff.

You can use SAP SE11 transaction to display and change technical settings using menu option of SE11 screen( 2nd screen) as well

1.6 SAP buffering monitoring

SAP transaction ST02 can be used to monitor SAP buffer and memory at instance level to see whether there is enough buffer space. SAP transaction ST10 can be used to monitor access status of both buffered SAP tables and not-buffered SAP tables.

1.7 Table buffering makes a performance difference – an example

Following is result of a ST12 ABAP trace on a business transaction execution. TABLE USR05, TVTA and /VSO/M-VSO_SETUP are buffered configuration tables. VBAK and VBUK are transaction table which has no buffering. Select statements are using full primary keys of those tables.

 

 

 

Table name

Buffer status

Total # of read

Total time(micro seconds)

Time per read(rounded)

TVTA

buffered

58

826

14

USR05

buffered

1,474

26,201

17

VBAK

No

2,342

8,260,148

3,526

VBUK

NO

2,342

6,769,828

2,890

/VSO/M_VSO_SETUP

Buffered

2,342

23,370

10

 

 

Above table shows a significant SQL runtime difference between reading buffered table and non-buffered table when primary keys are used.

2 How do we determine whether a SAP table should be buffered or not?

2.1 What should we consider to buffer a table?

Bye now, you know how SAP table buffering is working now and significant runtime improvement for table access through buffering. So you might wonder how we can reach a decision on whether a SAP table should be buffered or not.

To reach decision, you need to consider following factors/guidelines

Figure 7 SAP table buffering – influencing factors

Following are further prints to help you to understand above points better:

  1. Table size – In general we should not buffer a big table since memory is normally limited. We should be extra diligent when you buffer a table which is bigger than 1% of defined buffer space.
  2. Data change habit – This refers to when, how often, volume of change related to table. In general, we should not buffer a table which is changed frequently. A table with a change/read ratio > 0.5 should not be buffered normally.
  3. Data access habit – This refers to access frequency, access time pattern, access volume pattern (associated selection criteria) and load generated by the SQL. we should not buffer a table which is seldom accessed/queried by SAP programs/transactions. How table is access or access pattern is important as well. Buffer a table with higher database load can be good for database performance. Common wisdom is what is good to database would be good to SAP application. Your option to buy into thisJ. Certain SQL SELECT statement would bypass buffer. If majority of SQL statements being used for a table would bypass buffer, then buffering the table makes no sense.
  4. Buffer synchronization delay impact – We should not buffer a table when buffer synchronization delay results in obsoleted data being read by other SAP programs/transactions. This is not a concern for a SAP system with only one application server. If business insert/update data in a quiet window (Data change habit), then this is not a concerns any more.
  5. Business performance requirement – If buffering a table can improve a business process performance significantly, then we should consider buffering seriously.
  6. Buffer and memory status – If buffer space is not enough and cannot be increased due to physical memory limitation, buffering new table might create more displacements or swaps. If there are heavy swaps in table buffer area, you would make the things worse to buffer additional table, I think you can agree with me on this. It is better to maintain 10% of free buffer space – do not forgot to check free directory as well.
  7. Database server and application server load status – this would help us from load distribution point view – where is the best place to keep the load based on specific case. The general wisdom is to remove load from database server to application servers. but if your application server is already overloaded and database server has a lot of idle power, it would not makes sense to buffer a table before application server load can be mitigated. The normal recommendation is that SAP server/instance should maintain at least 20% free CPU power for peak processing – this is especially important to DB server.

Standard SAP table has a predefined setting on table buffering attributes. You should not change the setting for a standard SAP table in general. A SAP tables which stores business transaction data cannot buffered. SAP tables for master data are not buffered. SAP tables for configuration table are normally buffered. However, buffering setting on standard SAP setting can be changed suited to your specific business needs.

Consultants from SAP recommend that table over 5 MB should not buffered. But if this table is frequent accessed, very expensive, very stable, there are enough free buffer space and buffering the table can help program to meet business performance goal, then buffering the table might be a wise choice even it is 10 MB in size. If a table is small and seldom changed, but there is a high chance that delay of synchronization can create data inconsistency and impact accuracy of business result when there is a change, this table cannot be buffered.

Table buffering type is a fine setting mainly dependent on table size, table change habit and access habit. The setting would impact size of data which would be marked “changed”/”Invalid”, If each time, only one record is changed:

  • Fully buffered -> all records of the table would be invalidated and reloaded to buffer by the system. One change would result in loading of whole table.
  • Generic buffered ->all records which share the same common keys fields would be invalidated and reloaded to buffer by the system. Access other records in the affected group needs to get from database after they are marked “changed” until they are reloaded. Risk of getting obsoleted data if data is accessed after change and before synchronization.
  • Single record buffered -> only the change record is invalidated. Other records would be valid in buffer. But single record buffered needs more round trips between database and application server to buffer a table.

Under fully buffered and generic buffered, I would like to highlight that change one record would cause more than one record being reloaded to buffer from database. When table size is big, volume of change and access is high in the same time window, this can create serious system performance issue due to frequent buffer reloading- I have seen this in my work.

If many records are changed each time, “full” or “generic” table buffering might be working better to avoid too many round trips between application server and database server related to buffer synchronization. Number of key fields should be decided based on fields reference in SQL where-clause.

So what tables can be buffered or cannot be buffered – you need to review all those factors and understanding what is your key driving factors based on your business need. There is no hard line here. Bottom line is that you need to know the context surrounding the table buffering – the system, the business application and the table.

2.2 How do we know whether a SAP table should be buffered in SAP development/project?

Here, we only consider “new” local table developed in a SAP development. Designing a new local table involves many areas – like table structure, primary key and index, data retention, data management etc. Among them, it is buffering attribute. If you do not specify buffering attribute explicitly, the table would not be buffered by SAP. Many developers overlook technical settings of SAP table. In the end, it creates performance issue and more effort to correct this later.

Transactional table cannot be buffered normally. We only need to consider local table like mapping, reference and set up tables by collecting needed information related to previous stated table buffering guidelines:

  1. Who would populate data? Then when/how frequent is the data inserted to the table? How many entries would be inserted to the table? Who can be a person, a SAP job, a SAP program and an interface.
  2. Who would use the data? Then when/how frequent is the data accessed? What selection criteria would be used to access data? How many records would be accessed by each user.
  3. Who would modify the data? Then then when/how frequent is the data modified? What are selection criteria for data, how many records would be updated at one time window.
  4. How long would data stay in the table? This is data retention period.
  5. Business performance requirement. If program performance is critical, spends notable time on this table and there is no way to optimize the access, then buffering might be important.
  6. Production server status – buffer/memory status and Database server/application capacity status.

Based on table definition, you know table record size already, then based on above information you collected, you can get a good indication about table size. With change frequency and access frequency, you can get a good indication about change ratio – # of changes/# of reads.

With above information, you can reach decision on buffering. For example:

  1. Small table and seldom changed can be buffered if servers status(buffer and CPU) has no issue.
  2. Table is not small but it is access a lot – table in general should be buffered if change habit is not a concern.
  3. Table is small but change habit is a concern – then this table cannot be buffered generally.
  4. Table cannot be buffered if change habit would cause data inconsistency which business cannot afford. If data is changed well before they are being used, then buffer synchronization delay is not concern anymore.

Changes here include data population, data modification and data deletion. If data is changed well before they are being used, then buffer synchronization delay is not a concern anymore.

In SAP project/development, you might need to buffer an existing local table as well, this is mentioned in following section.

2.3 How do we know whether a SAP table should be buffered in SAP production/support?

In SAP development, most information for table buffering is from business requestor who requests the development. In production box, we have different SAP tools to record table related information. We can get a lot of information related to table size, access habit, change habit and system status information.

The best starting point to analyze table buffering is to use SAP transaction ST10 – table call statistics. ST10 analysis can provide a list of table which should be reviewed here further. Click here to know how to run SAP ST10 to do sap table buffering analysis.

If SAP ST10 states that a table can be buffered, then you need to do more investigation to confirm whether it can be buffered.

  • Get table size – normally refer to disc space but can be refer to number of entries. SAP transaction DB02 can tell you disc space and history. SAP transaction DB20/DB20ORA and SE15 can show you number of entry in a table. DB20/DB20ora report number of table entries from oracle statistics table while SAP SE16 transaction tell you accurate number of entry in a table by accessing each record of the table.
  • Understand how the table is accessed by analyzing table SQL statements Cache via SAP transaction ST04. SQL cache can tell how many seconds the SQL has spent so far, type of SQL operation and total of number of operation. For select/update statement, it can show you the where-clause which you can get clear picture on select-conditions.
  • Run ST02 and ST06 – check table buffer status to see whether there is any contention here. Using ST06 to check CPU status on database server and application server as well as main memory to see whether there is potential concern related to buffering.
  • You need to talk with business owners to understand remaining details of access habit and change habit as well as possible impact from delay of buffer synchronization.
  • You also need to understand the performance requirement – based on current status and time spent on that table, you can know what runtime improvement you would get by buffering the table.

With above information, you can make an informed decision on table buffering. Following the similar process, you can confirm whether you should stop buffering on a table or change buffering type of the tables. Again, ST10 information like number of invalidation and number of database records have several contributors – like se16 transaction would bypass buffer and increased number of rows. It is not safe to make a decision to remove buffering on a table just based on ST10 number of invalidations and number of rows impacted.

3. Further clarification

SAP table buffering does not support secondary index. If a buffered table is big and reading the table is not using primary index, then read a record from such table is consuming more application server CPU and benefit of runtime improvement might not be that significant than read it from database using secondary index. You might need to test to see the improvement before buffer and after buffer to make sure that runtime improvement worth the consumption of CPU and memory in application server side.

SAP application buffering also include number range buffering. Number range buffering is important for application performance. However this is not covered here. I might cover this in future posting.

There are other types of SAP buffer like program buffer etc., those are important to SAP application performance as well. They are configured by SAP basis team. I have covered them in my post – how to run SAP ST02 and use ST02 to do performance analysis on SAP memory and buffer.

We should not confuse SAP buffering with program level data buffering. Data in SAP buffer is available to all SAP programs while program level buffering is only available to a specific program while the program is running normally.

I assume that you have known how to run SAP transaction ST10, SAP transaction ST06, SAP transaction ST04 and SAP transaction DB02. I plan to do a post on ST04 and DB02 in the future.

SAP ST12 Trace SQL performance analysis

This post is to continue my writing on how to tune a SAP program/application performance based on SAP ST12 traces. My previous post is writing about overall SAP program performance tuning process and how to analyze ST12 ABAP trace for SAP program/application performance tuning. Here, I would cover how to analyze SAP SQL trace to do SQL tuning to improve SAP program/application performance from development point view.

1 SAP ST12 trace SQL analysis

You might wonder where you should start to analyze SQL statement performance. My choice is to use “Summarized SQL Statements” of SAP ST12 performance trace and focus on top expensive SQL statements. Please click here for how to navigate to “Summarized SQL Statements” screen from SAP ST12 performance trace.

1.1 Understand data in “Summarized SQL Statements” screen

Following is a portion of SAP “Summarized SQL Statements” screen from a SAP ST12 performance traces. The displayed is sorted by total execution time or duration of a SQL spends during tracing period in default.

Figure 1 – SAP-PERF.CA

Before you analyze SQL performance based on “Summarized SQL Statements” screen, you need to make sure that you understand the data presented in the screen:

– Number of times which a SQL is executed by the program during the trace.

– Number is in percentage which of all table read are identical. Two reads on database table are identical, this means that the same program read the same record two times. So as long as the SQL statement where clause is the same on the same table even they are at different location of the program and different fields are returned, they are still identical. In the figure-1, the second line has a value of “1,200” and “58” in column “execution” and “identical”, this means that 696 of 1,200 times when SQL are executed is identical. 1,200 X 58 / 100 = 696.

– Duration is sum of each individual SQL execution time for number of times under “executions” column. Records are total # of records which database server returns to application server where your program is running.

– Average time per execution for the SQL = duration/Number_of_execution. Average number of records retrieved from database table per execution of the SQL = Records/executions.

– SAP table buffer type. There are no buffer, single record buffer, generic area buffer and full table buffered. Blank means that table is not buffered.

-SAP transparent table/pool/cluster/view name. When it is a cluster table or pool table in source code, “Summarized SQL Statements” would show cluster or pool name instead of the table name. For example, in SQL, you can get change header or item information from table “CDPOS”, in ST12, it would show CDCLS under Obj. name column instead of “CDPOS”.

– This is a “shorten” SQL statement from database SQL cache. You can see full SQL statement by double clicking the SQL statement. You can select the SQL followed by clicking on the in figure-1, SAP would then show you the SQL execution plan which contains full SQL. Several ABAP SQLs can be mapped into one SQL statement in the summary windows. You review the related ABAP source code by clicking in above figure-1 after you place cursor on the line you are interested.

At this moment, I would like to clarify several things further to avoid possible confusion on data displayed in “Summarized SQL Statements” screen showed in figure-1.

Number of execution, identical selection and Number of records returned in “Summarized SQL Statements” is from database server point view not application/program logic point view. For example, Select-for-all SQL statement is just executed once from ABAP program logic point view, but number of execution in “Summarized SQL Statements” screen might be more than 1 depends on number of records in the ABAP internal table and system setting. Also, number of records returned by the database for SELECT-FOR-ALL statement could be more than number of records which the program see since the database interface in the application side would remove duplicated records before data is passed to the program.

Number of records which a program sees could be different from what database server actually processes as well. For example, when database server does a “SUM” operation, it just returns one value/record but database might need to get all needed records to get the total result.

All time here is measured in Microseconds and measured from application server point view. So the database time includes impact from network traffic time and IO system when applicable. There are general guidelines based on average time taken by different type of database data operation sequential read, direct read, insert and commit to see whether there is a database performance issue(including network and IO). However network and IO normally have a broad impact on a SAP system performance instead of cherry-picking one program.

1.2 Analyze Top expensive SQL statements

Following are steps I use to analyze top expensive SQL statements captured in SAP ST12 SQL trace for performance improvement:

  • Check database table access strategy.
  • Review identical access.
  • Review Table buffer strategy.
  • Review number of SQL execution.
  • Review data returned.
  • Review table data volume management strategy.

All above checks/tests, if you do not like term “steps”, can happen to one SQL statement. Or only one check is needed for a particular SQL statement. All depend your knowledge on the business solution, the program, testing case, and the SQL.

1.2.1 Check database table access strategy

This step is to review SQL execution plan to see whether an index or right index is used to read the table. So we can tune SAP program performance by speeding up data retrieval via an index or more appropriate index.

First, you got the SQL execution plan from “Summarized SQL Statements” screen and index details

Figure 2 – SAP-PERF.CA

You can get index details by double clicking on the index name or get all indexes by double-clicking the table name in the execution plan.

Then, you review and analyze execution plan by cross-checking between SQL where-clause statement and execution plan. You also need to check SQL statement in execution plan and corresponding SQL code in the ABAP program. You can have following result:

1. Index is not used and database is using “full table scan” to read database data

This could be due to the fact that fields in where-clause of ABAP SQL code do not “match” fields of any existing index. Or ABAP SQL where-clause (selection criteria) has specified index fields, but the program passes no data to those index fields during the execution. Please notice that field sequence in where clause makes no difference on execution plan for Oracle CBO engine.

To tune SAP program performance in this case, possible solution can be:

  • Use alternative table for the information.
  • Modify SQL where-clause in a way so proper index can be used to read table such as adding index fields etc.
  • Change program to make sure that data is passed to index field(s) in where-clause.

2. Index is used but not correct

This could be due to complexity where-clause in SQL statement and similar index which could confuse Oracle CBO. Here, i am not talking about wrong index choice related to table, system statistics or database setting, which can be quickly fixed via updating table/system statistics or change database setting requiring no ABAP effort.

To fix such issue, the where-clause of the SQL statement needs to be simplified. Or you can use corresponding database hints in ABAP code to influence database CBO index choice for this particular SQL.

3. Index is used but other index might be better

This could be due to the fact that table has several indexes and those indexes have common fields where it is referenced in SQL statement.

You need to use corresponding database hints to tell database that what index should be used, you can review and change table index design or change where-clause based on table indexes picture.

4. Index is used but index itself is not selective

This is normally due to index design. This happens usually to local table or local developed index.

To fix this, you need to redesign the index using “selective” table fields and arrange index fields properly with consideration of SQL query for the table and clustering factor. What is selectivity of a field? It is a measurement against two data: Number of distinctive value for a field and number of record in that table. The higher number of distinctive value for a field, the more selective the field is.

Also selectivity of a field and selectivity of index might not be necessary the same. Sometimes, an index is not selective but a SQL where-clause can be very selective – data histogram can play a difference. For example, if a table has 1,000 records, one field has only two distinctive A and B. If B has 999 entries and A has only 1 entry, then it is very selective to select entry from the table with condition of values equal to “A” assuming the field is the only field of an index. Querying the table using “B” value is not selective.

Now I would like to mention “estimated cost” in the execution plan, the higher it is, the more expensive the SQL is. For CBO (Cost Based Optimizer), the database server always uses an execution plan which has a lower “COST”. But CBO can make a wrong choice. Discussion on this is not focus of this posting.

1.2.2 Review identical access

This is to understand why there are identical accesses in “Summarized SQL Statements” to see whether we can eliminate identical access to tune SAP program performance. You review identical access by reviewing the SQL source code and SQL statement in the execution plan. It is possible one SQL statement in “Summarized SQL Statements” screen can mapped to more than one ABAP SQL statements at different program code locations.

1. More than one SQL statements from the ABAP program are mapped to the SQL statement.

2. Only one SQL statement from the ABAP program is mapped to the SQL statement.

To tune SAP program performance, you can consider following options

  • Consolidate the similar SQL statements. If you need two fields from a record at different program location, it is better to retrieve two fields at one location instead of doing it separately at different program location from program performance point view.
  • Move SQL statement to more appropriate location like move it outside of loop, put it at document head-level instead of line-item level and implement at expected high organization level (such as sales organization) instead of repeating it at every organization at a low level (such as sales area).
  • Retrieve once but keep it for future reference to avoid database table access. There are several SAP ABAP techniques to achieve this.
  • For SELECT-FOR-ALL SQL statement, you can sort the internal table and remove “duplicated records” before the internal table is used in the SQL statement to retrieve data from a table.

Identical access sometimes can represent a significant performance tuning opportunity because it can point to potential repeated execution on the same business function due to improper design or coding. In Figure-1, if we assume that time for each access is same, we can make about 58% performance tuning on 2nd SQL by just removing those identical access.

1.2.3 Review Table buffer strategy

This is to review table buffer status based on “Summarized SQL Statements” screen to see whether we can enable buffer or deactivate buffer to tune SAP program performance. You also can review whether the SQL statement has bypassed SAP buffer based on code and whether the business logic needs to bypass SAP buffer.

Which SQL statement would bypass SAP buffer? Following is the list:

  • Any SQL select statement with a keyword “bypassing buffer”.
  • Any Select with a sub query or with joins
  • Any “count” function ( Count, Min, MAX, SUM, AVG)
  • Group by
  • Select DISTINCT
  • Order By
  • Select for update

Click here for SAP document and examples on ABAP SQL statements bypassing buffer.

If SAP program should not bypass buffer when it access table, then you need to change SAP SQL code so data requested is from application buffer instead of database table.

Only SAP configuration table and parameter table are appropriate candidates for buffering if the table is “seldom” changed and has an “appropriate” size. What is “seldom” and what is “appropriate” table size, there is really no hard cut-off line. The general guideline is we can buffer record if record change rate is under 1% and table size does not exceed 5MB. You might need to select “right” buffer mode based on table size, changes and type of query.

If a buffered table usage is now changed more frequently, this can impact your program performance as well due to buffer synchronization. In this case, you need to disable the buffer.

I might write a post to share my experience on dealing with table buffering in the future.

1.2.4 Review number of SQL execution

Here we review Number of execution of SQL in the summary screen to see whether we can tune SAP program performance by reducing number of execution on the SQL. Further by reviewing number of execution, it might be discovered that a subroutine might be executed more than what is really needed! To reduce number of SQL execution, you can consider similar solutions mentioned in 1.2.3 section of this post.

If there are individual “Insert” or “update” on a table, it would help to tune program performance by using mass operation like mass insert and mass update.

1.2.5 Review data returned

This is to review what information each record contains and how many records are returned by the database server to ensure that only data needed by business operation is returned from database not more and not less. Eliminating un-needed fields of each record can help on performance as well. Reducing selected fields can lead to a new execution plan like getting data only from an index. It can reduce round trips between application server and database server as well. Reducing number of records by adding more specific retrieval condition into SQL where-clause instead of do data filtering in the program level can help SAP program performance.

1.2.6 Review table data volume management strategy

Number of entry in a table has a big impact on database time used to search a record when the table primary index is not being used. It is clear that using index or not would make no performance difference if the table has only several records in the same way that using a calculator or not make no time difference to calculate 1+1. So we need to understand how long a data should be kept in a table to meet business requirement and how long the data is actually kept in the table. If this understanding would result in “significant number” of records being removed from the table, then the SAP program performance would be improved greatly even there is no code change.

Sometimes, you might need to void aggregated operation in database server such as group and sum etc. But sometimes, you need to reduce database server load by moving “group” or “sort” operation from database server to application server. There is no one rule for all in this situation.

Last but not least, you also can review top expensive SQL statement to see whether they are related and can be combined SQL on different tables via join and/or database view this can help on the SAP program performance as well.

2 Clarification

When I talk about improving SAP program performance, I mainly cover this from application side on how we can improve ABAP program code (ABAP and SQL) and ABAP program/business solution design to tune SAP program/application performance.

I am not talking about ABAP program performance from system/network point view like table statistics, table/index storage status (fragment, allocation) etc. So you need to make sure that performance issue is not due to system status (Capacity, OS, DBMS, IO, Network etc). Please refer to performance introduction for details on what influences program performance. If all other programs are working fine in a system while your program is “consistently” performing wore than normal, this can normally means a code/design issue. If performance is becoming worse and worse due to steadily increased volume, the performance issue should be related to design and/or code of the program. There are “general” criteria to say whether database performance can be a concern.

SAP ST12 has combined features from SAP transaction SE30 (ABAP trace) and ST05 (SQL trace). So what I stated here is applicable to ST05 trace (SQL) analysis or SAP SQL tuning.

Should I analyze ABAP trace, SQL trace or both traces? This depends on your situation. If your program spends 99% of time on database side, then you should focus on SQL performance analysis to tune SAP program performance.

I am not talking on how to deal with a one-time SAP program performance incident where program runtime is deviated greatly from normal range That is a performance incident trouble-shooting.

Last not least, you might need to search SAP OSS note for possible solution if you cannot find a solution to a performance issue especially when standard SAP program/table is involved.

How to analyze ST12 ABAP trace to tune SAP program performance

You are asked to work on a SAP ABAP program to make it run faster. You are following the performance process discussed in the post – SAP ABAP program performance tuning process. You have used SAP ST12 transaction to trace program execution. Now you are wondering how to understand SAP ST12 traces collected and do the performance analysis to find out specific performance tuning opportunity. This post would cover:

  1. How to understand ABAP trace of ST12 and
  2. How to analyze ABAP trace for performance tuning.

SQL trace of ST12 would be discussed in separate post.

1 Understand data in SAP ST12 ABAP trace screen

Again, here I assume that you know how to navigate through ST12 trace. SAP ST12 has following evaluation options

Figure 1 – ST12 traces

ST12 trace analysis has two main components normally: ABAP trace and performance(SQL) trace
.

Click “ABA Trace” button, you would get ST12 ABAP trace screen like:

Figure 2 ABAP trace screen

The above screen has two parts vertically – the upper part is overview of runtime distribution. The time distribution tell you program runtime distribution among CPU(ABAP), Database and system. In the figure-2, The program spends 62% of runtime on ABAP operation. The lower part is a hit list showing runtime distribution among ABAP components. Hit list is sorted on “gross time” by default. But you can sort it on other column based on your analysis need.

Now you wonder what are those columns “Call” etc in SAP ST12 ABAP trace screen meaning. Please refer to table 1 for details:

Table 1 Understand ST12 ABAP trace data

Column

Explanation

Call

Specific operation executed captured in the trace – can be a simple ABAP statement or a form/FM etc.

No.

number of times which the object in “call” column is actually executed during the trace.

Gross

total time which “call” takes to execute “No.” times.

Net

time spent by direct ABAP/SQL statements in object under “call” column. For simple ABAP statement – Gross = Net. for a “function module”, “Net” time is a sum of time spent by all direct/simple ABAP/SQL statements in object under “call” column.

Gross(%)

Gross/total-runtime X 100%.

Net(%)

Net/total-runtime X 100%.

Program

Where the “call” is from.

Type

Default “blank” – Call is an ABAP operation.

“DB” – call is aSQL operation.

“SYS” – call is a system operation.

2 Analyze ABAP trace

2.1 Identify “silver bullet” from ST12 ABAP trace

In my experience, performance issues are often caused by top expensive ABAP statements in the program. Addressing those individual ABAP statements usually improve a program performance greatly resulting in meeting business performance requirement. Those top expensive ABAP statements are what I called “silver bullet”.

If you sort SAP ST12 ABAP trace screen by “Net %” column descending, then most expensive ABAP/SQL statements would show up at top of display like below:

Figure 3 ST12 ABAP trace sorted by Net%

To review corresponding ABAP source code detail, you can place the cursor on the line, then click icon in SAP ST12 ABAP trace screen. For example, I click once on 2nd line -“Select LIPS”, then I click . It shows program name, exact source code info as below

Figure 4 – Sample Source code related to ABAP trace

If you have ABAP knowledge, you can understand why the statement is so expensive in its’ context, like sort a unchanged table in a loop etc. Application knowledge with insight on testing case can help you to identify problem and solution quickly, For example, if the testing case is to create one sale order, but top expensive ABAP statement is to read order header table VBAK many times, then this is definitely a design or coding issue.

For “DB” type of call, it would show corresponding SQL summary screen for that location if you click on the link . See following example for Call “Select LIPS”.

On the above screen, you would find important information like program, location, number of execution, number of records etc needed in performance analysis. You also can get SQL execution plan from here. I would cover more in SQL analysis of SAP ST12 trace.

Following is a list of common scenarios on expensive ABAP statement from my experiences:

Table 2 Typical ABAP performance concerns and their solution

Item

Likely solution

Comment

Wrong technique to read internal table

Sort, binary read, using table index.

How to read a table would make “material difference” for a big internal table.

Too many read on internal table from different places

Combine multiple read statements.

Reduce un-necessary visit on the same table.

Sort a table repeatedly

Avoid un-necessary sort.

Sorting a table in a loop or sorting the same table at different locations is an “enemy” for performance.

Too many individual operation on internal table like modification etc

Use mass operation related to internal table.

 

Loop on internal table

Reduce loop, combine loop etc

 

Expensive SQLs

Reduce # of execution, use ‘right’ index, buffer table/data etc.

More details in ST12 SQL trace analysis section.

Others like sleep, wait event

Avoid synchronous RFC calls and hard coded wait and sleep etc.

 

RFC

Tuning remote executed FM/program.

 

 

I would like to point out no matter how efficient a code is, it should not be executed if it is not needed by business. So we should try to avoid executing the code instead of improving the code in such situation. This has been covered below as a separate point for subroutine/form but the idea is applicable here as well.

2.2 Identify extra “subroutine” call based on ST12 ABAP trace

Hopefully, by addressing expensive ABAP statements, the program performance meets requirement. If there is no “silver bullet” detected in ST12 ABAP trace and program performance is short of expectation, what we should do next?

Now, we should pay attention to ABAP subroutines (forms and Function module) calls, we should consider following points–

  • Whether the program needs to execute the subroutine.
  • Whether number of times which the subroutine is executed by the program is justified by business/functional need.

I think you remember our focus is top expensive subroutine calls in this type of analysis.

Figure 5 ABAP trace – validate number of execution

You need application-level knowledge to assess above points. If one function module takes 60 minutes for two executions, it might reduce runtime 30 minutes when number of execution is one. If we remove the function call, it would save 60 minutes.

In my performance tuning experience, I found that a logic/subroutine developed for one country was executed “un-necessary” in another country’s business process, this contributed to performance issue. In another scenario, I found a logic which should be executed once on SAP document head level was executed at line item level – this means if the logic would execute 100 times if the document has 100 line items even the logic only need to execute once for the whole document.

You can use “Call Hierarchy” button to do top down analysis to see where time is spent or bottom-up to see where the call is from. You might find this useful. But I seldom find the need to do this type of analysis.

After all above works (including SQL analysis), performance of the program is normally greatly improved and meets user’s expectation. However there are cases that we cannot meet performance tuning goal and no more meaningful tuning opportunity with all above effort. Then what? Please continue reading.

2.3 Review Program Design and Configuration

Now you can analyze general performance and try to find out what you should do to improve the performance.

You can open ABAP trace summary screen to get an overview of performance on the program execution by clicking “ABAP Trace Summary” button. This would show runtime distribution of program execution. The relation among them is total runtime = database time + ABAP time + system time. Total runtime showed up in ST12 should be almost similar to actual program runtime. If there are big difference between ST total time and actual program runtime, this can be related to setting of ST12 when you did the trace and operating system and the quality of trace might be jeopardized. You might wish to redo the trace.

Please be advised this CPU time is actually the time which the program spends in ABAP logic side which can includes “wait” time not the actual CPU time which the server spends during the program execution.

System time should be minimal – normally under 1%. If this is high, then you need to review system status. You might need to redo the trace when the system is in normal load status.

There are other views like Summary per application component etc.

With overview information from ST12 ABAP summary screen and top expensive subroutine information from ST12 ABAP trace screen, you should be able to establish function area which creates the performance bottleneck. At this point, we need to review program design and underlying function configuration to make performance improvement. Our customer order loading program performance was greatly improved after SAP SD price procedure is simplified such as reducing number of price group and optimizing access sequence via pre-step etc.

Performance issue can be due to overall solution design. In one case, I was asked to approve index request for a local report which mainly produces supply/demand picture for selected materials, It took 10 minutes to give the picture for a single material. To fix this, developer planned to create two indexes on two different SAP tables. In my experience, I know that this local report is similar to standard MM transaction MD04 which takes seconds to return similar result. That information is shared with developer. The program was leveraged related logic from standard SAP MD04 design and performance issue was addressed without creating new indexes.

Coming to this point, the program should be able to meet performance requirement in most cases. However, if performance of transaction still falls short of expectation due to volume, what is next step to make program to meet the performance requirement.

2.4 Break-down the job and Use parallel processing

After all those effort , if the performance is still a concern, there is no ABAP/SQL coding issue, there is no function configuration concern, the design is ideal but performance is still short of the expectation. Now we should consider breaking down the work to smaller tasks which can be handled by multiple processes at the same time – this is called parallel process. Bottom-line is that we should only consider parallel solution when underlying program has a sound design and performance has been well tuned. I would write a post about parallel processing in the future.

2.2 SQL Trace Review

This is to fix inefficient database access based on SQL Summary screen from SAP ST12 “performance trace”, following is SQL summary screen…

If performance trace shows that program spends most of time on database side, it is critical to analyze program performance/SQL trace to identify improvement opportunity. Please Click here for my subsequent post on how to analyze SAP ST12 SQL trace to tune SAP program performance.