Best Practices in ABAP Programming - SELECT Query

Tips# 1:

Always specify your conditions in the Where-clause instead of
checking them yourself with check-statements.
The database system can then use an index (if possible) and the
network load is considerably less.


Example:

Don'ts 

Select Query with CHECK statement will reduce the performance of the query.

SELECT * FROM SBOOK INTO SBOOK_WA.
  CHECK: SBOOK_WA-CARRID = 'LH' AND
         SBOOK_WA-CONNID = '0400'.
ENDSELECT.

Do's

Use WHERE condition Instead.

SELECT * FROM SBOOK INTO SBOOK_WA
  WHERE CARRID = 'LH' AND
        CONNID = '0400'.

ENDSELECT.


Tips# 2:

If you are interested if there exists at least one row of a database  table or view with a certain condition, use the Select ... Up To 1 Rows statement instead of a Select-Endselect-loop with an Exit.

If all primary key fields are supplied in the Where condition you can even use Select Single.
Select Single requires one communication with the database system, whereas Select-Endselect needs two.

Example:

Don'ts

Select Query loop with EXIT statement is not good practice.

SELECT * FROM SBOOK INTO SBOOK_WA
    WHERE CARRID = 'LH'.
  EXIT.
ENDSELECT.

Do's

Use Upto 1 row instead of EXIT statement or use SELECT SINGLE statement to increase the performance.

SELECT * FROM SBOOK INTO SBOOK_WA
  UP TO 1 ROWS
  WHERE CARRID = 'LH'.

ENDSELECT.


SELECT SINGLE * FROM SBOOK INTO SBOOK_WA
  WHERE CARRID = 'LH'.

ENDSELECT.

Tips# 3

If you want to find the maximum, minimum, sum and average value or the count of a database column, use a select list with aggregate functions instead of computing the aggregates yourself.

Network load is considerably less.


Don'ts  

Calculation of a field is not a good practice to be used inside a SELECT loop Statement.

DATA: MAX_MSGNR type t100-msgnr.
MAX_MSGNR = '000'.
SELECT * FROM T100 INTO T100_WA
  WHERE SPRSL = 'D' AND
        ARBGB = '00'.
  CHECK: T100_WA-MSGNR > MAX_MSGNR.
  MAX_MSGNR = T100_WA-MSGNR.
ENDSELECT.


Do's

Use aggregate function and reduce the complexity of SELECT loop.

DATA: MAX_MSGNR type t100-msgnr.
SELECT MAX( MSGNR ) FROM T100 INTO max_msgnr
  WHERE SPRSL = 'D' AND

        ARBGB = '00'.

Tips# 4

Use a select list or a view instead of Select * , if you are only interested in specific columns of the table. Network load is considerably less. 

Example

Don'ts

Using * to select all the fields instead of specific fields reduces the performance during Runtime.

SELECT * FROM DD01L INTO DD01L_WA

  WHERE DOMNAME LIKE 'CHAR%'

        AND AS4LOCAL = 'A'.

ENDSELECT.  
Do's

Select only the specific field needed for processing the program.

SELECT DOMNAME FROM DD01L

  INTO DD01L_WA-DOMNAME

  WHERE DOMNAME LIKE 'CHAR%'

        AND AS4LOCAL = 'A'.

ENDSELECT.

Tips# 5:

Whenever possible, use column updates instead of single-row updates to update your database tables.
Network load is considerably less.


Example

Don'ts


SELECT * FROM SFLIGHT INTO SFLIGHT_WA.
  SFLIGHT_WA-SEATSOCC =
    SFLIGHT_WA-SEATSOCC - 1.
  UPDATE SFLIGHT FROM SFLIGHT_WA.

ENDSELECT.

Do's

UPDATE SFLIGHT

       SET SEATSOCC = SEATSOCC - 1.