SAP Consultancy @ SC.in : Rules for Better SQL Programming

Tip #1 :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 &GT; 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.

SAP Consultancy @ SC.in

Wednesday, October 15, 2014

Rules for Better SQL Programming

No comments: