8.3Meeting the Requirements

As you’ve learned in the previous chapters, an existing ABAP application can only benefit from SAP HANA if it uses the code-to-data paradigm. To avoid any risks, however, you should modify as few parts of the existing system as possible. To ensure portability of the system, you also only want to use SAP HANA views and SQLScript if necessary or if it leads to significant performance gains in comparison with Open SQL.

In the following sections, you’ll learn how to determine the extent to which Program ZR_A4H_CHAPTER8_TOP_CUST uses the SAP HANA database and which modifications you can implement to accelerate the program.

8.3.1Narrowing Down the Problem Using Runtime Statistics

When analyzing Program ZR_A4H_CHAPTER8_TOP_CUST, you start by using Transaction STAD. Within this transaction, you call the runtime statistics for a program execution to analyze the program’s runtime and determine the amount of data processed by the program.

The runtime statistics in Figure 8.4 show that Program ZR_A4H_CHAPTER8_TOP_CUST was executed in 547 seconds (Response Time). This runtime is made up of 475 seconds Processing Time and 71 seconds Database Request Time. This means that the largest portion of the runtime is attributed to the ABAP program itself and not the time needed for database access. However, database access also takes too long for a dialog program.

The lower part of Figure 8.4 shows detailed information on the database accesses. As you can see, a little over 5.2 million records were read within the database request time of 71 seconds.

Runtime Statistics from Transaction STAD

Figure 8.4Runtime Statistics from Transaction STAD

Because the table access statistics (refer to Chapter 7, Section 7.4.1) was activated during the selected execution of the program, you also examine the five tables where the program took the longest time for read accesses (see Figure 8.5). The access time of 71 seconds was almost exclusively used for accesses to table SBOOK (with 5,201,341 records read from this table). Accesses to table SCUSTOM only took 0.02 seconds (with a total of 4,637 records read from this table).

Table Access Statistics

Figure 8.5Table Access Statistics

The preceding analysis results have shown that most of the execution time of the ABAP program wasn’t used within database accesses. Only 13% (71 seconds) of the runtime was used within the database to read about 5.2 million records. In the next steps, you must therefore analyze the ABAP processing and further analyze the database accesses.

8.3.2Detailed Analysis of the ABAP Program Using Transaction SAT

To learn more about the ABAP processing, you now analyze Program ZR_A4H_CHAPTER8_TOP_CUST in more detail using the ABAP runtime analysis in Transaction SAT. Figure 8.6 shows the result of the program execution using Transaction SAT. The program was executed in 545 seconds.

The results of the runtime measurement can be evaluated as follows:

8.3.3Detailed Analysis of Database Accesses

Before further analyzing the program using the SQL trace, you should check the SQL profile of the application and compare this with static code analyses.

Code Analysis Using SQL Monitor

You first use the SAP Code Inspector to examine the performance of the TEST_A4H_BOOK_CHAPTER08 package. The results are then linked to the existing data from the SQL Monitor in Transaction SWLT. In this transaction, runtime data for the entire system is displayed for access to table SBOOK. Figure 8.7 shows that the SELECT statement was executed 33,000 times with an average execution time of 14.8 milliseconds and 1,196 records read. If you scroll to the right of the screen, the Type and Name of Processing Block columns (not displayed in Figure 8.7) show that the SELECT statement is run in the GET_MILES_FOR_CUSTOMER method.

Performance Analyses in Transaction SWLT

Figure 8.7Performance Analyses in Transaction SWLT

The Check Title and Check Message columns also display a Code Inspector check for this method, which indicates that this is a SELECT statement within a loop (with the loop not being present within the same modularization unit as the database access).

When clicking the Additional Information column, the system displays the different levels of the call hierarchy (not shown in this screen). This allows you to navigate easily to the different levels of the call hierarchy where you’ll find the loop in the GET_TOP_CUSTOMERS method. When clicking the Show Check Documentation button (shown in Figure 8.7), a document with optimization tips appears. This documentation contains a description of the problem together with possible optimization measures.

You know now that accesses to table SBOOK are caused by a very frequently executed SQL statement, and you also know where this statement is executed and where to find the loop responsible for its execution. Because you also want to know if the SQL statement is executed with identical values each time, you’ll record an SQL trace.

SQL Trace with Transaction ST05

You run Program ZR_A4H_CHAPTER8_TOP_CUST again while creating an SQL trace. This will show you how often each statement was executed, if there were identical executions, the execution times, the number of data records read, and the text of the SQL statement that was transferred to the database.

Figure 8.8 and Figure 8.9 show you the list of structure-identical SQL statements and the call hierarchy (call stack) for the SQL statement used to access table SBOOK. The Redundancy and Identical columns show that the statements weren’t executed with identical values and that all bookings within a certain time period that weren’t canceled are read for a customer.

SQL Trace: SQL Statements with the Same Structure

Figure 8.8SQL Trace: SQL Statements with the Same Structure

You must execute 4,637 SQL statements for this purpose. Using the stack trace from the main records, you can display how the statement for table SBOOK was used via the ABAP stack. By double-clicking an entry, you can easily navigate between the levels of the call hierarchy.

SQL Trace: Call Hierarchy

Figure 8.9SQL Trace: Call Hierarchy

8.3.4Analysis Result

The analysis of Program ZR_A4H_CHAPTER8_TOP_CUST described in the previous sections has shown that the long runtime can be attributed mainly to a large number of SQL statement and function module executions (particularly for unit conversion). The reason for this is the large number of records that are transferred from the database to the application.

When analyzing the source code, you’ll notice that by using the GET_TOP_CUSTOMERS method, the flight bookings are read and processed separately for each customer. Due to the large number of flight bookings (in our example, more than 5 million bookings were read), the database and table buffer are accessed frequently, and there are many function module calls.

Records are often processed individually if function modules and methods are used or reused that aren’t suitable for mass data. In our example, each customer’s miles are determined using the GET_MILES_FOR_CUSTOMER method to identify the premium customers.

8.3.5Optimization Using Open SQL

In the first step, you’ll try to accelerate the identification of premium customers without using SAP HANA views and SQLScript. For this purpose, you’ll create Program ZR_A4H_CHAPTER8_TOP_CUST_1 and call the GET_TOP_CUSTOMERS_1 method within this program.

The new implementation differs from the original program in the following ways:

Listing 8.1 shows the original implementation of the code for identification of premium customers as pseudo code.

"Selecting customers
SELECT * FROM scustom ...
...
"Determining miles per customer by re
"using the method GET_MILES_FOR_CUSTOMER
CALL METHOD GET_MILES_FOR_CUSTOMER(...)
...
"Selecting the bookings for the customer
SELECT * FROM sbook...
...
"Selecting the connection master data for the
"bookings
SELECT SINGLE * FROM spfli...
...
"Unit conversion per booking
CALL FUNCTION 'UNIT_CONVERSION_SIMPLE'...
...
"Reading the Customizings per booking
CALL METHOD GET_PARAMETER_VALUE(...)
...
"Selecting the master data for the customer
SELECT SINGLE * FROM scustom...
...
ENDSELECT.
...
...
ENDSELECT.

Listing 8.1Original Implementation

Listing 8.2 shows the optimized coding for determining the premium customers.

"Single reading operation for the Customizing
CALL METHOD GET_PARAMETER_VALUE(...)

"Reading all customers, bookings, and master data of the
"connections using a JOIN and a
"field string
SELECT... FROM scustom
INNER JOIN sbook...
INNER JOIN spfli...
WHERE...
...
"Calculating the miles in accordance with the
"Customizing
IF class = 'C'.
lv_miles = ...
ELSEIF class = 'F'.
lv_miles = ...
ELSE.
...
ENDIF.
...
COLLECT ls_miles INTO lt_miles.
ENDSELECT.

"One-time unit conversion per customer and
"for the unit used for this customer
LOOP AT lt_miles INTO ls_miles.
...
CALL FUNCTION 'UNIT_CONVERSION_SIMPLE'...
...
ENDLOOP.

Listing 8.2Coding Optimized with Open SQL

Despite the optimization, premium customers are still identified in ABAP because the logic described in Section 8.2.1 can’t be expressed using Open SQL.

8.3.6Analysis of the First Optimization

You now run Program ZR_A4H_CHAPTER8_TOP_CUST_1, which runs much faster. A runtime analysis using Transaction STAD confirms the positive impact of the modifications (see Figure 8.10). The program is now executed within only 71 seconds. The database portion was reduced to about 25 seconds. However, a large number of records (more than 5.2 million records) are still read from the database.

Runtime Statistics after the First Optimization

Figure 8.10Runtime Statistics after the First Optimization

The ABAP trace (Transaction SAT) clearly shows these improvements. From Figure 8.11, you can see that the UNIT_CONVERSION_SIMPLE function module was called only once per customer (4,637 times) and that the table buffers weren’t accessed as frequently. However, the same number of records was read from the database so that the related load of the database interface remained unchanged.

ABAP Trace after the First Optimization

Figure 8.11ABAP Trace after the First Optimization

The SQL trace in Transaction ST05 shows the improvement as well. A join is now executed only once to transfer all records (more than 5.2 million) to the program in one operation (see Figure 8.12).

SQL Trace after the First Optimization

Figure 8.12SQL Trace after the First Optimization

8.3.7Analysis Result

The analysis using Transactions STAD, SAT, and ST05 shows that despite the adjustment, more than 5.2 million records are still transferred from the database server to the application server. The reason for this is primarily that some of the calculations are done for individual bookings, which are listed here:

In particular, the application of the early-booking factor can only be done for individual bookings because it depends on the time difference between booking date and flight date. To optimize the program further, you have to avoid transferring every individual booking from the database to the application server. Basically, there are two options to reach this goal:

8.3.8Optimizing a Database Procedure

You’ll optimize the program using an AMDP. To do this, you’ll create Program ZR_A4H_CHAPTER8_TOP_CUST_2. This program calls the GET_TOP_CUSTOMERS_2 method, which calls the AMDP GET_TOP_CUSTOMERS method of the ZCL_A4H_CHAPTER8_TOP_CUST_AMDP class implemented in SQLScript (see Listing 8.3). Here, the values are read from the Customizing and stored in variables that are required in the subsequent step. Then the program reads the bookings and calculates the surcharges for the booking classes and the early bookings (values from Customizing). The data are aggregated per customer and unit for the distance flown taking into account the reduction for any discounts. You then implement the conversion of all distances into miles. Subsequently, you determine the top customers and transfer them to the procedure’s return structure. As a result, the system only transfers the required data of the top customers to the ABAP application server.

METHOD get_top_customers
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
USING za4h_c8_params scustom sbook spfli.

declare lv_factor_c decimal := 0;
declare lv_factor_f decimal := 0;
declare lv_earlyb_d integer := 0;
declare lv_earlyb_f decimal := 0;

/* Get the customizing */
select value into lv_factor_c from za4h_c8_params
where mandt = :iv_mandt and name = 'FACTOR_C';
select value into lv_factor_f from za4h_c8_params
where mandt = :iv_mandt and name = 'FACTOR_F';
select value into lv_earlyb_d from za4h_c8_params
where mandt = :iv_mandt and name = 'EARLYB_D';
select value into lv_earlyb_f from za4h_c8_params
where mandt = :iv_mandt and name = 'EARLYB_F';

/* Calculate the miles and read additional data
which is needed later */
lt_miles = select s.mandt as mandt, s.id as customer_id,
s.discount as discount, i.distid as miles_unit,
( case b.class when 'Y' then i.distance
when 'C' then i.distance * :lv_factor_c
when 'F' then i.distance * :lv_factor_f end )
as miles_with_factor,
( case when days_between(b.order_date, b.fldate)
< :lv_earlyb_d then 0
when days_between(b.order_date, b.fldate)
>= :lv_earlyb_d then i.distance * :lv_earlyb_f end )
as miles_earlyb
from scustom as s
inner join sbook as b ON b.mandt = s.mandt
and b.customid = s.id
inner join spfli as i ON i.mandt = b.mandt
and i.carrid = b.carrid and i.connid = b.connid
where s.mandt = :iv_mandt
and b.fldate >= :iv_date_from
and b.fldate <= :iv_date_to
and b.cancelled != 'X';

/* Aggregate the data and consider the discount */
lt_miles_aggregated = select mandt, customer_id,
miles_unit as unit_code,
sum( ( miles_with_factor + miles_earlyb ) * ( 100 -discount ) / 100 ) as unit_value
from :lt_miles
group by mandt, customer_id, miles_unit;

/* Do the conversion */
lt_miles_converted = CE_CONVERSION(
:lt_miles_aggregated,
[ error_handling = 'set to null',
client = :iv_mandt,
family = 'unit',
method = 'ERP',
erp_rate_lookup = 'ERP_DIMENSION_ONLY',
target_unit = 'MI',
source_unit_column = "UNIT_CODE",
output_unit_column = "UNIT_CODE_CONVERTED",
output = 'input,converted,output_unit' ],
[ "UNIT_VALUE" as "UNIT_VALUE_CONVERTED" ]
);
/* Fill the output parameter */
et_top_customer = select top :iv_number customer_id,
s.name, sum(round(unit_value_converted, 0)) as miles,
unit_code_converted as miles_unit
from :lt_miles_converted as c
inner join scustom as s on s.mandt = c.mandt
and s.id = c.customer_id
group by customer_id, name, unit_code_converted
order by miles desc;

ENDMETHOD.

Listing 8.3AMDP GET_TOP_CUSTOMERS Method

8.3.9Analysis of the Second Optimization

The second optimization is once again analyzed using Transaction STAD. Figure 8.13 shows that the runtime is now only 2.3 seconds and is determined almost completely by the time in the database. Only 11 data records were transferred.

Eleven Records Read from the ZEV_A4H_MILES View

Figure 8.13Eleven Records Read from the ZEV_A4H_MILES View

Regarding the table accesses in Figure 8.14, you can see that only 10 records are now read by the ZCL_A4H_CHAPTER8_TOP_CUST_AMDP=>GET_TOP_CUSTOMERS method. This is the aggregated final result.

Runtime Statistics: Table Accesses

Figure 8.14Runtime Statistics: Table Accesses

Due to the smaller result set, the number of calls of the internal database statements in the ABAP program can be dramatically reduced. As you can see from the ABAP trace (Figure 8.15), the program is now executed almost entirely in the database (98%).

ABAP Trace after the Second Optimization

Figure 8.15ABAP Trace after the Second Optimization

The SQL trace in Transaction ST05 (see Figure 8.16) also confirms the good result. This trace also shows that the ZCL_A4H_CHAPTER8_TOP_CUST_AMDP=>GET_TOP_CUSTOMERS method is accessed only once.

SQL Trace after the Second Optimization

Figure 8.16SQL Trace after the Second Optimization

For a detailed analysis of the SQL statement using PlanViz, you now take the call from Transaction ST05, which may look like Listing 8.4. You then run the statement in the SQL Console in SAP HANA Studio.

CALL "ZCL_A4H_CHAPTER8_TOP_CUST_AMDP=>GET_TOP_CUSTOMERS#stb2#20150614202022" ('001',10,'20130701',
'20150630')

Listing 8.4Call of Procedure

The analysis result of PlanViz is displayed in Figure 8.17. As you can see, an analytical search was performed, which means that the Online Analytical Processing (OLAP) engine was used. You can see the execution time and the CPU time in microseconds for each node.

PlanViz

Figure 8.17PlanViz

The fact that the value for CPU time is higher than the value for execution time shows that the respective nodes ran in parallel. This means that several threads started and ran on several CPUs so that about 20 seconds of CPU time were used within the runtime of 2.9 seconds.

[»]Sample Execution Times

Note that our sample program and the optimized versions were run on a small system in the cloud and didn’t use very powerful hardware. If these sample programs were run on a more powerful system with more CPUs, a runtime of about one second or less would be possible for this procedure.

8.3.10Analysis Result

The identification of premium customers was optimized in two steps:

By optimizing both the program and the database access, premium customers can now be identified at much higher speeds. Figure 8.18 shows a graphical representation of the runtimes.

Due to this improvement, the code can now be used in dialog programs, and you benefit from a range of new possibilities and options. You can, for example, use the database procedure for planning and simulation purposes to analyze the impact of changed parameters for mile calculation.

With this sample scenario, we were able to illustrate the following:

You can now also further analyze the other programs from the TEST_A4H_BOOK_CHAPTER08 package and try to accelerate them using your technical options.