Optimize Conventional ABAP code Part 3: Improve program by using join instead of nested SELECT statements

After completing this exercise, you would be able to:

1. Analyze program with code inspector (SCI) to detect performance issues.
2. Decrease the database read time by using joins instead of nested SELECT statements
3. measure the improvement in program with ABAP trace (SAT)

*&---------------------------------------------------------------------*
*& 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 1: Create Program and understand what it does

Create a program and copy the above code in your package ZH2B. Name it as ZH2B_OSQL_2. Activate and execute it.

Task 2: Analyze

Analyze the program with code inspector (SCI) to identify potential performance problems.

1. Create a local check variant for code inspector (name it as ZPERF). In the check variant, activate all performance checks.
   • Save the variant and go back to start screen of SCI tcode
2. Perform an Inspection of your program based on your check variant (name it as ZOSQL)
   • In tcode SCI, in Frame Inspection, enter the name of the inspection and click Create
   • In frame object selection, enter Single, Program and type name of your program
   • In frame Check Variant, enter name of your check variant
   • On the toolbar, click on button Execute (F8)
3. Analyze the result of inspection, navigate to ABAP source code. What is the issue and what can be done?
   • On the toolbar, click on Results to see inspection result.
   • Check the reported problems, double click a message to navigate to ABAP source code.
   • Read check documentation to get hints on how to improve your program

Task 3: Improve

Copy the code of get_data_template to get_data_solution. In subroutine get_data_solution, replace two nested SELECT-loops by one SELECT on both database tables (Join)

• Define one local structure type with all required fields from both tables (name it as lty_s_cust_book). Declare a structure and internal table based on this type (name it as ls_cust_book)
• Remove the two select statements by one select statement. Combine the data from SBOOK and SCUSTOM through an inner join with join condition. Check the fields used in the SELECT statement and use internal table lt_cust_book as target for the select statement.
• Optional: Use aliases for two tables to improve readability and add table names in WHERE clause
• In loop over lt_cust_book, implement calculation of the average days between order date and flight date and fill table ct_customers with the result.
• Make sure data is sorted by column_id. For every customer, calculation is done and new line is added in ct_customers. Then start with new customer.
• Alert: Make sure you add the last customer to ct_customers.
• Activate and test your program. Make sure earlier version and improved version of program deliver same data.

*&---------------------------------------------------------------------*
*& 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_book,
id TYPE scustom-id,
name TYPE scustom-name,
postcode TYPE scustom-postcode,
city TYPE scustom-city,
country TYPE scustom-country,
fldate TYPE sbook-fldate,
order_date TYPE sbook-order_date,
END OF lty_s_cust_book.
* Work Area for Result
DATA ls_customer LIKE LINE OF ct_customers.
* Targets for Select
DATA: lt_cust_book TYPE SORTED TABLE OF lty_s_cust_book WITH NON-UNIQUE KEY id,
ls_cust_book TYPE lty_s_cust_book.

* help variables
DATA lv_count TYPE i.
* Processing
*****************
CLEAR ct_customers.
SELECT c~id c~name c~postcode c~city c~country b~fldate b~order_date
FROM scustom AS c INNER JOIN sbook AS b
ON c~id = b~customid
INTO TABLE lt_cust_book
WHERE b~cancelled = space.
LOOP AT lt_cust_book INTO ls_cust_book.
IF sy-tabix = 1. "first booking of first customer
ls_customer-id = ls_cust_book-id.
ls_customer-name = ls_cust_book-name.
ls_customer-postcode = ls_cust_book-postcode.
ls_customer-city = ls_cust_book-city.
ls_customer-country = ls_cust_book-country.
CLEAR ls_customer-days_ahead.
ELSEIF ls_cust_book-id <> ls_customer-id. "first booking of new customer
ls_customer-days_ahead = ls_customer-days_ahead / lv_count.
INSERT ls_customer INTO TABLE ct_customers.
ls_customer-id = ls_cust_book-id.
ls_customer-name = ls_cust_book-name.
ls_customer-postcode = ls_cust_book-postcode.
ls_customer-city = ls_cust_book-city.
ls_customer-country = ls_cust_book-country.
CLEAR ls_customer-days_ahead.
CLEAR lv_count.
ENDIF.
ls_customer-days_ahead = ls_customer-days_ahead + ( ls_cust_book-fldate -
ls_cust_book-order_date ).
lv_count = lv_count + 1.

ENDLOOP.
* Store last entry in result table
ls_customer-days_ahead = ls_customer-days_ahead / lv_count.
INSERT ls_customer INTO TABLE ct_customers.
* SORT ct_customers BY id. " not needed, already sorted
ENDFORM. "

Task 4: See how much improvement

go to tcode SAT (ABAP trace). Compare the runtime of subroutine get_data_template and get_data_solution

Check the gross runtimes.