Rules for Better SQL Programming # Tip 3

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.