Transcript No Slide Title

Technique # 1
SELECT * FROM table
SELECT * FROM VBAK …..
WRITE : / VBAK-VBELN, ……..
ENDSELECT.
Instead Use
SELECT VBELN, AUART, BNAME, KUNNR
INTO (VBAK-VBELN, VBAK-AUART, ….)
FROM VBAK …..
WRITE : / VBAK-VBELN, VBAK-AUART,…….
ENDSELECT.
Performance advantage (selecting 1000 records) with only fields
VBELN, AUART, BNAME & KUNNR
SELECT * FROM table 

SELECT column1 ,..
2,910,000 ms
239,000 ms
Technique # 2
SELECT FROM table ... EXIT
SELECT VBELN BNAME …. INTO (VBAK-VBELN,….)
FROM VBAK.
ENDIF.
IF SY-DBCNT > 1000. EXIT.
WRITE : / VBAK-VBELN, VBAK-BNAME,…….
ENDSELECT.
Instead Use
SELECT VBELN, AUART, BNAME, KUNNR
INTO (VBAK-VBELN, VBAK-AUART, ….)
FROM VBAK ….. UPTO 1 ROW.
WRITE : / VBAK-VBELN, VBAK-AUART,…….
ENDSELECT.
Performance advantage
SELECT ... EXIT. ENDSELECT.
SELECT .. UPTO 1 ROWS
 410,000 ms
 368,000 ms
Technique # 3
CHECK inside SELECT ... ENDSELECT
SELECT VBELN AUART … INTO ( VBAK-VBELN, ….)
FROM VBAK.
CHECK VBAK-VBELN+7(1) = ‘9’.
WRITE : / VBAK-VBELN, VBAK-AUART,…….
ENDSELECT.
Instead Use
SELECT VBELN, AUART, BNAME, KUNNR
INTO (VBAK-VBELN, VBAK-AUART, ….)
FROM VBAK …..
WHERE VBAK-VBELN LIKE ‘-------9---‘
WRITE : / VBAK-VBELN, VBAK-AUART,…….
ENDSELECT.
Performance advantage (scanning 57,000 records)
Method I
Method II


27,958,000 ms
3,065,000 ms
Technique # 4
SELECT FROM table ORDER BY
SELECT VBELN BNAME ….. INTO (VBAK-VBELN ,…..)
FROM VBAK WHERE BNAME IN S_BNAME
ORDER BY BNAME
ENDSELECT.
Instead Use
SELECT VBELN, AUART, BNAME, KUNNR
INTO TABLE IVBAK FROM VBAK
WHERE BNAME IN
S_BNAME
SORT IVBAK BY BNAME.
Technique # 5
SELECT & APPEND vs SELECT INTO TABLE
SELECT VBELN AUART …. INTO (IVBAK-VBELN,… )
FROM VBAK WHERE BNAME IN S_BNAME
APPEND IVBAK
ENDSELECT.
Instead Use
SELECT VBELN, AUART, BNAME, KUNNR
INTO TABLE IVBAK FROM VBAK
WHERE BNAME IN
S_BNAME
Technique # 6
NESTED SELECTS vs VIEWS
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.
Instead use DATABASE VIEWS
SELECT * FROM ZV_VBAK
WHERE VBELN BETWEEN .... AND ....
WRITE : ZV_VBAK-VBELN, .....
ENDSELECT.
Technique # 7
Use Tables instead of Views
SELECT * FROM ZV_VBAK
WHERE VBELN BETWEEN .... AND ....
WRITE : ZV_VBAK-VBELN, .....
ENDSELECT.
Instead use DATABASE Tables and use ABAP Code to
Manipulate the data.
SELECT * FROM VBAK
Technique # 8
ABAP CALCULATIONS
vs
SELECT SUM, AVG, MIN, MAX
SELECT MATNR KWMENG MEINS INTO VBQTY
FROM VBAP
WHERE …..
COLLECT VBQTY.
ENDSELECT.
Instead Use
SELECT MATNR SUM(KWMENG) MEINS
INTO TABLE VBQTY FROM
VBAP
WHERE ….. GROUPBY …..
Technique # 9
READING LARGE INTERNAL TABLES
WITHOUT USING BINARY SEARCH
SELECT VBELN KUNNR .... INTO TABLE IVBAK
WHERE ......
......
READ TABLE IVBAK WITH KEY VBELN = ‘00900000090’.
Instead Use
READ TABLE IVBAK WITH KEY VBELN = ‘00900000090’.
BINARY SEARCH ( TRANSPORTING NO FIELDS)
Note :
In order for the BINARY SEARCH to work properly the table must be sorted in
ascending order of the search criteria.
If you want to verify the existence of a record but don’t need any of the fields
from the record, then use the faster variant : BINARY SEARCH (
TRANSPORTING NO FIELDS)
Technique # 10
Moving data from Internal table1 to Internal
table 2
LOOP AT ITAB1.
MOVE ITAB1 TO ITAB2.
APPEND ITAB2.
ENDLOOP.
Instead Use.
Itab2[ ] = itab1[ ].
Note : Both the internal tables must be of same structure
Technique # 10
Appending data from Int. table1 to Int. table 2
LOOP AT ITAB1.
MOVE ITAB1 TO ITAB2.
APPEND ITAB2.
ENDLOOP.
Instead Use.
APPEND LINES OF ITAB1 TO ITAB2.
Technique # 12
DELETING DATA FROM INTERNAL TABLES
LOOP AT IVBAK WHERE BNAME = ‘SMITH’.
DELETE BNAME.
ENDLOOP.
Instead Use
DELETE IVBAK WHERE BNAME = ‘Smith’.
Technique # 13
LOOPING AT INTERNAL TABLES
If you are looping at an internal table just to count the number of
records that fulfill certain criteria then use the following variant of the
loop statement
LOOP AT IVBAK TRANSPORTING NO FIELDS
WHERE BNAME = ‘Smith’.
ADD 1 TO COUNTER.
ENDLOOP.
Technique # 14
Indexes
DB2 uses Cost Based Optimiser
Recommendations
 Use transaction DB05 for table Analsysis with respect to
Indexes.
Technique # 15
DB Commit
DB2 also keeps locks for Read Only Transactions
Recommendations
 Regular commits ( Approximately one commit per
minute)
 Use WITH HOLD option to preserve Cursor Position
across Commits.
Technique # 15
DB Commit
DB2 also keeps locks for Read Only Transactions
Recommendations
 Regular commits ( Approximately one commit per
minute)
 Use WITH HOLD option to preserve Cursor Position
across Commits.