Using SQL statements in ABAP Programming and Database performance

The basic principle for performance in ABAP database operations is that there should be minimal data transferred between the application server and database.

SQL Performance Best Practices in ABAP

Field Selection Strategy

• Select only the fields that are required. Avoid using SELECT * unless you need all fields. However, in specific scenarios where you have multiple SELECT statements in different parts of your program querying the same table but different columns, it may be advisable to use SELECT * because the output is stored in a buffer while your program executes. In this case, subsequent SELECT statements can use the buffered data.

" Good practice - select only required fields
SELECT bukrs, gjahr, belnr
  FROM bkpf
  INTO TABLE lt_documents
  WHERE bukrs = 'US01'.

" Avoid unless all fields needed
SELECT *
  FROM bkpf
  INTO TABLE lt_documents
  WHERE bukrs = 'US01'.

Sorting Data

• When you need output in a sorted manner, it is better to use SORT statement in ABAP rather than ORDER BY in the SQL query, as sorting is typically more efficient when performed on the application server.

" Preferred approach
SELECT bukrs, belnr, gjahr
  FROM bkpf
  INTO TABLE lt_documents
  WHERE bukrs = 'US01'.

SORT lt_documents BY belnr.

Nested SELECT Statements

• Be very careful with nested SELECT statements, especially when large amounts of data are expected as output. This can significantly impact performance due to multiple database round trips.

JOIN vs FOR ALL ENTRIES IN

The comparison between JOIN and FOR ALL ENTRIES IN is not appropriate as both serve different purposes:

• JOIN is used to join two database tables and fetch data directly from the database in a single operation.

• FOR ALL ENTRIES IN is used to join a database table with an internal memory table (internal table). This allows you to use data already present in memory to filter database queries.

Therefore, you cannot use them as alternatives to each other, as they address different data retrieval scenarios.

" Using FOR ALL ENTRIES IN
SELECT bukrs, belnr, gjahr
  FROM bkpf
  INTO TABLE lt_header
  FOR ALL ENTRIES IN lt_selection
  WHERE bukrs = lt_selection-company_code.

Conclusion

Optimizing SQL statements in ABAP requires careful consideration of data transfer, proper field selection, and choosing the right approach for data retrieval. Understanding the differences between various ABAP database access methods helps in writing efficient programs.