1 / 20

ABAP Performance Tuning

ABAP Performance Tuning. 튜닝의 요소. 기타 10%. System Tuning 20 % (OS,I/O, 메모리, DBMS..). Application Tuning 70 % (SQL, 인덱스,뷰, JOIN,Array, 부분 처리). Statement. ABAP <- DBIF. DBIF <- DBMS. SELECT ... ENDSELECT. wa. array. SELECT ... INTO TABLE. array. array. Statement. ABAP -> DBIF.

phil
Download Presentation

ABAP Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ABAP Performance Tuning

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

  3. Statement ABAP <- DBIF DBIF <- DBMS SELECT ... ENDSELECT wa array SELECT ... INTO TABLE array array Statement ABAP -> DBIF DBIF -> DBMS INSERT, UPDATE, DELETE wa wa wa INSERT, UPDATE, DELETE FROM TABLE array array / wa MODIFY FROM TABLE array wa SELECT ... FOR ALL ENTRIES array array UPDATE ... SET ... WHERE statement statement 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. 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,. ABAP/4 Performance Tuning(Technique # 1) 결론: 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. Bad statement SELECT FROM table ... EXIT Ex) SELECT VBELN BNAME INTO (VBAK-VBELN,.) FROM VBAK. IF SY-DBCNT > 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,. ABAP/4 Performance Tuning(Technique # 2) 결론: 1000 레코드 선택시 SELECT ... EXIT. ENDSELECT.  410,000 ms SELECT .. UPTO 1 ROWS  368,000 ms

  9. Bad statement SELECT FROM table ... CHECK Ex) SELECT VBELN … INTO ( VBAK-VBELN, ….) FROM VBAK. CHECK VBAK-VBELN+7(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,. ABAP/4 Performance Tuning(Technique # 3) 결론: 57,000 레코드 선택시 SELECT ... CHECK. 27,958,000ms SELECT …WHERE clause. 3,065,000ms

  10. 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. ABAP/4 Performance Tuning(Technique # 4)

  11. 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. ABAP/4 Performance Tuning(Technique # 5)

  12. 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임. ABAP/4 Performance Tuning(Technique # 6) Join 문장을 사용 할 수 있음 SAP R3 3.1부터 ABAP SQL에서 JOIN 가능함.

  13. 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 ….. ABAP/4 Performance Tuning(Technique # 7) 합계,평균,최대값,최소값 등 수를 계산할 때 DBMS의 기능을 사용하는 것이 좋다.최적의 logic으로 처리 하기 때문임.

  14. 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) ABAP/4 Performance Tuning(Technique # 8) Large internal table을 처리할 때 binary search transport no fields를 사용 한다.

  15. Bad statement LOOP AT ITAB1. MOVE ITAB1 TO ITAB2. APPEND ITAB2. ENDLOOP. Good statement Itab2[ ] = itab1[ ]. ABAP/4 Performance Tuning(Technique # 9) 같은 구조 internal table간 데이터 이동을 전제로 함.

  16. Bad statement LOOP AT ITAB1. MOVE ITAB1 TO ITAB2. APPEND ITAB2. ENDLOOP. Good statement APPEND LINES OF ITAB1 TO ITAB2. ABAP/4 Performance Tuning(Technique # 10) Internal table내 data append하기

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

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

  19. Bad statement select * from bseg where bukrs= bsik-bukrs and rebzg=bsik-belnr and lifnr=bsik-lifnr and shkzg=‘S’ and koart=‘K’. Good statement select * from bseg where bukrs=bsik-bukrs and rebzg=bsik-belnr. check lifnr=bsik-lifnr. check shkzg=‘S’. check koart=‘K’. ABAP/4 Performance Tuning(Technique # 13) 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확인

More Related