Introduction
This document covers some of the common mistakes found in using FOR/NEXT loop as part of the BPC Script logic. These mistakes may cause the script logic to run slower and consume the system resources. Although these mistakes produce the desired results, invariably they cause performance issues in production. This document details how to identify and rectify them.
Disclaimer: This is based on author's personal experience with script logic; the readers are advised to exercise their judgement while putting this in use.
Definition of *FOR/NEXT
Script logic variables can consist of one or more dimension member values in a reference list. In the following example, the variable %Q1% is defined in the *FOR statement as consisting of the three dimension members: 2006.JAN, 2006.FEB, 2006.MAR:
*FOR %Q1% = 2006.JAN, 2006.FEB, 2006.MAR
The *FOR command is followed by a *NEXT command to establish a process loop. The intent when using a *FOR/*NEXT is to define a variable with one or more values, then cycle through each member of the variable list one at a time.
General Mistakes
Pitfall 1: Using FOR loop to call business rules
For loops shouldn't be used to call the business rules recursively. Observe the code below.
P1.1
| *FOR %PER% = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06 *RUN_PROGRAM CALC_ACCOUNT CATEGORY = Actual CURRENCY = LC TIME = %PER% CALC = TEST *ENDRUN_PROGRAM *NEXT |
The code P1.1 will call business rule CALC_ACCOUNT 6 time recursively. This increases the IO and slows down the calculation. In most circumstances the goal should be to reduce the IO. The following approach would be preferred.
P1.2
| *RUN_PROGRAM CALC_ACCOUNT CATEGORY = Actual CURRENCY = LC TIME = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06 CALC = TEST *ENDRUN_PROGRAM |
The code P1.2 will call the business rule CALC_ACCOUNT only one time. This keeps the IO to lowest.
Pitfall 2: Using *FOR loop in place of *XDIM Filters
FOR loops shouldn't be used to filter data, instead using *XDIM_MEMBERSET would the correct approach. Observe the code below.
P2.1
| *XDIM_MEMBERSET ACCOUNT = PL010 *XDIM_MEMBERSET TIME = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06,2011.07 *FOR %PER% = 2011.01,2011.02,2011.03 *WHEN ACCOUNT *IS PL010 *WHEN TIME *IS %PER% *REC(ACCOUNT = PL020) *ENDWHEN *ENDWHEN *NEXT |
The code P2.1 filters out extra periods selected by the XDIM filter using the FOR statement. This increases the IO and slows down the calculation. To reduce the IO and calculation overhead the following approach would be preferred.
P2.2
| *XDIM_MEMBERSET ACCOUNT = PL010 *XDIM_MEMBERSET TIME = 2011.01,2011.02,2011.03 *WHEN TIME *IS * *REC(ACCOUNT = PL020)) *ENDWHEN |
The code P2.2 will produce the identical results that of code P1.1 but will run significantly faster.
Pitfall 3: Using FOR Loop with TMVL function
Sometimes while using TMVL function, especially with the time dimension, the usage of FOR loop becomes inevitable.
P3.1
| *XDIM_MEMBERSET ACCOUNT = PL010 *XDIM_MEMBERSET TIME = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06 *FOR %PER% = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06 *WHEN ACCOUNT *IS PL010 *WHEN TIME *IS %PER% *REC(ACCOUNT = PL020, TIME = TMVL(1,%PER%)) *ENDWHEN *ENDWHEN *NEXT |
The code P3.1 uses the FOR loop to provide %PER% variable to TMVL function. In the process the code makes recursive calls to WHEN statement.
P3.2
| *XDIM_MEMBERSET ACCOUNT = PL010 *XDIM_MEMBERSET TIME = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06 *WHEN TIME *FOR %PER% = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06 *IS %PER% *REC(ACCOUNT = PL020,TIME = TMVL(1,%PER%)) *NEXT *ENDWHEN |
The code P3.2 will produce the identical results that of code P3.1 but will run significantly faster. The reason for this is explained in the next example. But in short, P3.2 uses only one WHEN statement while code P3.1 uses multiple WHEN statement
Pitfall 4: Using FOR Loop to outside WHEN statement
When FOR loop is used outside the WHEN statement, the system produces multiple WHEN / ENDWHEN blocks during code compilation and execution. This creates substantial overhead on the system and should be avoided. Avoid wrapping WHEN statement inside a for loop.
In the example below data from one period (2011.01) is being copied for 6 other periods.
P4.1
| *XDIM_MEMBERSET ACCOUNT = PL010 *XDIM_MEMBERSET TIME = 2011.01 *FOR %PER% = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06 *WHEN ACCOUNT *IS PL010 *REC(ACCOUNT = PL020, TIME = %PER%) *ENDWHEN *NEXT |
The code P4.1 uses the FOR loop outside the WHEN statement, this although is not wrong syntactically but creates the following code during execution.
*XDIM_MEMBERSET ACCOUNT = PL010
*XDIM_MEMBERSET TIME = 2011.01
*WHEN ACCOUNT
*IS PL010
*REC(ACCOUNT = PL020, TIME = 2011.01)
*ENDWHEN
{…..}
*WHEN ACCOUNT
*IS PL010
*REC(ACCOUNT = PL020, TIME = 2011.06)
*ENDWHEN
P4.2
| *XDIM_MEMBERSET ACCOUNT = PL010 *XDIM_MEMBERSET TIME = 2011.01 *WHEN ACCOUNT *IS PL010 *FOR %PER% = 2011.01,2011.02,2011.03,2011.04,2011.05,2011.06 *REC(ACCOUNT = PL020, TIME = %PER%) *NEXT *ENDWHEN |
The code P4.2 will read the data only once as it produces only one *WHEN/*ENDWHEN statement and avoids creating multiple reads and loops. The code P4.2 when compiled will look like the following.
*XDIM_MEMBERSET ACCOUNT = PL010
*XDIM_MEMBERSET TIME = 2011.01
*WHEN ACCOUNT
*IS PL010
*REC(ACCOUNT = PL020, TIME = 2011.01)
{…}
*REC(ACCOUNT = PL020, TIME = 2011.06)
*ENDWHEN
Conclusion
While using FOR loop it is necessary to avoid wrapping WHEN/ENDWHEN with the FOR loop. Most importantly, the key message is to understand how the system compiles and executes the code. The best way to see that is to validate the code in UJKT. Using other logging mechanism and monitoring transactions can also aide in the process.
