Rules for Better SQL Programming # Tip 5

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.