Long runtimes can occur when loading data targets in SAP BW if large amounts of data have to be processed, for example, in end routines. Performance problems may sometimes occur only after the beginning of production, due to growing data volumes that were not available at the time of development and in the test phase. In such cases, there are always risks involved when performance optimizations have to be made to a productive application. The approach described below represents one way of significantly speeding up the processing of large amounts of data under certain conditions — without having to make changes to the originally implemented logic or processing steps.
A typical example in practice is the reporting on open purchase orders. One of our SAP BI implementation projects required the processing of these open purchase orders at the individual item level, along with their material classification and vendor acknowledgement, and any related receipts of inbound goods — all valuable information that had to be available in reports.
Lookup tables were used to provide the material classifications, the vendor confirmations, and the related receipts of inbound goods. These tables were defined as standard tables. The processing of these table entries was followed by the calculation of open, confirmed, and unconfirmed order quantities, and by the offsetting of goods receipts in the case of partial deliveries. The processing of these lookup tables took place in nested loops within an outer loop using the RESULT_PACKAGE. Because both the RESULT_PACKAGE and the lookup tables contained several tens of thousands of records, the overall performance of the data processing was correspondingly low.
The lookup tables were queried using complex WHERE clauses within several nested LOOP – ENDLOOP blocks. Tables of TYPE HASHED TABLE could therefore no longer be used.
The total processing time for approximately 400,000 order items was over 90 minutes.
The use of sorted tables led to only minor performance improvements.
Here is a code example from the data processing:
The problem of the long duration could only be solved with the help of multidimensional internal tables. Here we defined a HASHED TABLE, whose “UNIQUE KEY“ improved reading performance greatly. In the case above, the key consisted of the number of the purchase order header concatenated with the purchase order position for item detail. In addition to this key, the table contained another internal table located at the position of an additional field, and which corresponded to the table in Listing 1.
The filling of the original internal table remains unchanged and takes place within the FOR ALL ENTRIES statement using the RESULT_PACKAGE:
At the end of processing the data are transferred into the HASHED TABLE and the original internal table is deleted:
Optimizing Data Processing
Within the following data processing, the original internal table is filled only with the currently required data records:
The processing of the original internal table remains unchanged.
At this point the table contains only the records which are required for this processing step. In the current processing the required records are: material classifications, vendor acknowledgements and inbound goods receipts. Since the internal table only contains very few data records compared to previously, the processing inside the loop is now much more efficient.
What is the benefit of this technique?
One advantage of implementing this technique is that no changes need to be made to the existing internal tables and the logic used in the WHERE clauses. The cost of setting up the HASHED TABLE is of little significance within the entire application lifecycle. This solution can be used with low risk to optimize the runtime of existing applications, since the core of the processing logic remains unchanged and only optimizes data provisioning.
This particular technique is recommended, for example, for applications that have been used in production for a long time and are reaching their runtime limits due to the ever increasing volume of data to be processed.
In the above practical example of processing open purchase order documents, the total data processing time was reduced from over 90 minutes to just under 15 minutes.
Source of image: Inspiricon AG