Performance Tuning of ABAP report program – PART 1
Improving the performance of an ABAP report program
involves several programming techniques that optimize memory usage, reduce
processing time, and minimize database access. Here are some strategies to
improve performance:
1. Efficient
Database Access
Select only required fields: Always select
only the fields you need instead of using SELECT. This reduces the amount of
data transferred between the database and the application server.
abap
SELECT matnr, maktx FROM mara INTO TABLE
lt_mara WHERE matnr IN lt_matnr.
Use FOR ALL ENTRIES carefully: Ensure that the
internal table used in the FOR ALL ENTRIES clause is not empty to avoid
retrieving the entire dataset from the database.
abap
IF lt_matnr IS NOT INITIAL.
SELECT matnr, maktx FROM mara INTO TABLE
lt_mara FOR ALL ENTRIES IN lt_matnr WHERE matnr = lt_matnrmatnr.
ENDIF.
Avoid nested SELECT statements: Instead of
querying the database in a loop, use a single query to fetch the data.
Use JOIN instead of multiple SELECT: Combining
tables in a single query with a JOIN is more efficient than performing multiple
SELECT statements.
abap
SELECT a~matnr,
b~maktx FROM mara AS a
INNER JOIN makt AS
b ON a~matnr = b~matnr INTO TABLE lt_data.
2. Internal
Table Optimization
Choose the appropriate internal table type:
Use the right type of internal table based on the operations you need to
perform.
Standard Table: For simple data storage and
sequential processing.
Sorted Table: For tables that need to be
sorted by a key, providing faster access for binary searches.
Hashed Table: For large datasets requiring
fast key-based access (constant time complexity).
Use SORT and BINARY SEARCH: Sorting an
internal table and using BINARY SEARCH improves performance when searching
large tables.
abap
SORT lt_data BY matnr.
READ TABLE lt_data WITH KEY matnr =
lv_matnr BINARY SEARCH.
3. Minimize
Memory Consumption
Clear unnecessary data: Free up memory by
clearing or refreshing internal tables when they are no longer needed.
abap
CLEAR lt_data.
REFRESH lt_data.
Avoid deep structures when not necessary: Keep
data structures simple to avoid excessive memory consumption.
4. Use Parallel
Processing (RFC)
Distribute the workload across different
processors using parallel processing techniques like CALL FUNCTION with STARTING
NEW TASK. This allows long running reports to be processed faster.
abap
CALL FUNCTION 'YOUR_FUNCTION' STARTING NEW
TASK 'TASK1' PERFORMING task_finished ON END OF TASK.
5. Buffering
Leverage table buffering: For frequently
accessed static data, enable buffering at the dictionary level to reduce
database hits. This can be configured in the technical settings of the table.
Buffer reads: Instead of fetching the same
data from the database repeatedly, store the data in a buffer for reuse.
6. Optimize
Looping Structures
Minimize nested loops: Nested loops result in
exponential complexity and should be avoided wherever possible.
abap
LOOP AT lt_outer.
READ TABLE lt_inner WITH KEY key =
lt_outerkey.
ENDLOOP.
Instead, use a
single loop or optimize by using parallel processing or joins.
Use LOOP AT GROUP for grouping data: Instead
of using nested loops or manually grouping data, use the LOOP AT GROUP
statement to process data in groups efficiently.
abap
LOOP AT GROUP g_group OF lt_data INTO
DATA(group_data).
ENDLOOP.
7. Using Field
Symbols and Data References
Use field symbols: Accessing internal tables
via field symbols can be faster because it avoids copying data.
abap
LOOP AT lt_data ASSIGNING <fs_data>.
ENDLOOP.
Data references: Use references to pass large
data structures instead of copying them to save memory and processing time.
8. Effective
Use of Modularization
Avoid redundant code: Modularize your code
using subroutines, methods, and function modules to reduce redundancy, which
also aids performance optimization.
Minimize database access in loops: Move all
database access outside the loops, or fetch data once and store it in internal
tables for reuse.
9. Optimize ALV
Grid Reports
Use ALV with larger data: When dealing with
large datasets, the ALV grid (Object Oriented ALV) is more efficient for data
presentation compared to custom loops with WRITE.
10. Use
SELECTOPTIONS and RANGES
Instead of hardcoding values in the WHERE
clause, use SELECTOPTIONS and RANGES for dynamic selection, which allows for
faster processing and filtering of data.
11. Indexing
on Database Tables
Ensure that appropriate indexes exist on the
tables used in your program. This reduces the time for searching and retrieving
data.
12. Use CDS
Views for Complex Queries
In HANA environments, using Core Data Services
(CDS) views can offload complex data operations to the database level, taking
advantage of HANA’s in memory processing capabilities.
13. Efficient
Use of Aggregation Functions
Use aggregation functions (SUM, COUNT, AVG) in
the SQL query itself rather than fetching the entire dataset and performing the
calculations in ABAP.
By using these techniques, you can significantly improve
the performance of your ABAP report program. Each method addresses a specific
aspect of performance, from reducing database load to optimizing memory and
processing efficiency.
Here’s how to implement the various performance
improvement in ABAP code, along with explanations of each point:
1. Avoid
Nested SELECT Statements
Nested SELECT statements lead to multiple database
accesses and can be inefficient. It's better to perform a single SELECT with
joins or use internal tables for further processing.
Example:
abap
Inefficient
(Nested SELECT):
LOOP AT lt_orders
INTO wa_orders.
SELECT SINGLE FROM vbap INTO wa_vbap WHERE vbeln = wa_ordersvbeln.
ENDLOOP.
Optimized (Single
SELECT):
SELECT a~vbeln,
a~erdat, b~matnr, b~kwmeng
INTO TABLE lt_vbeln_data
FROM vbak AS a
INNER JOIN vbap AS b ON a~vbeln = b~vbeln
WHERE a~vbeln IN lt_orders.
Explanation: Instead of fetching each row in a loop with SELECT
SINGLE, join the tables in one query for better performance.
2. Use
INTO TABLE Instead of APPEND in SELECT Statements
Avoid appending records one by one in a loop. Use INTO
TABLE to retrieve all records at once.
Example:
abap
Inefficient (Using
APPEND):
SELECT FROM vbak INTO wa_vbak.
APPEND wa_vbak TO lt_vbak.
ENDSELECT.
Optimized (Using
INTO TABLE):
SELECT FROM vbak INTO TABLE lt_vbak.
Explanation: This retrieves all records in one go,
reducing the need for multiple accesses to the database.
3. Order of WHERE
Clause Based on Index
When a table has multiple indices, the WHERE clause
should align with the fields of the index, either primary or secondary.
Example:
abap
SELECT FROM mara
INTO TABLE lt_mara WHERE matnr = lv_matnr AND werks = lv_werks.
Explanation: If there’s an index for the fields matnr and
werks, ensure your WHERE clause follows the index sequence for faster database
lookup.
4. Use SELECT
UP TO 1 ROWS Instead of SELECT ... ENDSELECT ... EXIT
When testing for existence, use SELECT UP TO 1 ROWS
instead of looping through all the records with SELECT ... ENDSELECT.
Example:
abap
Inefficient (Using
SELECT...ENDSELECT):
SELECT FROM zflight INTO wa_flight WHERE airln = 'LF'
AND cntry = 'IN'.
EXIT.
ENDSELECT.
Optimized (Using UP TO 1 ROWS):
SELECT FROM zflight
INTO wa_flight WHERE airln = 'LF' AND cntry = 'IN' UP TO 1 ROWS.
Explanation: UP TO 1 ROWS stops the query as soon as it
finds one record, improving performance.
5. Use SELECT
SINGLE When Primary Key Fields Are Known
If all primary key fields are available in the WHERE
condition, use SELECT SINGLE.
Example:
abap
SELECT SINGLE FROM
mara INTO wa_mara WHERE matnr = lv_matnr.
Explanation: SELECT SINGLE is more efficient than
fetching multiple rows when you expect only one.
6. Column Updates
vs. Row Updates
To update multiple rows in a database, it is better to
use column updates in one query rather than updating each row individually.
Example:
abap
Inefficient
(Single row updates):
LOOP AT lt_data INTO
wa_data.
UPDATE mara SET maktx = wa_datamaktx WHERE
matnr = wa_datamatnr.
ENDLOOP.
Optimized (Column
updates):
UPDATE mara SET
maktx = wa_datamaktx WHERE matnr IN lt_data.
Explanation: The column update handles multiple records
in a single operation, which reduces database accesses.
7. Use Indexes for Frequently Accessed Data
Ensure indexes are used for frequently accessed data,
improving query performance.
Example:
abap
Efficient query
leveraging indexes:
SELECT FROM mara
INTO TABLE lt_mara WHERE matnr = lv_matnr.
Explanation: This ensures the query benefits from an
index if one exists on matnr.
8. Using
Buffered Tables
Using table buffering for frequently accessed tables
improves performance by reducing database access.
Example:
abap
Assuming table
ZCUSTOMERS is buffered:
SELECT FROM
zcustomers INTO TABLE lt_customers.
Explanation: Table buffering minimizes database access,
as the data is stored in the application server's memory.
9. Use
Aggregate Functions (e.g., MAX, MIN, SUM)
Instead of calculating aggregates in ABAP, use database
aggregate functions for efficiency.
Example:
abap
Inefficient
(Manual calculation):
MAXNO = 0.
SELECT FROM zflight INTO wa_flight WHERE airln = 'LF' AND cntry
= 'IN'.
CHECK wa_flightfligh
> MAXNO.
MAXNO = wa_flightfligh.
ENDSELECT.
Optimized (Using
Aggregate function):
SELECT MAX( fligh ) FROM zflight INTO MAXNO WHERE airln = 'LF'
AND cntry = 'IN'.
Explanation: The database can perform aggregate functions
like MAX or SUM much faster than ABAP can.
10. FOR
ALL ENTRIES in SELECT Statements
FOR ALL ENTRIES is used to fetch data based on multiple
entries in an internal table, but it should be used cautiously.
Example:
Abap
Inefficient (Nested SELECT):
LOOP AT lt_cntry
INTO wa_cntry.
SELECT SINGLE FROM zfligh INTO wa_fligh WHERE cntry =
wa_cntrycntry.
APPEND wa_fligh TO lt_fligh.
ENDLOOP.
Optimized (FOR ALL
ENTRIES):
SELECT FROM zfligh INTO TABLE lt_fligh FOR ALL
ENTRIES IN lt_cntry WHERE cntry = lt_cntrycntry.
Explanation: FOR ALL ENTRIES creates a single SELECT
statement for all matching entries in the internal table, making it much faster
than nested loops.
Key Considerations
When Using FOR ALL ENTRIES:
Ensure the
internal table is not empty: If the driver table (lt_cntry in this case) is
empty, the query retrieves all records from the database.
Remove duplicates
and sort the driver table: This reduces unnecessary database access and
improves performance.
Manage memory
efficiently: Use FREE or PACKAGE SIZE to handle large amounts of data and avoid
memory issues.
Summary of
Performance Tips:
Use aggregate
functions for computing max, min, sum, and average values.
Use FOR ALL
ENTRIES cautiously, ensuring the driver table has unique, sorted values.
Avoid unnecessary SELECT
statements in loops and use optimized queries like SELECT INTO TABLE.
Ensure indexes are
utilized for better query performance.
Use buffered
tables to reduce database access for frequently read tables.
By following these practices, you can ensure your ABAP
programs perform optimally, especially when working with large datasets and
frequent database interactions.
Using JOIN instead of multiple SELECT statements improves
performance significantly at the database level because it reduces the number
of database accesses and network roundtrips, leading to faster data retrieval.
When you use multiple SELECT statements, the program sends several requests to
the database, one for each table, and processes each result individually. In
contrast, a JOIN allows the database to fetch all the required data in a single
operation, reducing overhead and utilizing database optimization techniques.
Why JOIN is
Better:
1. Fewer Database Calls: Instead of multiple SELECT
statements, a JOIN fetches data from related tables in one call.
2. Optimized Execution Plans: Modern databases optimize JOIN
queries to efficiently retrieve data, using indexes and table relationships.
3. Reduced Data Transfer: A single JOIN retrieves the
needed records and sends them back to the application server in one go,
reducing network load.
4. Less ABAP Processing: With a JOIN, data processing
happens at the database level, which is faster than fetching the data into ABAP
and processing it there.
Example: Multiple SELECT
Statements vs. JOIN
Scenario:
You need to fetch customer details from two related
tables:
Table KNA1:
Contains general customer data (Customer number KUNNR, Name, City).
Table KNVV:
Contains salesspecific customer data (Customer number KUNNR, Sales area,
Payment terms).
1. Inefficient
Approach (Multiple SELECT Statements)
abap
DATA: lt_customers
TYPE TABLE OF kna1,
lt_sales_data TYPE TABLE OF knvv,
wa_kna1 TYPE kna1,
wa_knvv TYPE knvv.
Fetch data from KNA1
SELECT FROM kna1
INTO TABLE lt_customers WHERE land1 = 'DE'.
Loop through KNA1
data and fetch corresponding KNVV data
LOOP AT lt_customers
INTO wa_kna1.
SELECT SINGLE FROM knvv INTO wa_knvv WHERE kunnr = wa_kna1kunnr.
APPEND wa_knvv TO lt_sales_data.
ENDLOOP.
Drawbacks:
Multiple database hits: For each customer in KNA1,
the program performs a SELECT SINGLE on KNVV. If there are 1000 customers, this
means 1000 additional database calls.
Increased network traffic: The data must be
sent back and forth between the application and the database for each SELECT
SINGLE.
Inefficient processing: Each query must wait
for the previous one to finish, which creates delays.
2. Optimized
Approach (Using JOIN)
abap
DATA:
lt_customer_data TYPE TABLE OF kna1,
lt_combined_data TYPE TABLE OF kna1.
Fetch data from KNA1 and KNVV in a single join
SELECT a~kunnr,
a~name1, a~ort01, b~vkorg, b~vtweg, b~zterm
INTO TABLE lt_combined_data
FROM kna1 AS a
INNER JOIN knvv AS b ON a~kunnr = b~kunnr
WHERE a~land1 = 'DE'.
Advantages:
Single database hit: All required data is
fetched in one JOIN statement, eliminating the need for multiple database
calls.
Reduced network traffic: The entire result set
is transferred from the database to the application server in one operation.
Database level optimization: The database can
use indexes and optimization techniques to execute the JOIN query more
efficiently.
Faster execution: Since the database handles
the data retrieval in one go, the overall time to retrieve the data is reduced.
Performance
Benefits of JOIN:
Reduced Database
Load: With a JOIN, the database handles data retrieval in one operation,
reducing overhead and resource usage.
Optimized Query
Execution: The database engine can optimize JOIN operations more effectively
using available indexes, leading to faster retrieval.
Fewer Network
Trips: By using a single JOIN, all data is fetched in one step, minimizing the
network roundtrips between the application and the database.
Visualizing the
Difference:
Multiple SELECT
Statements:
1. Application sends SELECT for KNA1.
2. Database retrieves records from KNA1.
3. For each record in KNA1, the application sends a SELECT
SINGLE for KNVV.
4. The database performs 1,000 additional queries (one
for each customer).
5. The application waits for each query to finish before
continuing.
JOIN:
1. Application sends a single JOIN query.
2. Database retrieves data from both KNA1 and KNVV in one
step, applying optimizations.
3. All relevant data is sent back to the application in
one go.
Using JOIN eliminates multiple database calls and allows
the database to perform the complex task of combining data from multiple
tables.
The database
engine is better equipped to handle large datasets and can use indexing and
other optimizations to speed up queries.
Fewer network
calls and reduced ABAP level processing lead to faster program execution.
Thus, using JOIN instead of multiple SELECT statements
ensures better performance, reduced network traffic, and more efficient data
handling at the database level.
