Rules for Better SQL Programming

Always try to keep the hit list small by using the where clause were ever required or by describing the full search condition in the where clause.
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.