ABAP Performance Tuning

1
ABAP Performance Tuning
2
??? ??
?? 10
System Tuning 20 (OS,I/O,???,DBMS..)
Application Tuning 70 (SQL,???,?,JOIN,Array,??
??)
3
Array Mode and Workarea Mode
4
Database architecture
5
Rules of Efficient Database Programming (I)

• 1. Keep the hit set small
• specify everything you know in the WHERE clause
• 2. Keep data to be transferred small
• projection views and field lists
• no workarea update
• aggregate functions and group by
• 3. Keep the number of transfers small
• use array changes
• avoid nested selects(possible alternatives
  joins, subselects, explicit cursors)
• 4. Keep the area to be searched small
• let the database use the indexes in an optimal
  way
• create good indexes

6
Rules of Efficient Database Programming (II)

• 5. Take load off the database where possible
• Table buffering
• Avoid repeated reading of data (e.g. use global
  reading module for master data)
• Avoid SELECT before changes
• Avoid restart logic for mass data processing
• Better use open cursor with hold
• Use SORT instead of ORDER BY where possible
• Use DELETE ADJACENT instead of DISTINCT where
  possible
• Use logical databases only if design is
  appropriate
• Avoid dynamic SQL because it is not using
  buffering mechanisms

7
ABAP/4 Performance Tuning(Technique 1)

• Bad statement
• SELECT FROM table
• Ex) SELECT FROM VBAK ..
• WRITE / VBAK-VBELN, ..
• ENDSELECT.

• Good statement
• SELECT col1,col2 into(col1,col2) FROM table
• Ex) SELECT VBELN, AUART, BNAME, KUNNR
• INTO (VBAK-VBELN, VBAK-AUART, .)
• FROM VBAK ..
• WRITE / VBAK-VBELN, VBAK-AUART,.

?? 1000 ??? ??? SELECT FROM table
? 2,910,000 ms(micro seconds)
SELECT col1,col2.. ? 239,000 ms
DB SVR?AP SVR? ??? ?? ??? ??,??? ???? I/O??? ??.
??? ??? ???? AP SVR? ??? loading??? ?? ??? ????
?? ? ??
8
ABAP/4 Performance Tuning(Technique 2)

• Bad statement
• SELECT FROM table ... EXIT
• Ex) SELECT VBELN BNAME INTO (VBAK-VBELN,.)
• FROM VBAK.
• IF SY-DBCNT gt 1000. EXIT. ENDIF.
• WRITE / VBAK-VBELN,
  VBAK-BNAME,.
• ENDSELECT.

• Good statement
• SELECT col1,col2 into(col1,col2) FROM table UPTO
  n ROWS
• Ex) SELECT VBELN, AUART, BNAME, KUNNR
• INTO (VBAK-VBELN, VBAK-AUART, .)
• FROM VBAK .. UPTO 1 ROWS.
• WRITE / VBAK-VBELN, VBAK-AUART,.

?? 1000 ??? ??? SELECT ... EXIT. ENDSELECT.
? 410,000 ms SELECT .. UPTO 1 ROWS
? 368,000 ms
9
ABAP/4 Performance Tuning(Technique 3)

• Bad statement
• SELECT FROM table ... CHECK
• Ex) SELECT VBELN INTO ( VBAK-VBELN, .)
• FROM VBAK.
• CHECK
  VBAK-VBELN7(1) 9.
• WRITE /
  VBAK-VBELN,
• ENDSELECT.

• Good statement
• SELECT col1,col2 into(col1,col2) FROM table
  WHERE Clause
• Ex) SELECT VBELN, AUART, BNAME, KUNNR
• INTO (VBAK-VBELN, VBAK-AUART, .)
• FROM VBAK
• WHERE VBAK-VBELN LIKE -------9---.
• WRITE / VBAK-VBELN, VBAK-AUART,.

?? 57,000 ??? ??? SELECT ... CHECK.
? 27,958,000 ms SELECT WHERE clause. ?
3,065,000 ms
10
ABAP/4 Performance Tuning(Technique 4)

• Bad statement
• DBMS sort ??
• Ex) SELECT VBELN.. INTO (VBAK-VBELN,..)
• FROM VBAK
• WHERE BNAME IN S_BNAME
• ORDER BY BNAME

• Good statement
• AP?? internal table sort ??
• Ex) SELECT VBELN, AUART, BNAME, KUNNR
• INTO TABLE IVBAK FROM VBAK
• WHERE BNAME IN
  S_BNAME.
• SORT IVBAK BY BNAME.

11
ABAP/4 Performance Tuning(Technique 5)

• Bad statement
• SELECT APPEND
• Ex) SELECT VBELN . INTO (IVBAK-VBELN, )
• FROM VBAK
• WHERE BNAME IN S_BNAME
• APPEND IVBAK
• ENDSELECT.

• Good statement
• SELECT INTO TABLE
• Ex) SELECT VBELN, AUART, BNAME, KUNNR
• INTO TABLE IVBAK FROM VBAK
• WHERE BNAME IN S_BNAME.

12
ABAP/4 Performance Tuning(Technique 6)

• Bad statement
• NESTED SELECTS
• Ex) SELECT VBELN AUART VTWEG VKORG SPART
• INTO IVBAK FROM VBAK
• WHERE VBELN BETWEEN ..... AND
  ......
• SELECT POSNR MATNR ZMENG MEINS
• INTO IVBAP FROM VBAP
• WHERE VBELN
  IVBAK-VBELN.
• WRITE /IVBAK-VBELN,
  .......
• ENDSELECT.
• ENDSELECT.

• Good statement
• ??? ??? VIEWS ??
• Ex) SELECT FROM ZV_VBAK
• WHERE VBELN BETWEEN
  .... AND ....
• WRITE ZV_VBAK-VBELN, .....
• ENDSELECT.
• ??? ??? view? tables join, columns projection,
• rows selection? ?? logical table?.

Join ??? ?? ? ? ?? SAP R3 3.1?? ABAP SQL?? JOIN
???.
13
ABAP/4 Performance Tuning(Technique 7)

• Bad statement
• ABAP CALCULATIONS(Collect)
• Ex) SELECT MATNR KWMENG MEINS INTO VBQTY
• FROM VBAP WHERE ..
• COLLECT VBQTY.
• ENDSELECT.

• Good statement
• DBMS SUM, AVG, MIN, MAX ?? ??
• Ex) SELECT MATNR SUM(KWMENG) MEINS
• INTO TABLE VBQTY FROM VBAP
• WHERE .. GROUPBY ..

??,??,???,??? ? ?? ??? ? DBMS? ??? ???? ?? ??.???
logic?? ?? ?? ???.
14
ABAP/4 Performance Tuning(Technique 8)

• Bad statement
• READ TABLE ..WITH KEY.
• Ex) SELECT VBELN KUNNR .... INTO TABLE IVBAK
• WHERE ..........
• READ TABLE IVBAK WITH KEY VBELN
  00900000090.

• Good statement
• READ TABLE ..WITH KEY. BINARY SEARCH (
  TRANSPORTING NO FIELDS)
• Ex) SELECT VBELN KUNNR .... INTO TABLE IVBAK
• WHERE .......
• READ TABLE IVBAK WITH KEY VBELN
  00900000090.
• BINARY SEARCH ( TRANSPORTING NO FIELDS)

Large internal table? ??? ? binary search
transport no fields? ?? ??.
15
ABAP/4 Performance Tuning(Technique 9)

• Good statement
• Itab2 itab1 .

• Bad statement
• LOOP AT ITAB1.
• MOVE ITAB1 TO ITAB2.
• APPEND ITAB2.
• ENDLOOP.

?? ?? internal table? ??? ??? ??? ?.
16
ABAP/4 Performance Tuning(Technique 10)

• Bad statement
• LOOP AT ITAB1.
• MOVE ITAB1 TO ITAB2.
• APPEND ITAB2.
• ENDLOOP.

• Good statement
• APPEND LINES OF ITAB1 TO ITAB2.

Internal table? data append??
17
ABAP/4 Performance Tuning(Technique 11)

• Bad statement
• LOOP AT IVBAK WHERE BNAME SMITH.
• DELETE BNAME.
• ENDLOOP.

• Good statement
• DELETE IVBAK WHERE BNAME SMITH.

Internal table? data ????.
18
ABAP/4 Performance Tuning(Technique 12)

• Good statement
• select from vbap where vblen vbak-
• vblen and pstyv ZRE3 and
  werks0100.

• Bad statement
• select from vbap where vblen vbak-vblen.
• check vbap-pstyv ZRE3.
• check vbap-werks 0100.

Transparent table ? select ? ?? ??? ?? key field?
where?? ?? ??. Bad statement? Application server?
?? memory? ?? ??.
19
ABAP/4 Performance Tuning(Technique 13)

• Bad statement
• select from bseg where bukrs bsik-bukrs
• and rebzgbsik-belnr
• and lifnrbsik-lifnr
• and shkzgS
• and koartK.

• Good statement
• select from bseg where bukrsbsik-bukrs
• and rebzgbsik-belnr.
• check lifnrbsik-lifnr.
• check shkzgS.
• check koartK.

Pool,cluster table? data? ?? statement? parse?? ?
data ??? unpack ??? ? ??. ??? ?? logical table?
key field? where?? ????, ???? check??? ????.
20
ABAP Performance Tuning(? ?)

• Native SQL ??
• ??? DB link???
• SQL join?? ???
• SELECT SINGLE? ?? ??
• key? ? ???(index)
• Index full scan??? table full scan? ??? ??.
• R3??? mandt field? ?? index full scan?
  ???.
• Index? single block read??,table? multi
  block read?.
• Select option? client specified? ??
  mandt field? ABAP???? ?? ???.
• Read only table? buffering? ????.
• Full buffering(?? table),Generic
  buffering(? table)? ??? ? ??.
• SQL Trace? ABAP trace? ??? ????.
• Index????,full scan,DB ? AP?? ?? ?? ??
  ???? ??POINT??