Optimizing Conventional ABAP code Part 2: Improve Open SQL Statements by Using Field Lists Instead of SELECT *

After completing this exercise, you will be able to:
• Analyze a program with ABAP Trace (SAT) to locate performance leaks
• Decrease the runtime for database selects by using field lists instead of SELECT *
• Quantify the improvement with ABAP Trace (SAT)

Analyze the below program:

Create the below report in your package ZH2B, give name of the report as ZH2B_OSQL_1. Analyze the program code to get an idea of its functionality. Activate and execute the program.

*&---------------------------------------------------------------------*
*& Form get_data_template
*&---------------------------------------------------------------------*
FORM get_data_template CHANGING ct_customers TYPE ty_t_customers.
* Declarations
****************
* Work Area for Result

DATA ls_customer LIKE LINE OF ct_customers.
* Targets for Select
DATA: ls_scustom TYPE scustom,
ls_sbook TYPE sbook.
* help variables
DATA lv_count TYPE i.
* Processing
*****************
CLEAR ct_customers.
SELECT * FROM scustom
INTO ls_scustom.
ls_customer-id = ls_scustom-id.
ls_customer-name = ls_scustom-name.
ls_customer-postcode = ls_scustom-postcode.
ls_customer-city = ls_scustom-city.
ls_customer-country = ls_scustom-country.
CLEAR ls_customer-days_ahead.
CLEAR lv_count.
SELECT * FROM sbook
INTO ls_sbook
WHERE customid = ls_scustom-id
AND cancelled = space.
ls_customer-days_ahead = ls_customer-days_ahead +
( ls_sbook-fldate - ls_sbook-order_date ).
lv_count = lv_count + 1.
ENDSELECT.
IF lv_count <> 0.
ls_customer-days_ahead = ls_customer-days_ahead / lv_count.
INSERT ls_customer INTO TABLE ct_customers.
ENDIF.
ENDSELECT.
SORT ct_customers BY id.

ENDFORM. "

2. Analyze the source code. What are the two main parts of data processing? In which blocks are they encapsulated?

1. The program is reading data from the database and computing list of customers in subroutine get_data_template.
2. It is then displaying list of customers in subroutine display

3. From which tables the data is fetched?

SCUSTOM and SBOOK

All columns come from SCUSTOM except one column which is calculated.

ORDER_DATE and FLDATE fields from table SBOOK are used as input. The calculated column returns the average number of days between booking date and flight date.

4. Activate and execute the program.

Task 2: Analyze the Program with ABAP trace

1. Go to tcode SAT. Enter the name of your program and variant.

Press execute and wait.

2. Analyze the trace result.

On the tab Desktop 1, double click on Internal Processing Blocks.

Sort the Hitlist on the right by column Net [microsec] and look at top entry

Repeat same steps with External Processing Blocks,

3. Most expensive processing block is subroutine GET_DATA_TEMPLATE

4. Look for the most expensive data access. It is FETCH from DB table sbook.

Task 3: Improve

Create another program with source code as below

*&———————————————————————*
*& Form get_data_solution
*&———————————————————————*
FORM get_data_solution CHANGING ct_customers TYPE ty_t_customers.
* Declarations
****************
* Types for target fields
TYPES: BEGIN OF lty_s_cust,
id TYPE scustom-id,
name TYPE scustom-name,
postcode TYPE scustom-postcode,
city TYPE scustom-city,
country TYPE scustom-country,
END OF lty_s_cust.
TYPES: BEGIN OF lty_s_book,
fldate TYPE sbook-fldate,
order_date TYPE sbook-order_date,
END OF lty_s_book.
* Work Area for Result
DATA ls_customer LIKE LINE OF ct_customers.
* Targets for Select
DATA: ls_scustom TYPE lty_s_cust,
ls_sbook TYPE lty_s_book.
* help variables
DATA lv_count TYPE i.
* Processing
*****************
CLEAR ct_customers.
SELECT id name postcode city country
FROM scustom
INTO ls_scustom.

ls_customer-id = ls_scustom-id.
ls_customer-name = ls_scustom-name.
ls_customer-postcode = ls_scustom-postcode.
ls_customer-city = ls_scustom-city.
ls_customer-country = ls_scustom-country.
CLEAR ls_customer-days_ahead.
CLEAR lv_count.
SELECT fldate order_date
FROM sbook
INTO ls_sbook
WHERE customid = ls_scustom-id
AND cancelled = space.
ls_customer-days_ahead = ls_customer-days_ahead +
( ls_sbook-fldate – ls_sbook-order_date ).
lv_count = lv_count + 1.
ENDSELECT.
IF lv_count <> 0.
ls_customer-days_ahead = ls_customer-days_ahead / lv_count.
INSERT ls_customer INTO TABLE ct_customers.
ENDIF.
ENDSELECT.
SORT ct_customers BY id.
ENDFORM. “

In this subroutine get_data_solution, only those columns are read from the database which are actually needed.

Only ID, NAME, POSTCODE, CITY, COUNTRY fields are needed from table SCUSTOM

Only FLDATE, ORDER_DATE fields are needed from table SBOOK

Define 2 local structures with only the required fields lty_s_book and lty_s_cust.

Use these type for the data objects ls_scustom and ls_sbook

In the two SELECT statements, replace ‘*’ with a list of required fields.

Activate and test your program.

The data should be same from the original and optimized subroutine.

Task 4: See how much improvement

Repeat your runtime measurement of the new program with ABAP trace (SAT tcode)

Compare the runtimes of get_data_template and get_data_solution.

What is the gross runtime of each subroutine?