Optimize Conventional ABAP code Part 4: Improve Open SQL statements by buffering data

The aim of this assignment is:

• to analyze ABAP program with SQL trace (ST05) and to detect repeated SELECTs
• to decrease the database runtime by buffering data in internal tables
• to check how much improvement in ABAP trace (SAT)

Task 1: Create program

Copy the below code to create new program in your package ZH2B (name it as ZH2B_OSQL_3). Activate and execute it.

*&———————————————————————*
*& Form get_data_template
*&———————————————————————*
FORM get_data_template 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. “

Task 2: Analyze

Go to tcode ST05 in new session. Choose activate Trace with Filter.

Enter your user and program name and press Enter.

Return to another session where your Program is open

Execute it.

As soon as possible, return to SQL trace and choose Deactivate.

Go to Display Trace, and Execute to see the trace result.

From the menu choose Trace list -> Summarize Trace by SQL Statement.

Sort the list by column Duration and identify most expensive statement.

Sort the list by column Executions and identify the statement that has been repeated most often

Click on statement and choose Display call positions in ABAP program from tool bar.

Task 3: Improve

Copy the source code of get_data_template to subroutine get_data_solution. In subroutine get_data_solution, replace the two nested SELECT loops by two array fetch on SCUSTOM and SBOOK and 2 nested loops over internal tables

• define the two internal tables (name as lt_scustom and lt_sbook) with line types lty_s_book and lty_s_cust. Make sure you define the tables as sorted tables with key ID or customid

Why it is necessary to define two tables as sorted tables?

Otherwise the runtime environment will not be able to optimize the loop over these tables and lot of runtime is lost in searching necessary entries.

Before the two SELECT loops, implement two SELECT statements to read all customers and all non cancelled bookings into internal tables. Replace SELECT loops with loops over internal tables.

Activate and test your program. Make sure the data delivered is same as the original program.

Task 4: See how much Improvement

Perform runtime measurement with ABAP trace (SAT). Compare the gross runtimes of both the subroutines

*&———————————————————————*
*& 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,
customid TYPE sbook-customid,
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: lt_scustom TYPE SORTED TABLE OF lty_s_cust WITH NON-UNIQUE KEY id,
ls_scustom TYPE lty_s_cust,
lt_sbook TYPE SORTED TABLE OF lty_s_book WITH NON-UNIQUE KEY customid,
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 TABLE lt_scustom.
* ORDER BY id no improvement, sorting on Appl. server more efficient
SELECT customid fldate order_date
FROM sbook
INTO TABLE lt_sbook
WHERE cancelled = space.
* ORDER BY customid no improvement, sorting on Appl. server more efficient
LOOP AT lt_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.
LOOP AT lt_sbook INTO ls_sbook
WHERE customid = ls_scustom-id.
ls_customer-days_ahead = ls_customer-days_ahead + ( ls_sbook-fldate
– ls_sbook-order_date ).
lv_count = lv_count + 1.
ENDLOOP.
IF lv_count > 0.
ls_customer-days_ahead = ls_customer-days_ahead / lv_count.
INSERT ls_customer INTO TABLE ct_customers.
ENDIF.
ENDLOOP.
* SORT ct_customers BY id. ” already sorted
ENDFORM.