This document describes relevant steps to maximize the effect of ABAP performance tuning of custom coding for an SAP System running on HANA DB.
We will focus on tools and guidelines delivered by SAP.
We will start with a procedure how to find candidates (reports, transactions) that show a significant potential for performance improvements optimizing ABAP coding.
In each section of this document we will discuss different aspects of the optimization starting with the identification of potential candidates and ending with tips and tricks from real live projects.
The sections are
Each of the section can be read individually, but to get most out of this document it is recommended to read them in sequence.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Outlook
In the following section we will discuss the classical programming paradigms and their meaning for programming on SAP HANA.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
There are five performance guidelines for ABAP database operations. Despite all changes in the Database technology that come with SAP HANA, these classic programming paradigms remain valid.
With this in mind, we will put one Key message on top of this chapter
Let us have a look at the rules and their meaning as a rule of thumb
What does this mean for our ABAP code? We will know look at each rule what what they mean in the context of SAP HANA
Rule 1 : Keep the result set small
Rule 2 : Minimize amount of transferred data
Rule 3 : Avoide many small request and selecting not needed columns
Rule 4 : Define and use appropriate secondary indexes
Rule 5 : Keep unnecessary load away from the database
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Outlook
In the next section will will use Transaction ST03N (Workload Analysis) to analyse the workload on our system and to identify reports and transactions, that can benefit from code canges and gain performance from SAP HANA.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
There are several ways to identify reports and transactions that will benefit from code rework.
We will now describe how to use Transaction ST03N to identify reports and transactions that will potentially benefit from SAP HANA.
With our approach we will gain most from SAP HANA if we focus on ABAP programs with high DB load. The tool of choice is transaction ST03N (Workload Analysis).
How to proceed? In the following we will show brief step-by-step procedure
Step 1:
Call transaction ST03N
Switch to Expert mode first.
In the Workload tree choose Total
Servers and select the last full month.
In the Analysis Views choose the Transaction Profile folder and choose Standard
Step 2:
Go to tab “All Data” and download the list to Excel.
You can now manipulate the data, e.g. apply filter and hide or delete not columns you are not interested in.
If you remove columns from the list, it is recommended to keep the following ones:
Step 3:
Now we can check where the runtime comes from.
Create two new colums in your Excel to calculate percentage of DB time and
percentage of change time. Use this formula
To reduce the number of lines in the result add a filter on Trans/Rep. and apply a textfilter with the restriction to all names in customer namespace. This will
limit your selection to all transaction and reports that can be changed by a programmer without the need to modify SAP standard code.
SAP will take care of its own code and provide changes with patches and notes. Certain scenarios will benefit from SAP Standard Accelerators on HANA (Side Car, e.g. CO/PA Accelerator).
Sort the list by
Reports will benefit from HANA if DB time is mainly caused by
To identify these reports restrict the result list to reports/transactions with
You can do this if you apply a filter on both columns using the values given above.
Finally, the result table should look like this
In our example above we have marked these lines that fulfil our requirement on DB time and Change time in yellow.
Now you have created a list of candidates for code optimization. In the next sessions we will have a more detailed look on ABAP programs using SAP tools to identify areas with potential, the possible code changes and the result of these changes.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Coming next
In the next section we will have a look at some tools from SAP that will help us with the analysis of our ABAP code. With this information we can later do changes and also analyze pros and cons of our approach.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Part 4: Analyze and optimize ABAP Code - static code analysis
4.1 SAP Code Inspector
SAP code Inspector is a tool designed to perform static code checks. For shure it will not find everything, but you will see it is a fine tool to find and eleminate common mistakes, that are hidden in your code. It is especially useful if you start working on code that was designed and written years ago and there is a need to find areas for improvement without spending time for a detailed analysis.
How does it work? We will guide you through the tool without going to all facts and features and will concentrate us on the checks that will help us on our journey towards performant ABAP code running on SAP HANA.
Now start SAP Code Inspector and create a new inspection.
Enter transaction code SCI (or /nSCI) and this will lead you to the initial screen
We will run a new Inspection, so enter a name and proceed.
Now choose the object you are interested in. We will analyze a single program, so enter the mandatory data.
You can also check multiple objects, even the contens of a transport request.
Now grab either a pre-defined variant or choose from the list below.
In the first step we will use the four test delivered with 7.40 to identify problematic SELECT statements.
Let's have a brief look at the test we have flagged
Search problematic SELECT* statement
--> This test will find statements selecting all fields. This can be replaced with a field list
Search SELECT . . . FOR ALL ENTRIES-clauses to be transformed
--> Go further and find out, if this can be replaced using a (LEFT OUTER) JOIN
Search SELECT statement with DELETE statement
--> Try to avoid deleting single lines (within a loo), transfor this to an array operation
Search SELECTs in loops across modularization units
--> This will help you to find nested SELECTs hidden in modularization unigs, e.g. in FORMs or ROUTINEs
Now run the inspection and take a look at the result list. In our example, SAP Code Inspector has returned two areas of concern:
Select a line and double click on it to see the details
Again choose one of the messages to see the details
In our case the system detects a SELECT * statement, and from the analysis less than 10% of all fields retreived are used. As this analysis is done by an algorithm, it is always wise to review the analysis and decide based on a code analysis, wich field should be in the field list.
Let's go on, and jump to the affected lines of code. You can do this directly from the result
After this is reviewd and - if possible - fixed return to the result of the analysis and go to the second finding. Here we are informed of a nested SELECT.
Let us have a look at the new checks added to SAP Code Inspector with focus on efficient ABAP programming on SAP HANA.
In two sections SAP has added new checks
1. Section Performance Checks has four new checks
2. Section Robust Programming has two new checks
What does this mean in detail, and what does the check for me? The inline documentation gives us more information:
.............................................................................................................................................................
Performance Check 1.1: Search problematic SELECT * statements
Search for
SELECT * FROM dbtab statements which should be transformed into SELECT comp1 comp2 ... FROM dbtab.
This check searches for SELECT * FROM dbtab statements where less than a specified percentage of the fields are effectively used. The default value for the percentage is 20 percent. If less than 20 percent of the fields are used it is more adequate to select only the used fields.
If single subroutines are specified by the check set, only the coding in these routines is checked.
Check parameters:
Display detail information: If you switch on this parameter you get very detailed information about the access to the result of the select-* statements and the evaluation process.
EFFORT-Parameters
The check delivers the following result messages:
If you have switched on to get detail information, there are the additional messages:
The checks delivers the following test result parameters
....................................................................................................................................................................................................
Performance Check 1.2: Search SELECT .. FOR ALL ENTRIES-clauses to be transformed
Search for
SELECT ... FOR ALL ENTRIES statements which can be joined with other SELECT statements
This check searches for SELECT ... FOR ALL ENTRIES where the input for the for all entries table is the result of another SELECT statement. In this case the two statements can be joined into one statement with an improved performance.
The two SELECT statements can be in different subroutines.
If single subroutines are specified by the check set, only the coding in these routines is analyzed.
Buffered tables, except those with single record buffering, are excluded from the analysis. In this case two SELECT-statements are more adequate
General parameters:
Effort parameters:
The check delivers the following result messages:
If you have switched on to get detail information, there are the additional messages:
.............................................................................................................................................................
Performance Check 1.3: Search SELECT statement with DELETE statement
Search for
SELECT .. INTO TABLE tab statements which are followed by a DELETE statement for the result table tab .
The checks delivers the following test result parameters
......................................................................................................................................................................................................................................
Performance Check 1.4: Search for SELECTs in loops across modularization units
Find database operations in nested loops across modularization units
This check searches for database operations that are nested inside any kind of loop (e.g. LOOP-loop, DO-loop, ...). If in the loop there is a call to a modularization unit (PERFORM, CALL FUNCTION, CALL METHOD), then this call is tracked and it will be analyzed, whether there is a database operation in the called unit. This way, several levels of the call-stack are analyzed (for runtime reasons, there is an upper limit for the call-stack depth).
What you can do:
If a database operation is executed inside a loop for several entries of the internal table that is processed, please check whether it is possible to read or write the needed database lines with one database statement instead. For example, it could be possible to replace the SELECT inside the LOOP with a SELECT ... FROM dbtab FOR ALL ENTRIES IN itab.
General Parameters:
Effort Parameters (determine the effort to correct a problematic statement)
The check delivers the following result messages:
If you have switched on to get detail information, there are the additional messages:
..............................................................................................................................................................
Robust Programming 2.1: Deepooling/Declustering: Search SELECT for Pool/Cluster-Tables w/o ORDER BY
Search for SELECT and OPEN CURSOR statements for pool or cluster tables where no ORDER BY clause is specified.
This situation is problematic because the order of the returned database entries changes if the data base table is modified into a transparent table.
Remark: SELECT SINGLE statement are not analyzed since they are not problematic.
Remark: If the result of the SELECT statement is moved into a sorted internal table, a special message is raised
Messages of the test can be suppressed via the pseudo comment "#EC CI_NOORDER
.............................................................................................................................................................
Robust Programming 2.2: Search problematic statements for unsorted results of SELECT or OPEN CURSOR statements
This check searches for the following statements where the internal table is the result of a SELECT statement without ORDER BY clause. Without ORDER BY clause or other sorting measures the result of the select statement may be unsorted. So the statements may not work properly.
Check parameters:
The problematic statements are:
What you can do:
There are two (three) possibilities:
Remark: The SELECT statement and the critical statement can be in different subroutines.
.............................................................................................................................................................
If you want to learn more about SAP Code Inspector follow these links
http://wiki.sdn.sap.com/wiki/display/ABAP/Code+Inspector
http://www.sap.com/australia/campaign/es-newsletter-q4/code_inspector.epx
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Coming next
In the next section we will continue with will have a look at Transaction /nT05 (SQL Trace) and how SQL Trace will help us on our journey to performant ABAP on HANA programming.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Part 5: DB Performance Analysis with SQL Trace
If you experience slow transaction step is mainly caused by one or more long running SQL statements. To get to the root cause of this problem, do an SQL trace of the process step.
For this start ST05 Aattention: The trace is only performed for the instance you are currently logged on, so check Transaction SM51/SM51 before you start). Call Transaction /nST05 and activate the SQL trace via the ‘Activate Trace with Filter’. In case you have to trace another user and not your own, add this information to this popup.
Remark: You should always use filter criteria, to (a) keep the number of collected data low and (b) avoid unneccessary load on the system. And always Deactivate Trace after you have finished collecting the required data.
If the configuration is done, execute the long running statement.
As soon as you have performed the trace, switch of the tracing (Deactivate Trace) and go to Display Trace. Choose your trace file and display the summarized list.
Sort the lis by e.g. Time to see the time consuming statements.
Select the one you want to get more information and click on the SELECT statement to see the details of the WHERE clause.
Click on the ‘Explain’ button to see the execution plan of the statement as it is executed by the DB. You can see, if and which index/indices have been used
In our example no index was choosen and a full table scan was done.
No take appropriate actions and find out, what can be done to optimize this statement.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Coming next
In the next section we will continue with will have a look at Transaction /nSAT (ABAP Trace) and how ABAP Trace will help us on our journey to performant ABAP on HANA programming.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 |