Performance Suite – The Foundations – Part 1

Performance

There are many areas in an ABAP program where small changes to the code can affect dramatic performance increase. Some of the most common are listed here.  Every Developer is responsible for writing code that performs efficiently. Please run SCID/SLIN/SE30 after the completion of the program.

Database Access

Database access should be carefully designed before writing the code; the most efficient access should be chosen.

Master Data and Configuration Data

  1. When a program is based on transaction data, we recommend that you save relevant Master data in an internal table and only access the database upon failed lookup in the internal table.
  2. We recommend that all configurations related data (ex: payment terms, plants and most of tables beginning with letter T, etc.) should be retrieved into internal tables at the appropriate place (may be at beginning of start-of-selection).

SELECT and SELECT SINGLE

When using the SELECT statement, study the key and always provide as much of the left-most part of the key as possible. If the entire key can be qualified, code a SELECT SINGLE not just a SELECT.   If you are only interested in the first row or there is only one row to be returned, using SELECT SINGLE can increase performance by up to 3x.

Note: When select statements return more than one row and if you need only one row use up to 1 row.

Nested SELECTs versus table views or JOINs

Performance of nested SELECT loops is often very poor in comparison to a join.  To improve performance and reduce network load, you should consider creating a view in the data dictionary then using this view to select data, or using a JOIN in the FROM clause in your SELECT statement. Consult the Tech Team regarding database access design.  (Before creating custom views, look for Standard SAP views in the data dictionary).

 

Row-Level Processing of a table

Selecting data into an internal table using an array fetch (INTO TABLE itab) versus a SELECT-ENDELECT loop will give at least a 2x performance improvement.  After the data has been put into the internal data, then row-level processing can be done.

Incorrect:

REFRESH X006.
SELECT * FROM T006 INTO X006.
  APPEND X006.
ENDSELECT
Correct:
SELECT * FROM T006 INTO TABLE X006.

SELECT * versus Selecting individual fields

In general, use a SELECT statement specifying a list of fields instead of a SELECT * to reduce network traffic and improve performance.  For tables with only a few fields the improvements may be minor, but many SAP tables contain more than 50 fields when the program needs only a few.  In the latter case, the performance gains can be substantial.

Incorrect:

SELECT * FROM DD01L
  WHERE DOMNAME LIKE 'CHAR%'
        AND AS4LOCAL = 'A'.
ENDSELECT

Correct:

SELECT DOMNAME FROM DD01L     
 INTO DD01L-DOMNAME          
 WHERE DOMNAME LIKE 'CHAR%'  
       AND AS4LOCAL = 'A'.   
ENDSELECT

FOR ALL ENTRIES IN

When using the FOR ALL ENTRIES IN table addition to the SELECT statement a check must be made before the SELECT to confirm that at least one entry exists in the source table. This must be performed because the effect of FOR ALL ENTRIES IN with an empty table is to read ALL entries in the target database table.

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of
entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the
length of the WHERE clause.

The plus

  • Large amount of data
  • Mixing processing and reading of data
  • Fast internal reprocessing of data
  • Fast

The Minus

  • Difficult to program/understand
  • Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

  • Removing duplicates from the the driver table
  • Sorting the driver table
    If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:
    FOR ALL ENTRIES IN i_tab
    WHERE mykey >= i_tab-low and
    mykey <= i_tab-high.

INDEXES

When planning a select statement FIRST ascertain if the select can use the primary index of the driving table. If this is the case specify the WHERE clause to contain each of the primary key fields in the order that they appear within the key structure.

If the primary key does not match the requirement, then look for a secondary index in the Data Dictionary, which may suite, the requirement. Again, if a secondary key is found that meets the requirement, specify the WHERE clause to contain each of the key fields in the order of the secondary index. When the program is tested the developer must use the SQL trace facility to ensure that the required index is chosen in both the development and consolidation systems. A further check should also be made to ensure that the secondary index exists in the production system.

If no secondary index is found the developer should then proceed to ascertain if there is another route to the data via other tables. An example of this can be seen in the FI area where the table BSEG is supported by separate additional index tables BSID, BSAD, BSIK, BSAK, BSIS, BSAS etc.

If no alternative route can be found, then and only then a new secondary index to the driving table should be considered.  Please consult with the Development Manager/Team Lead and the Database Administrator.

SELECT WITH VIEW

SELECT * FROM DD01L
  WHERE DOMNAME LIKE 'CHAR%'
        AND AS4LOCAL = 'A'.
  SELECT SINGLE * FROM DD01T
    WHERE   DOMNAME    = DD01L-DOMNAME
        AND AS4LOCAL   = 'A'
        AND AS4VERS    = DD01L-AS4VERS
        AND DDLANGUAGE = SY-LANGU.
ENDSELECT.
SELECT * FROM DD01V                     
 WHERE DOMNAME LIKE 'CHAR%'            
       AND DDLANGUAGE = SY-LANGU.      
ENDSELECT.  
 

USE THE AGGREGATE FUNCTIONS

C4A = '000'.
SELECT * FROM T100
  WHERE SPRSL = 'D' AND
        ARBGB = '00'.
  CHECK: T100-MSGNR > C4A.
  C4A = T100-MSGNR.
ENDSELECT.
SELECT MAX( MSGNR ) FROM T100 INTO C4A  
 WHERE SPRSL = 'D' AND                 
       ARBGB = '00'.     
 
 
 
        

Increasing the performance in customer developments in SD:

Customer-specific programs and program enhancements (“User Exits”), certain times, encounters a bad performance. The bad performance is noticed during accesses to the following SAP SD tables: VBAK, VBAP, VMVA, LIKP, LIPS, VBRK, VBRP, VBFA.

The following solutions can be used to improve the performance:

a) Search orders for customer number (field VBAK-KUNNR):

Incorrect:

SELECT FROM vbak WHERE kunnr = …

Correct:

SELECT FROM vakpa WHERE kunde = …

SELECT FROM vbak WHERE vbeln = vakpa-vbeln.

b) Search order items for material number (field VBAP-MATNR):

Incorrect:

SELECT FROM vbap WHERE matnr = …

Correct:

SELECT FROM vapma WHERE matnr = …

SELECT FROM vbap WHERE vbeln = vapma-vbeln

AND posnr = vapma-posnr

2. Accesses to deliveries (tables LIKP, LIPS)

a) Search for deliveries with customer number (field LIKP-KUNNR):

Incorrect:

SELECT FROM likp WHERE kunnr = …

Correct:

SELECT FROM vlkpa WHERE kunde = …

SELECT FROM likp WHERE vbeln = vlkpa-vbeln.

b) Search for delivery items with material number (field LIKP-MATNR):

Incorrect:

SELECT FROM lips WHERE matnr = …

Correct:

SELECT FROM vlpma WHERE matnr = …

SELECT FROM lips WHERE vbeln = vlpma-vbeln

AND posnr = vlpma-posnr

c) Search for deliveries with sales order number (preceding document,

field LIPS-VGBEL):

Incorrect:

SELECT FROM lips WHERE vgbel = …

Correct:

SELECT FROM vbfa WHERE VBELV = … and VBTYP_N = ‘J’

SELECT FROM lips WHERE vbeln = vbfa-vbeln

AND posnr = vbfa-posnn

3. Accesses to invoices (tables VBRK, VBRP)

a) Search for invoices with customer number (“payer”) (field

VBRK-KUNRG):

Incorrect:

SELECT FROM vbrk WHERE kunrg = …

Correct:

SELECT FROM vrkpa WHERE kunde = …

SELECT FROM vbrk WHERE vbeln = vrkpa-vbeln

b) Search for invoice items with material number (field VBRP-MATNR):

Incorrect:

SELECT FROM vbrp WHERE matnr = …

Correct:

SELECT FROM vrpma WHERE matnr = …

SELECT FROM vbrp WHERE vbeln = vrpma-vbeln

AND posnr = vrpma-posnr

c) Search for invoices with delivery number (preceding document, field

VBRP-VGBEL):

Incorrect:

SELECT FROM vbrp WHERE vgbel = …

Correct:

SELECT FROM vbfa WHERE vbtyp_n = ‘M’

AND vbelv = …

SELECT FROM vbrp WHERE vbeln = vbfa-vbeln

AND posnr = vbfa-posnn

d) Search for invoices with order number (preceding document, field

VBRP-AUBEL):

Incorrect:

SELECT FROM vbrp WHERE aubel = …

Correct:

SELECT FROM vbfa WHERE vbtyp_n = ‘M’

AND vbelv = …

SELECT FROM vbrp WHERE vbeln = vbfa-vbeln

AND posnr = vbfa-posnn

 

4. Other accesses in SD:

a) Document flow:

Incorrect:

SELECT vbelv FROM vbfa WHERE vbeln …

In table VBFA only the preceeding document is used to search for

the subsequent document (for example, delivery for order).

Searching the other way makes no sense with this table since the

preceding documents (for example, order for delivery) are stored

directly in the document tables. Thus reading in table VBFA is a

one-way street.

Correct:

SELECT vgbel FROM lips WHERE vbeln = …; or

SELECT vgbel FROM vbrp WHERE vbeln = …; or

SELECT aubel FROM vbrp WHERE vbeln = …

b) Search for shipping unit item with delivery

Incorrect:

SELECT FROM vepo WHERE vbtyp = ‘J’

AND vbeln = i_lips-vbeln

Correct:

SELECT FROM vbfa WHERE vbtyp_n = ‘X’

AND vbelv = i_lips-vbeln

SELECT FROM vepo WHERE venum = vbfa-vbeln

Internal Tables

Make use the 4.x internal table types (Standard, Sorted or Hashed) wherever appropriate.

Reading single records of internal tables

When reading a single record in an internal table, the READ TABLE WITH KEY is not a direct READ.  This means that if the data is not sorted according to the key, the system must sequentially read the table.   Therefore, SORT the table and use READ TABLE WITH KEY BINARY SEARCH for better performance.

The SORT statement

e.g.       SORT xtab BY field1 field2.

Proper care should be taken, when sorting an internal table to set the BY clause as definitively as possible. In the example xtab is sorted only by field1 (Primary) and field2 (secondary), whereas the statement SORT xtab will sort the table by all fields. This is obviously much more resource intensive. The SORT statement should be used instead of APPEND SORTED BY.

Small Internal tables Vs. Complete Internal Tables

In general it is better to minimize the number of fields declared in an internal table.  While it may be convenient to declare an internal table using the LIKE command, in most cases, programs will not use all fields in the SAP standard table.

Copying internal tables

InCorrect:  
                   
REFRESH TAB_DEST.
LOOP AT TAB_SRC INTO TAB_DEST.
  APPEND TAB_DEST.
ENDLOOP.
Correct:                     
TAB_DEST[] = TAB_SRC[].

Modifying a set of lines

              InCorrect:  
                   
LOOP AT TAB.
  IF TAB-FLAG IS INITIAL.
    TAB-FLAG = 'X'.
  ENDIF.
  MODIFY TAB.
ENDLOOP.
               Correct:                     
TAB-FLAG = 'X'.                   
MODIFY TAB TRANSPORTING FLAG      
           WHERE FLAG IS INITIAL. 

Deleting a sequence of lines

              InCorrect:  
DO 101 TIMES.
  DELETE TAB_DEST INDEX 450.
ENDDO.
               Correct:                     
DELETE TAB_DEST FROM 450 TO 550.

Linear search vs. binary

              InCorrect:  
READ TABLE TAB WITH KEY K = 'X'.
              Correct:                     
READ TABLE TAB WITH KEY K = 'X' BINARY SEARCH.

Comparison of internal tables

              InCorrect:  
DESCRIBE TABLE: TAB1 LINES L1,
                TAB2 LINES L2.
IF L1 <> L2.
  TAB_DIFFERENT = 'X'.
ELSE.
  TAB_DIFFERENT = SPACE.
  LOOP AT TAB1.
    READ TABLE TAB2 INDEX SY-TABIX.
    IF TAB1 <> TAB2.
      TAB_DIFFERENT = 'X'. EXIT.
    ENDIF.
  ENDLOOP.
ENDIF.
IF TAB_DIFFERENT = SPACE.
  " ...
ENDIF.
            Correct:  
IF TAB1[] = TAB2[].   
 " ...               
ENDIF.                

Modify selected components

           InCorrect:  
LOOP AT TAB.
 TAB-DATE = SY-DATUM.
 MODIFY TAB.
ENDLOOP.
           Correct:  
WA-DATE = SY-DATUM.                     
LOOP AT TAB.                            
 MODIFY TAB FROM WA TRANSPORTING DATE. 
ENDLOOP.                                

Appending two internal tables

           InCorrect:  
LOOP AT TAB_SRC.
  APPEND TAB_SRC TO TAB_DEST.
ENDLOOP
           Correct:  
APPEND LINES OF TAB_SRC TO TAB_DEST.

Deleting a set of lines

            InCorrect:  
LOOP AT TAB_DEST WHERE K = KVAL.
  DELETE TAB_DEST.
ENDLOOP
            Correct:  
DELETE TAB_DEST WHERE K = KVAL.

The following set of guidelines can be used for the detailed performance analysis of programs and transactions whose performance, when observed, no longer satisfies your requirements.

Before starting the analysis, you have to answer the following questions:

o Can you reproduce the problem (repeatedly)?

o Which data can be used to reproduce the problem?

(for example, retrieving packages with 10, 50 and 100 documents during collective billing).

o Does performance only decrease in the live system or also in the

test system?

o Was performance better in the past or when did the problems start?

o Do you have the necessary authorization for Transactions STAD, ST05, ST06, SE30, SM50 and SM66?

The first analysis step should be to examine the statistical individual records, which can be used to isolate the areas in which performance problems occur in individual programs. To further analyze the problem, use the performance trace to examine SQLs, database accesses, RFCs and lock operations (enqueues) in detail.

If you are still unable to find the problem, the ABAP trace and ABAP debugger are other possible methods for analysis.

1. Transaction STAD (or Transaction STAT if you have a release higher than 4.6C): Analysis of statistical individual records (updated for each transaction).

Statistical information comprises response times, the main memory requirement, database accesses, and so on. The records are saved on the application servers for approximately one day. The statistical records can be grouped by transactions and then analyzed.

On the selection screen, you enter the user name, transaction name or program name as well as the time period to be analyzed. Also note the Server Selection switch. By default, the data is collected by all application servers.

You can use the following option switches to select the display type of the individual record statistics:

  • Show all statistic records, sorted by start time
  • Show all records, grouped by business transaction
  • Show business transaction sums (-> this is recommended for SD transactions)

Statistical analysis:

o Response times

Are the response times the same or do high response times occur sporadically?

You can use the Fcod column, which displays the function code within a transaction, to determine whether high response times only ever occur for one screen within a transaction. The records in question can be selected by double-clicking an individual statistical record. To display all of the details concerning the individual record, choose All details.

 

o High database times (DB req time)

In principle, this is a database problem that can be analyzed using a SQL trace (STO5). You can use the value for ‘kbytes transferred’ or ‘Database rows’ to distinguish between two types of database problems:

– The database time is too high even though relatively little data is read! (Avg.time/row (ms) = sequential read).

The optimal read time is 1 msec per data record for ‘sequential read’).

Select-* clauses check whether all or some of the fields are required in the program. Where clauses, which should only read one row at most in order to check whether certain information exists, for example, should contain the addition ‘UP TO 1 ROWS':

SELECT * FROM dbtable UP TO ROWS WHERE field1 = x1.

ENDSELECT.

– The database time is high because the volume of data transferred is high, but the rate at which data is read (approximately 1 msec per record) is optimal. The Where clause is used to keep the number of records read by the database for each SQL statement as low as possible. If, for example, CHECK statements for table fields are contained in SELECT .. ENDSELECT loops, they should be replaced with a suitable Where clause, if possible.

If there are SELECT statements without a Where clause on tables that are constantly increasing in size, that is, BSEG or VBRK, the program design must be revised.

Is identical data read repeatedly?

To determine this, use the ‘GoTo.Identical Selects’ function in the SQL trace. Here, you have to check whether identical

accesses can be avoided.

o High roll wait times (wait time)

There are probably communication problems here. The RFC trace (ST05) is used to analyze problems of this type.

o High CPU times (CPU time)

High CPU times imply that there are time-consuming calculations in the ABAP code or frequent accesses to the table buffer. Programs with a CPU time greater than 50% should be examined using the ABAP trace or the ABAP debugger.

2 Comments

  1. Glynn Williams July 8, 2010 at 8:34 am · Reply

    Sundar – thank you for taking time to document and share your knowledge – you are a star!

  2. Rainer Schick August 19, 2010 at 7:07 am · Reply

    Hallo

    anbei interessante Links

    Gruss

    Rainer

Leave a Reply