ABAP Performance Tuning 1 ABAP Performance Tuning for

ABAP Performance Tuning 1 ABAP Performance Tuning for SAP R/3

튜닝의 요소 기타 10% System Tuning 20 % (OS, I/O, 메모리, DBMS. . ) Application Tuning 70 % (SQL, 인덱스, 뷰, JOIN, Array, 부분 처리) 2 ABAP Performance Tuning for SAP R/3

Array Mode and Workarea Mode Statement ABAP <- DBIF <- DBMS SELECT. . . ENDSELECT wa array SELECT. . . INTO TABLE array ABAP -> DBIF -> DBMS wa wa INSERT, UPDATE, DELETE FROM TABLE array / wa MODIFY FROM TABLE array wa SELECT. . . FOR ALL ENTRIES array statement Statement INSERT, UPDATE, DELETE wa UPDATE. . . SET. . . WHERE 3 ABAP Performance Tuning for SAP R/3

Database architecture 4 ABAP Performance Tuning for SAP R/3

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 5 ABAP Performance Tuning for SAP R/3

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 6 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 1) • Bad statement • Good statement ð SELECT * FROM table ð SELECT col 1, col 2 into(col 1, col 2) FROM table Ex) SELECT * FROM VBAK …. . WRITE : / VBAK-VBELN, ……. . ENDSELECT. 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 col 1, col 2. . 239, 000 ms DB SVR와AP SVR간 데이터 전송 부하가 적고, 데이터 베이스의 I/O횟수를 줄임. 필요한 컬럼을 선택하여 AP SVR의 버퍼에 loading함으로 전체 메모리 사용률을 줄일 수 있음 7 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 2) • Bad statement • Good statement ð SELECT FROM table. . . EXIT ð SELECT col 1, col 2 into(col 1, col 2) FROM table UPTO n ROWS Ex) SELECT VBELN BNAME INTO (VBAK-VBELN, . ) FROM VBAK. Ex) SELECT VBELN, AUART, BNAME, KUNNR IF SY-DBCNT > 1000. EXIT. ENDIF. WRITE : / VBAK-VBELN, VBAK-BNAME, ……. INTO (VBAK-VBELN, VBAKAUART, . ) FROM VBAK …. . UPTO 1 ROWS. WRITE : / VBAK-VBELN, VBAK-AUART, . ENDSELECT. 결론: 1000 레코드 선택시 SELECT. . . EXIT. ENDSELECT. . UPTO 1 ROWS 8 410, 000 ms 368, 000 ms ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 3) • Bad statement • Good statement ð SELECT FROM table. . . CHECK ð SELECT col 1, col 2 into(col 1, col 2) FROM table WHERE Clause Ex) SELECT VBELN … INTO ( VBAK-VBELN, …. ) FROM VBAK. CHECK VBAK-VBELN+7(1) = ‘ 9’. WRITE : / VBAK-VBELN, …… Ex) SELECT VBELN, AUART, BNAME, KUNNR INTO (VBAK-VBELN, VBAK-AUART, . ) FROM VBAK … WHERE VBAK-VBELN LIKE ‘-------9 ---‘. WRITE : / VBAK-VBELN, VBAK-AUART, . ENDSELECT. 결론: 57, 000 레코드 선택시 9 SELECT. . . CHECK. 27, 958, 000 ms SELECT …WHERE clause. 3, 065, 000 ms ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 4) • Bad statement • Good statement ð DBMS sort 사용 ð AP서버 internal table sort 사용 Ex) SELECT VBELN. . INTO (VBAK-VBELN, …. . ) Ex) SELECT VBELN, AUART, BNAME, KUNNR FROM VBAK INTO TABLE IVBAK FROM VBAK WHERE BNAME IN S_BNAME ORDER BY BNAME WHERE BNAME IN S_BNAME. SORT IVBAK BY BNAME. 10 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 5) • Bad statement ð SELECT & APPEND • Good statement ð SELECT INTO TABLE Ex) SELECT VBELN, AUART, BNAME, KUNNR Ex) SELECT VBELN …. INTO (IVBAK-VBELN, … ) FROM VBAK WHERE BNAME IN S_BNAME INTO TABLE IVBAK FROM VBAK WHERE BNAME IN S_BNAME. APPEND IVBAK ENDSELECT. 11 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 6) • Bad statement ð NESTED SELECTS • Good statement ð 데이터 베이스 VIEWS 사용 Ex) SELECT * FROM ZV_VBAK Ex) SELECT VBELN AUART VTWEG VKORG SPART INTO IVBAK FROM VBAK WHERE VBELN BETWEEN. . . AND. . . WHERE VBELN BETWEEN. . AND. . WRITE : ZV_VBAK-VBELN, . . . ENDSELECT. SELECT POSNR MATNR ZMENG MEINS INTO IVBAP FROM VBAP 데이터 베이스 view는 tables join, columns projection, WHERE VBELN = IVBAK-VBELN. rows selection에 의한 logical table임. WRITE : /IVBAK-VBELN, . . . . ENDSELECT. Join 문장을 사용 할 수 있음 SAP R 3 3. 1부터 ABAP SQL에서 JOIN 가능함. 12 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 7) • Bad statement • Good statement ð ABAP CALCULATIONS(Collect) ð DBMS SUM, AVG, MIN, MAX 기능 사용 Ex) SELECT MATNR KWMENG MEINS INTO VBQTY FROM VBAP …. . COLLECT VBQTY. WHERE Ex) SELECT MATNR SUM(KWMENG) MEINS INTO TABLE VBQTY FROM VBAP WHERE …. . GROUPBY …. . ENDSELECT. 합계, 평균, 최대값, 최소값 등 수를 계산할 때 DBMS의 기능을 사용하는 것이 좋다. 최적의 logic으 로 처리 하기 때문임. 13 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 8) • Bad statement • 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’. WHERE. . . . … READ TABLE IVBAK WITH KEY VBELN = ‘ 00900000090’. BINARY SEARCH ( TRANSPORTING NO FIELDS) Large internal table을 처리할 때 binary search transport no fields를 사용 한다. 14 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 9) • Bad statement • Good statement ð LOOP AT ITAB 1. ð Itab 2[ ] = itab 1[ ]. MOVE ITAB 1 TO ITAB 2. APPEND ITAB 2. ENDLOOP. 같은 구조 internal table간 데이터 이동을 전제로 함. 15 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 10) • Bad statement • Good statement ð LOOP AT ITAB 1. ð APPEND LINES OF ITAB 1 TO ITAB 2. MOVE ITAB 1 TO ITAB 2. APPEND ITAB 2. ENDLOOP. Internal table내 data append하기 16 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 11) • Bad statement • Good statement ð LOOP AT IVBAK WHERE BNAME = ‘SMITH’. ð DELETE IVBAK WHERE BNAME = ‘SMITH’. DELETE BNAME. ENDLOOP. Internal table내 data 삭제하기. 17 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 12) • Bad statement • Good statement ð select * from vbap where vblen = vbak-vblen. ð select * from vbap where vblen = vbak- check vbap-pstyv = ‘ZRE 3’. vblen and pstyv= ‘ZRE 3’ and werks=‘ 0100’. check vbap-werks = ‘ 0100’. Transparent table 를 select 할 경우 가능한 모든 key field를 where절에 사용 하라. Bad statement는 Application server의 많은 memory가 이용 된다. 18 ABAP Performance Tuning for SAP R/3

ABAP/4 Performance Tuning(Technique # 13) • Bad statement • Good statement ð select * from bseg where bukrs= bsik-bukrs ð select * from bseg where bukrs=bsik-bukrs and rebzg=bsik-belnr. and lifnr=bsik-lifnr check lifnr=bsik-lifnr. and shkzg=‘S’ check shkzg=‘S’. and koart=‘K’. check koart=‘K’. Pool, cluster table의 data는 위의 statement가 parse되기 전 data 부분을 unpack 시켜야 만 한다. 따라서 해당 logical table의 key field만 where절에 사용하고, 나머지는 check문장에 사용한다. 19 ABAP Performance Tuning for SAP R/3

ABAP Performance Tuning(기 타) • Native SQL 활용 오라클 DB link사용시 SQL join문장 사용시 • SELECT SINGLE을 자주 사용 key를 잘 만들자(index) • Index full scan보다는 table full scan이 오히려 좋다. R 3특성상 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확인 20 ABAP Performance Tuning for SAP R/3