Rules for Better SQL Programming # Tip 2

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.