Have you been reviewing code samples lately? There are some great documents out there which make it very simple to understand the point, even if you’re a little code shy.
The ‘Boost your performance – ABAP Do’s and Don’ts.pdf’ document is available in the SAP Service Market Place (SMP) and is a great read for all experience levels. The essential pieces of the ‘Performance Improvement Puzzle’ are clearly shown in easy to understand diagrams. You could pick a single piece and loose months digging deep into just that one aspect of a working SAP system and still know where you were in the grand scheme of things. You don’t get lost because you understand why you are there.
The emphasis on improving you coding skills requires two separate focuses that must be improved at the same time if you want to achieve great results:
- ‘What’ is the code supposed to be doing;
- ‘How’ is the code achieving that goal.
Sounds simple enough but this clarity is quite often missing from code that is already live in the BW Production system. This in turn results in some seriously poor performance and makes life for the BW Support team less enjoyable. The “Supportable Coding in Data Warehousing” article outlines a simple strategy to ensure that the ‘What’ and ‘How’ aspects of the code are kept together, in the code for everyone’s benefit.
In the “Boost your performance – ABAP Do’s and Don’ts.pdf” document on page 14 the first two guidelines should always be implemented in everything you do. Never underestimate the benefits of reducing the width and height of all DataSets your code touches.
By focusing on only the fields (width) and records (height) that are needed by the business rules, you minimise the amount of unnecessary CPU and Input/Output (IO) processing. This always has a direct reflection on the Process Chains ability to deliver the overnight data fresh for tomorrow’s reports.
The minimising of the width is achieved by reviewing all structures, internal tables and SELECT statements to remove the fields that are not absolutely necessary:
- Avoid nesting entire structures in other structure definitions;
- Avoid definitions that just replicate an existing table;
- Avoid using SELECT * statements.
The minimising of the height is achieved with more specific selection filters that result in DataSets that have fewer records. This is generally achieved by:
- Dropping internal table records that are no longer required;
- LOOP AT … WHERE statement;
- SELECT … WHERE statement;
- SELECT … FOR ALL ENTRIES IN statement.
In the “Boost your performance – ABAP Do’s and Don’ts.pdf” document on page 58 there is a great code example that all BW Developers should be using. While the example is focused on the aspect of bypassing the buffer, it is providing a great template to be used in the BW Transformation Start Routine and End Routine.
Use of the FOR ALL ENTRIES IN statement assists to minimise the work done by the database server and returns less records to the program running on the application server; the current DataPacket. This is the primary performance improvement. This benefit continues to cascade throughout the life of the executing program.
Use of the FOR ALL ENTRIES IN statement is an efficient way to shift the technical debt of using the DataPacket to apply a selection filter on the database operation. The Start Routine code will now be naturally optimised to process only related records.
“Remove unnecessary memory consumption because
of lazy code development filling up internal tables”
For Example: A Transformation that loads the ‘Purchase Order Schedule Line Items’ (aka the 2LIS_02_SCL DataSource) can have two SELECT … FOR ALL ENTRIES IN statements in the Start Routine to get the matching Material and Vendor master data records. We then have the freedom to implement any business rule that requires a comparison of the PO-SCL transaction data records to the Material and Vendor master data attributes. Most importantly, the two internal tables that contain the Material and Vendor attributes have been height optimised because they only contain the master data records that exist in the current DataPacket being processed.
When the business rules in the Transformation need access to data that is not readily available in the DataPacket, seriously consider the FOR ALL ENTRIES IN performance improvement to ensure you process only relevant matching records.
Further Reading: The official “SELECT … FOR ALL ENTRIES IN” documentation.
Download: The “Boost your performance – ABAP Do’s and Don’ts.pdf” [SMP Logon Required] document from SAP Service Market Place.