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.

If you like this post, you may as well like these too:

1. 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...
2. 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...
3. 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...
4. Rules for Better SQL Programming # Tip 4 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...