Select Query #1
Wrong
SELECT * FROM sflight INTO xflight.
CHECK xflight-carrid = 'LH '.
CHECK xflight-connid = '0300'.
CHECK xflight-fldate(4) = '2002'.
WRITE: / xflight-fldate.
ENDSELECT.
Right
SELECT * FROM sflight INTO xflight
WHERE carrid = 'LH ' AND
connid = '0300' AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.
Select Query #2
Wrong
SELECT * FROM sflight INTO xflight
WHERE carrid = 'LH ' AND connid = '0300'.
CHECK xflight-fldate(4) = '2002'.
WRITE: / xflight-fldate.
ENDSELECT.
Right
SELECT * FROM sflight INTO xflight
WHERE carrid = 'LH ' AND
connid = '0300' AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.
Tip #2 : Minimize the Amount of Transferred Data
Minimize the amount of data transferred between the database and the application server.
Wrong
SELECT * FROM sflight INTO xflight WHERE carrid = 'LH '
AND connid = '0300'
AND fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.
Right
SELECT fldate FROM sflight INTO (xflight-fldate) WHERE carrid = 'LH '
AND connid = '0300'
AND fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.
Apply UP TO n ROWS.
Wrong
SELECT id name discount FROM scustom INTO (xid, xname, xdiscount)
WHERE custtype = 'B'
ORDER BY discount.
IF sy-dbcnt > 10. EXIT. ENDIF.
WRITE: / xid, xname, xdiscount.
ENDSELECT.
Right
SELECT id name discount
FROM scustom UP TO 10 ROWS
INTO (xid, xname, xdiscount)
WHERE custtype = 'B'
ORDER BY discount.
WRITE: / xid, xname, xdiscount.
ENDSELECT.
or how about using SELECT SINGLE instead of SELECT UP TO 1 ROWS.
Use the UPDATE … SET Statement
Wrong
SELECT * FROM sflight
INTO xflight
WHERE carrid ='LH '.
xflight-seatsocc = xflight-seatsocc + 1.
UPDATE sflight FROM xflight.
ENDSELECT.
Right
UPDATE sflight
SET seatsocc = seatsocc + 1
WHERE carrid = 'LH '.
Use aggregate functions
Wrong
sum = 0.
SELECT seatsocc
FROM sflight INTO xseatsocc
WHERE fldate LIKE '2002%'.
sum = sum + xseatsocc.
ENDSELECT.
WRITE: / sum.
Right
SELECT SINGLE SUM( seatsocc )
FROM sflight INTO sum
WHERE fldate LIKE '2002%'.
WRITE: / sum.
Apply Having Clause
Wrong
SELECT carrid connid fldate MAX( luggweight )
INTO (xcarrid, xconnid, xfldate, max)
FROM sbook
GROUP BY carrid connid fldate.
CHECK max gt 20.
WRITE: / xcarrid, xconnid, xfldate, max.
ENDSELECT.
Right
SELECT carrid connid fldate MAX( luggweight )
INTO (xcarrid, xconnid, xfldate, max)
FROM sbook
GROUP BY carrid connid fldate
HAVING MAX( luggweight ) gt 20.
WRITE: / xcarrid, xconnid, xfldate, max.
ENDSELECT.
Tip #3: Keep the number of round trips between the database and the application server small.
Use high-speed array operations with UPDATE, INSERT, DELETE, MODIFY.
Wrong
LOOP AT itab INTO wa.
INSERT INTO sbook VALUES wa.
ENDLOOP.
Right
INSERT sbook FROM TABLE itab.
Apply the INNER JOIN. Avoid nested SELECT-ENDSELECT loops
Wrong
SELECT * FROM sflight INTO xflight WHERE planetype = '727-200'.
SELECT * FROM sbook INTO xbook
WHERE carrid = xflight-carrid AND
connid = xflight-connid AND
fldate = xsflight-fldate.
WRITE: / xflight-carrid, xflight-connid, xbook-bookid.
ENDSELECT.
ENDSELECT.
Right
SELECT f~carrid f~connid b~bookid
INTO (xcarrid, xconnid, xbookid)
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 planetype = '727-200'.
WRITE: / xcarrid, xconnid, xbookid.
ENDSELECT.
Apply the OUTER JOIN
Wrong
SELECT * FROM sflight INTO xflight WHERE planetype = '727-200'.
SELECT * FROM sbook INTO xbook
WHERE carrid = xflight-carrid
AND connid = xflight-connid
AND fldate = xflight-fldate.
WRITE: / xflight-carrid, xflight-connid, xflight-fldate,
xbook-bookid.
ENDSELECT.
IF sy-dbcnt = 0.
CLEAR xbook-bookid.
WRITE: / xflight-carrid, xflight-connid, xflight-fldate,
xbook-bookid.
ENDIF.
ENDSELECT.
Right
SELECT f~carrid f~connid f~fldate b~bookid
INTO (xcarrid, xconnid, xfldate, xbookid)
FROM sflight AS f LEFT OUTER JOIN sbook AS b
ON f~carrid = b~carrid AND f~connid = b~connid
AND f~fldate = b~fldate.
WHERE planetype = '727-200'.
WRITE: / xcarrid, xconnid, xfldate, xbookid.
ENDSELECT.
Use subqueries
Wrong
SELECT carrid connid MAX( seatsocc )
FROM sflight
INTO (xcarrid, xconnid, max)
GROUP BY carrid connid
ORDER BY carrid connid.
SELECT fldate FROM sflight
INTO yfldate
WHERE carrid = xcarrid AND
connid = xconnid AND
seatsocc = max
ORDER BY fldate.
WRITE: / xcarrid, xconnid, yfldate.
ENDSELECT.
ENDSELECT.
Right
SELECT carrid connid fldate
FROM sflight AS f
INTO (xcarrid, xconnid, xfldate)
WHERE seatsocc IN
( SELECT MAX( seatsocc ) FROM sflight
WHERE carrid = f~carrid AND connid = f~connid )
ORDER BY carrid connid fldate.
WRITE: xcarrid, xconnid, xfldate.
ENDSELECT.
For frequently used INNER JOINs, you can create a database view in the ABAP Dictionary
Wrong
SELECT f~carrid f~connid b~bookid
INTO (xcarrid, xconnid, xbookid)
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: / xcarrid, xconnid, xbookid.
ENDSELECT.
Right
SELECT carrid connid bookid
INTO (xcarrid, xconnid, xbookid)
FROM sflightbook.
WRITE: / xcarrid, xconnid, xbookid.
ENDSELECT.
Tip #4: Keep the Cost of the Search Down
Specify the WHERE clause to keep the number of searches down and create suitable indices if necessary.
Wrong
SELECT bookid FROM sbook INTO xflight
WHERE orderdate = '20020304'.
WRITE: / xbookid.
ENDSELECT.
Right
SELECT bookid
FROM sbook INTO xbookid
WHERE carrid = 'LH ' AND
connid = '0300' AND
fldate = '20020304'.
WRITE: / xbookid.
ENDSELECT.
Make sure that the first n fields of the designated index are stated with EQ within the WHERE clause.
Wrong
SELECT * FROM sflight INTO xflight
WHERE carrid = 'LH ' AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.
Right
SELECT * FROM sflight
INTO xflight
WHERE carrid = 'LH ' AND
connid = '0300' AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.
Replace the inner OR with an IN operator
Wrong
SELECT * FROM sflight
INTO xflight
WHERE carrid = 'LH ' AND
(connid = '0300' OR connid = '0302') AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.
Right
SELECT * FROM sflight
INTO xflight
WHERE carrid = 'LH ' AND
connid IN ('0300', '0302') AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.
You cannot process NOT operators in SELECT using an index.
Wrong
SELECT * FROM sflight
INTO xflight
WHERE carrid <> 'LH ' AND
connid = '0300'.
WRITE: / xflight-fldate.
ENDSELECT.
Right
SELECT * FROM sflight
INTO xflight
WHERE carrid IN ('AA ', 'QM ') AND
connid = '0300'.
WRITE: / xflight-fldate.
ENDSELECT.
Think about optimizer hints if the optimizer fails to find a sound execution plan.
Wrong
SELECT carrid connid cityfrom
FROM spfli INTO (xcarrid, xconnid, xcityfrom)
WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'.
WRITE: / xcarrid, xconnid, xcityfrom.
ENDSELECT.
Right
SELECT carrid connid cityfrom
FROM spfli INTO (xcarrid, xconnid, xcityfrom)
WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'
%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.
WRITE: / xcarrid, xconnid, xcityfrom.
ENDSELECT.
Tip #5. Remove the load from the database.
Check if the table meets the criteria for table buffering. When applying the table buffering check if the table is frequently read, should be relatively small and deferred visibility of changes is acceptable.
Do not apply table buffering for tables which are changed heavily or if the contents of the table must be always up-to-date.
Ensure that you use the correct SELECT STATEMENT. Here are some of the statements which bypass the table buffer.
SELECT ... DISTINCT
SELECT ... COUNT, SUM, AVG, MIN, MAX
SELECT ... ORDER BY f1 ... fn
SELECT ... GROUP BY / HAVING
SELECT ... FOR UPDATE
SELECT ... JOIN
WHERE clause contains IS NULL statement
WHERE clause contains subquery
SELECT ... BYPASSING BUFFER
Avoid reading the same data again and again.
Wrong
SELECT SINGLE * FROM scarr
INTO xcarr
WHERE carrid = 'LH '.
...
SELECT SINGLE * FROM scarr
INTO zcarr
WHERE carrid = 'LH '.
Right
SELECT SINGLE * FROM scarr
INTO xcarr
WHERE carrid = 'LH '.
zcarr = xcarr.
Check whether a SELECT is really needed before an UPDATE is made.
Wrong
SELECT SINGLE * FROM sflight
INTO xflight
WHERE carrid = 'LH ' AND
connid = '0300' AND
fldate = '20021204'.
xflight-seatsocc = 1.
UPDATE sflight FROM xflight.
Right
UPDATE sflight
SET seatsocc = 1
WHERE carrid = 'LH ' AND
connid = '0300' AND
fldate = '20021204'.
Avoid the ORDER BY clause if the desired sorting doesn’t correspond to the index used.
Wrong
SELECT p~airpfrom p~airpto f~fldate p~deptime
INTO xflight
FROM spfli AS p INNER JOIN sflight AS f
ON p~carrid = f~carrid
AND p~connid = f~connid
WHERE p~carrid = 'LH '
ORDER BY p~airpfrom p~airpto f~fldate p~deptime.
WRITE: / xflight-airpfrom, xflight-airpto,
xflight-fldate, xflight-deptime.
ENDSELECT.
Right
SELECT p~airpfrom p~airpto f~fldate p~deptime
INTO TABLE flights
FROM spfli AS p INNER JOIN sflight AS f
ON p~carrid = f~carrid
AND p~connid = f~connid
WHERE p~carrid = 'LH '.
SORT flights BY airpfrom airpto fldate deptime.
LOOP AT flights INTO xflight.
WRITE: / xflight-airpfrom, xflight-airpto,
xflight-fldate, xflight-deptime.
ENDLOOP.
