Optimize Database Access From ABAP QL ã /1
Copyright Please note: No part of this Training Session may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or information storage and retrieval systems, for any purpose without the express written permission of SAP America. © SAP America, 2000. All rights reserved. SAP Virtual Classroom™ is a registered trademark of SAP America. ã /2
Course Objectives At the conclusion of this course, you will know: l Five Rules for Writing an ABAP Program with Efficient Database Access. l Performance Check List. ã /3
ABAP Open SQL Overview Performance of Business Transactions R/3 and DBMS Architecture SQL Efficient Database Programming in ABAP ã /4
Performance of Business Transactions General rule: The performance of a business transaction is primarily determined by its DB accesses. DB Application GUI ã /5
ABAP Open SQL Overview Performance of Business Transactions R/3 and DBMS Architecture SQL Efficient Database Programming in ABAP ã /6
R/3 Architecture Central DB (stores all data and application programs) Data transfer between database and application server DBMS processes DB cache Database server Communication from / to user WP Application server Presentation server (workstation) ã /7 Local data. . .
The Optimizer Execution plan l Rule-based l Cost-based ã /8
ABAP Open SQL Overview Performance of Business Transactions R/3 and DBMS Architecture SQL Efficient Database Programming in ABAP ã /9
ABAP SQL Open SQL Native SQL DB interface Embedded SQL Database ã / 10
Solution Set of a SQL Command Data to be transferred SELECT clause Relevant data - the hit list WHERE clause Search area FROM clause Data to be searched only limited by index access One or more tables SELECT field 1 field 2 field 3 field 4 FROM ( table 1 INNER JOIN table 2 ON table 1~field 1 = table 2~field 1 ) WHERE. . . AND field 1 IN ('A', 'B', 'C') AND field 3 LIKE 'T%' ã / 11
ABAP Open SQL Overview Performance of Business Transactions R/3 and DBMS Architecture SQL Efficient Database Programming in ABAP ã / 12
Rules for Open SQL Programming l 5 Rules l Should be as independent of the DBMS as possible ã / 13
Keep the Hit List Small Rule 1: Keep the hit list small ã / 14
Keep the Hit List Small Use a WHERE clause wherever possible SELECT * FROM sflight INTO wa. CHECK wa-fldate(4) = ´ 1998´. WRITE: / wa-carrid, wa-connid, . . . ENDSELECT. Use instead SELECT * FROM sflight INTO wa WHERE fldate LIKE ´ 1998%´. WRITE: / wa-carrid, wa-connid, . . . ENDSELECT. Performance advantage: ã / 15 SELECT *. . . CHECK. ENDSELECT. . . WHERE. 300, 000 msec (for 400 recs. ) 3, 700 msec
Effects of Rule 1 R/3 work process DB work process Network communication DB CPU consumption DB memory consumption Database cache Physical I/O Operating system Database files ã / 16 Database Service processes
Keep the Transferred Dataset Small Rule 2: Keep the set of data to be transferred between the database and the application small ã / 17
Keep the Transferred Dataset Small Field list vs. SELECT * FROM sbook INTO wa WHERE carrid NE 'BA'. WRITE: / wa-customid, wa-class. ENDSELECT. Use instead SELECT customid class FROM sbook INTO (wa-customid, wa-class) WHERE carrid NE 'BA'. WRITE: / wa-customid, wa-class. ENDSELECT. Performance advantage: ã / 18 SELECT column. . ENDSELECT *. . . ENDSELECT. 2. 2 sec (for 21500 records) 6. 7 sec
Keep the Transferred Dataset Small Use data selectively SELECT carrid connid FROM sbook INTO (wa-carrid, wa-connid) WHERE carrid = 'LH'. IF sy-dbcnt > 10. EXIT. ENDIF. WRITE: / wa-carrid, wa-connid ENDSELECT. Use instead SELECT carrid connid FROM sbook UP TO 10 ROWS INTO (wa-carrid, wa-connid) WHERE carrid = 'LH'. WRITE: / wa-carrid, wa-connid, . . . ENDSELECT. Performance msec advantage: ã / 19 SELECT column. . . EXIT. ENDSELECT column. . . UP TO 10 ROWS. 300, 000 34, 000 msec
Keep the Transferred Dataset Small Data transfer Appl. server REPORT XYZ SELECT * FROM SBOOK Appl. server Report result Data transfer from DB server to appl. server 10000 SBOOK records each with 97 bytes required 30 transfers of 32 K packages from the DB server to the appl. server ã / 20 Database server
Keep the Transferred Dataset Small Data transfer Appl. server REPORT XYZ SELECT column 1. . FROM SBOOK Appl. server Report result Data transfer of DB server to appl. server 10. 000 SBOOK records each with 9 bytes required 3 transfers of 32 K packages from the DB server to the appl. server ã / 21 Database server
Keep the Transferred Dataset Small Referencing of table fields SELECT * FROM sflight INTO wa WHERE carrid ='LH'. wa-seatsocc = wa-seatsocc + 1. UPDATE sflight FROM wa. ENDSELECT. Instead of this, use: UPDATE sflight SET seatsocc = seatsocc + 1 WHERE carrid = 'LH'. Performance advantage: SELECT *. . . ENDSELECT. UPDATE. . . SET. . . ã / 22 500. 000 ms (for 100 records) 56. 700 ms
Keep the Transferred Dataset Small Use aggregate functions sum = 0. SELECT loccuram FROM sbook INTO wa-loccuram WHERE fldate LIKE '1998%'. sum = sum + wa-loccuram. ENDSELECT. WRITE: / sum. Instead of this, use: SELECT sum( loccuram ) FROM sbook INTO sum WHERE fldate LIKE '1998%'. WRITE: / sum. Performance advantage: SELECT. . . sum = sum +. . . ENDSELECT. 3. 6 sec (with 21, 500 records) SELECT sum ( price ) INTO 1. 1 sec ã / 23
Keep the Transferred Dataset Small Aggregate functions – proceed with care l Databases can calculate roundings in a different manner to the ABAP runtime system. l Databases can recognize the NULL value, ABAP cannot. n AVG (1, 3, 0, 0) = 1 n AVG (1, 3, NULL) = 2 l Select the right data type for the target field. n n ã / 24 For AVG use data type 'F' (Floating Point). For SUM use a data type which is large enough to incorporate the total so as to avoid an overflow of figures.
Keep the Transferred Dataset Small The "Having" clause SELECT carrid connid fldate MAX( luggweight ) INTO (carrid, connid, fldate, max) FROM sbook GROUP BY carrid connid fldate. CHECK max > 20. WRITE: / carrid, connid, fldate, max. ENDSELECT. SELECT carrid connid fldate MAX( luggweight ) INTO (carrid, connid, fldate, max) FROM sbook GROUP BY carrid connid fldate HAVING MAX( luggweight ) > 20. WRITE: / carrid, connid, fldate, max. ENDSELECT. ã / 25
Effects of Rule 2 R/3 work process DB work process Network communication DB CPU consumption DB memory consumption Database cache Physical I/O Operating system Database files ã / 26 Database Service processes
Keep the Number of Roundtrips Small Rule 3: Keep the number of roundtrips between the database and application small ã / 27
Keep the Number of Roundtrips Small Array operations – for example, with INSERT LOOP AT itab. INSERT INTO dbtab VALUES itab. ENDLOOP. Instead of this, use: INSERT dbtab FROM TABLE itab. * If double lines can appear: INSERT dbtab FROM TABLE itab ACCEPTING DUPLICATE KEYS. IF sy-subrc = 4. . Error handling. . . ENDIF. ã / 28
Keep the Number of Roundtrips Small Avoid multi-way SELECTs l JOINs implemented as views in the ABAP Dictionary l JOINs in ABAP Open SQL l SELECT. . . FOR ALL ENTRIES ã / 29 SELECT * FROM t 1 WHERE. . . SELECT * FROM t 2 WHERE. . . SELECT * FROM t 3 WHERE. . . SELECT * FROM t 4 WHERE. . . SELECT * FROM t 5 WHERE. . . ENDSELECT. SELECT * FROM t 6 WHERE. . . SELECT * FROM t 7 WHERE. . . SELECT * FROM t 8 WHERE. . . ENDSELECT.
Keep the Number of Roundtrips Small View in the ABAP Dictionary SELECT * FROM sflight. SELECT * FROM sbook WHERE carrid = sflight-carrid AND connid = sflight-connid AND fldate = sflight-fldate. IF SY_SUBRC NE 0. WRITE: sbook-carrid, sbook-bookid, . . . ENDIF. ENDSELECT. Instead of this, use: SELECT * FROM sflightsbookview. * View im ABAP-Dictionary WRITE: / sflightsbookview-price, sflightsbookview-paymentsum, . . . ENDSELECT. ã / 30
Keep the Number of Roundtrips Small INNER JOIN in the FROM clause SELECT * FROM sflight INTO wa_sflight. SELECT * FROM sbook INTO wa_sbook WHERE carrid = wa_sflight-carrid AND connid = wa_sflight-connid AND fldate = wa_sflight-fldate. WRITE: / wa_sflight-carrid, wa_sflight-connid, wa_sbook-bookid. ENDSELECT. Instead of this, use: SELECT f~carrid f~connid b~bookid INTO (carrid, connid, bookid) FROM sflight AS f INNER JOIN sbook AS b ON f~carrid = b~carrid AND f~connid = b~connid AND f~fldate = b~fldate. WRITE: / carrid, connid, bookid. ENDSELECT. ã / 31
Keep the Number of Roundtrips Small Inner Join SFLIGHT: CARRID CONNID. . . AA LH LH QF. . . DISTANCE SBOOK: CARRID CONNID. . . 2. 572 6. 658 3. 162 1. 689. . . 0017 0400 0402 0598. . . AA AA AA QF. . . 0017 0018 0598. . . BOOKID 1 2 17 10. . . Join operator Join result table on the database CARRID CONNID. . . DISTANCE CARRID CONNID. . . BOOKID AA AA QF ã / 32 0017 0598 2. 572 1. 689 AA AA QF 0017 0598 1 2 10 Please note: The resulting quantity does not contain any entries for the airline company LH.
Keep the Number of Roundtrips Small LEFT OUTER JOIN in the FROM clause SELECT * FROM sflight INTO wa_sflight. SELECT * FROM sbook INTO wa_sbook WHERE carrid = wa_sflight-carrid AND connid = wa_sflight-connid AND fldate = wa_sflight-fldate. WRITE: / wa_sflight-carrid, wa_sflight-connid, wa_sbook-bookid. ENDSELECT. IF sy-dbcnt = 0. CLEAR wa_sbook-bookid. WRITE: / wa_sflight-carrid, . . . wa_sbook-bookid. ENDIF. ENDSELECT. Instead of this, use: SELECT f~carrid f~connid f~fldate b~bookid INTO (carrid, connid, fldate, bookid) FROM sflight AS f LEFT OUTER sbook AS b ON f~carrid = b~carrid AND f~connid = b~connid AND f~fldate = b~fldate. WRITE: / carrid, connid, fldate, bookid. ENDSELECT. ã / 33
Keep the Number of Roundtrips Small Left Outer Join SFLIGHT: CARRID CONNID. . . AA LH LH QF. . . 0017 0400 0402 0598. . . DISTANCE 2. 572 6. 658 3. 162 1. 689. . . SBOOK: CARRID CONNID. . . AA AA AA QF. . . 0017 0018 0598. . . BOOKID 1 2 17 10. . . Join operator Join result table on the database CARRID CONNID. . . DISTANCE AA AA LH LH QF ã / 34 0017 0400 0402 0598 . . . . 2. 572 6. 658 3. 162 1. 689 CARRID CONNID. . . AA 0017 NULL QF 0598 . . . . BOOKID 1 2 NULL 10 Please note: The resulting set always contains the complete ‘outer' table.
Effects of Rule 3 R/3 work process DB work process Network communication DB CPU consumption DB memory consumption Database cache Physical I/O Operating system Database files ã / 35 Database Service processes
Keep the Search Costs Down Rule 4: Keep the costs of the search down ã / 36
Keep the Search Costs Down Tables and indices Index A 1 2 3 Table Block 1 Record 4 Index B A B Record 2 3 5 7 4 5 6 7 ã / 37 Block 2 Record 5 Record 1 Record 6 Block 3 Record 7 Record 3 C D E F G B E G
Keep the Search Costs Down Use as many EQs as possible SELECT * FROM sbook WHERE carrid = 'AA' AND connid = '0017' AND fldate = '19981205'. . Processing. . . ENDSELECT. ã / 38
Keep the Search Costs Down Use as many EQs as possible SELECT carrid connid fldate bookid custtype orderdate FROM sbook INTO. . . WHERE carrid = 'LH' AND fldate = '19981119' AND orderdate = '19981118'. . Verarbeitung. . . ENDSELECT. SBOOK Key: mandt carrid connid fldate bookid SELECT f~carrid f~connid f~fldate b~bookid b~custtype b~orderdate INTO (carrid, connid, fldate, bookid, custtype) FROM sflight AS f INNER JOIN sbook as b ON f~carrid = b~carrid AND f~connid = b~connid AND f~fldate = b~fldate WHERE f~carrid = 'LH‘ AND fldate = '19981119' AND orderdate = '19981118'. . Verarbeitung. . . ENDSELECT. ã / 39
Keep the Search Costs Down Key to the SBOOK table Mand 1 Carrid Primary index Mand Secondary index Carrid Fldate ã / 40 Fldate 3 2 Connid 4 Connid Orderdate Fldate
Keep the Search Costs Down Replace the inner OR with an IN operator f 0 = x 1 / 41 (f 1 = y 1 OR f 1 = y 2 OR f 1 = y 3). f 0 = x 1 ã AND f 1 IN (y 1, y 2, y 3).
Keep the Search Costs Down Index design l Place fields that are effective in the selection process at the beginning l The following fields are not effective in the selection process: MANDT, BUKRS, GJAHR. n l l The following are effective: BUCHUNGSNUMMER, BELNR, MATNR, KUNNR, Create small indices Avoid overlaps (disjunct indices) Up to 3 indices in each table do not have to be critical Avoid using complex WHERE clauses with IN and OR operators for index fields l You cannot process NOT operators in SELECT using an index l Verify the use of indices (for example, SQL trace) ã / 42
Effects of Rule 4 R/3 work process DB work process Network communication DB work process DB CPU consumption DB memory consumption Database cache Physical I/O Operating system Database files ã / 43 Database Service processes
Remove the Load from the Database Rule 5: Remove the load from the database ã / 44
Remove the Load from the Database l Buffer tables l Avoid repeated reading of data l Is a SELECT needed before a change is made? l ORDER BY vs. SORT l Use the “right” logical database l ==> More ã / 45 scala ble
Remove the Load from the Database ABAP Open SQL and table buffering l What is table buffering? l When should you buffer tables? l What should you bear in mind when programming SQL accesses to buffered tables? ã / 46
Remove the Load from the Database Table buffering – the concept 3 Table buffer SELECT col 1. . FROM T 001 1 PROGRAM XYZ 2 Read data from database if it is not in the table buffer Database server Appl. server RESULT 5 4 ã / 47 Transfer data from the database server to the application server and the table buffer
Remove the Load from the Database Why buffer tables? Table buffer PROGRAMM XYZ 0. 2 - 1 ms Database server SELECT SINGLE col 1. . FROM T 001 Appl. server 8 - 600 ms ã / 48
Remove the Load from the Database Buffering types Full buffering (100%) Generic buffering 1 key field Generic buffering 2 key fields Single-record buffering (partial) key 1 key 2 ã / 49 key 3 data 001 001 002 002 002 003 003 key 2 key 3 data key 2 001 001 002 002 003 003 003 A A B B B C C D A A A B B C C C D D D key 3 data key 2 key 3 001 001 001 002 002 002 003 003 003 003 A A B B B C C D A A A B B C C D D 2 4 1 3 5 1 3 6 8 1 2 3 0 3 5 2 3 6 2 4 2 3 5 8 1 2 3 4 data
Remove the Load from the Database Buffer synchronization I Local buffer is up to date Application server A UPDATE T 001. . . Table buffer . . . R/3 DB interface Communication system Database management system DDLOG ã / 50 Database The database is up to date
Remove the Load from the Database Buffer synchronization II Local buffer is up to date Application server A UPDATE T 001. . . Application server B Table buffer . . . Table buffer Buffer is NOT up to date R/3 DB interface INSERT DDLOG Communication system Database management system DDLOG ã / 51 Database The database is up to date
Remove the Load from the Database Buffer synchronization III Buffer synchronization: every 1 -2 minutes Local buffer is up to date Application server A Application server B Table buffer . . . Buffer is invalidated R/3 DB interface SELECT DDLOG Communication system Database management system DDLOG ã / 52 Database The database is up to date
Remove the Load from the Database Invalidation of the buffer Full buffering (100%) Generic buffering 1 key field Generic buffering 2 key fields Single-record buffering (partial) key 1 key 2 key 3 data Any change invalidates the buffer ã / 53 001 001 002 002 002 003 003 key 2 key 3 data key 2 001 001 002 002 A A B B B C C key 3 data Changes invalidate the corresponding generic areas 001 001 001 002 002 002 key 3 A 2 A 3 C 3 data If there is a change in only one work area, only the one record is invalidated. Other changes invalidate the entire table.
Remove the Load from the Database When should you buffer a table? l You should buffer a table if: n It is read frequently n It is relatively small n It is read much more frequently than it is changed l Possible candidates for buffering n n ã / 54 Control tables and customizing tables “Small” master data tables (100 data records for material master data -> few changes)
Remove the Load from the Database How is a table buffered? l Where? n In the technical settings in the maintenance routine of a table in the ABAP Dictionary. l How? n A decision that is based on the buffering type: w w w ã / 55 Fully buffered Single-record buffering Generic (by specifying the number of key fields for the generic key)
Remove the Load from the Database SQL statements that bypass the table buffer l SELECT. . . BYPASSING BUFFER l SELECT. . . DISTINCT l SELECT. . . COUNT, SUM, AVG, MIN, MAX l SELECT. . . ORDER BY f 1. . . fn l SELECT. . . GROUP BY / HAVING l SELECT. . . FOR UPDATE l SELECT. . . JOIN l WHERE clause contains IS NULL statement l WHERE clause contains subquery l Native SQL statements (EXEC SQL. . ENDEXEC) ã / 56
Remove the Load from the Database Repeated reading of data l This should be avoided because: n n ã / 57 It places an unnecessary burden on the database Different results could be read
Remove the Load from the Database ORDER BY vs. SORT l ORDER BY n . . . does not necessarily mean that certain indexes will be used in the database n . . . often results in large overhead for the database. l Is the data really needed in a certain order? ã / 58
Remove the Load from the Database Logical databases l Pre-defined effective table access n Simplified list creation n Optimization is performed centrally l But use the “right” LDB! ã / 59
Effects of Rule 5 R/3 work process DB work process Network communication DB CPU consumption DB memory consumption Database cache Physical I/O Operating system Database files ã / 60 Database Service processes
Performance Check List l Is the program using SELECT * statements? Convert them to SELECT column 1 column 2 or use projection views. l Are the fields within the SELECT. . WHERE normalized to same domain? Convert data fields in where clause to same field level domain. Ex. ( SELECT FROM MARA…WHERE matnr = fld 2… DATA: fld 2 like mara-matnr) ã / 61
Performance Check List l Are Check Statements for table fields embedded in a SELECT … ENDSELECT loop? Incorporate the CHECK statements into the WHERE clause of the SELECT statement. l Do SELECTS on non-key fields use an appropriate DB index or is the table buffered? Create index for the table in the data dictionary or buffer tables if they are read only or read mostly. SELECT into ITABs on key fields, LOOP ITAB or DELETE to filter on remaining criteria. ã / 62
Performance Check List l Is Program Using nested selects to retrieve data ? . Convert nested SELECT to database views or Inner joins. l Are there SELECTs without WHERE condition against files that grow constantly? . Work to get as many key and non-key fields for each DB file. Select into ITAB on keys, then LOOP or DELETE ITAB to filter remaining non-key fields. ã / 63
Performance Check List l Is the program using Select … Append ITAB…. ENDSELECT technique to fill Internal Table ? . Change the processing to read the data immediately into an internal table. (SELECT VBELN AUART… INTO TABLE IVBAK…) l Is the program using SELECT. . ORDER BY statement? . Data should be read into an internal table first and then sorted, unless there is an appropriate index for the order by field. ã / 64
Performance Check List l Is the programming doing calculations/summations that can be done on the database via SUM, AVG, MIN, MAX functions for the SELECT statement? . Use the calculation capabilities of the database via SELECT SUM… l Is the program inserting/updating or deleting data in dialog mode (not via an update function module)? . Make sure that the program issues COMMIT WORK statements when one or more logical units of work (LUWs) have been processed. ã / 65
Course Objectives l Five Rules for Writing an ABAP Program with Efficient Database Access. l Performance Check List. ã / 66
Скачать презентацию Optimize Database Access From ABAP QL ã 1
cc20d6fe8d977afc03c207e0a2e85a2b.ppt