Troubleshooting
Problem
Customer is looking for ways to improve Excel link (spreadsheet report) performance. What is the server preference 'ENABLE_REPRUN_ALT', and can it help speed up Excel link report performance?
Symptom
Customer is suffering from slow performance when running Excel link reports.
- TIP: There are several other/different methods to improve Excel link performance - see separate IBM Technote #1347497.
Example #1
In one real-life example (using a spreadsheet with 22,000 fGetVal formulae, using Oracle), the following test results were seen:
- Blank (no server preference): 400 seconds
- ENABLE_REPRUN_ALT = ALT2: 220 seconds
Example #2
In a different real-life customer example (using a spreadsheet with 40,000 fGetVal formulae, running over multiple periods and many accounts, using MS SQL), the performance of the spreadsheet was 10 times faster (using both F10 and F9) when using ENABLE_REPRUN_ALT = ALT2.
Example #3
In a different real-life customer example, the problem only affects reports where thery were using the Group as company code (instead of a single legal entity) in the fgetval formulae. In their case, the following test results were seen:
- Blank (no server preference): 2 hours
- ENABLE_REPRUN_ALT = ALT2: 30 seconds
Cause
ENABLE_REPRUN_ALT is a server preference which has been included in Controller for many years (since at least Controller 8.3).
====================================================
ENABLE_REPRUN_ALT has the following available values:
ALT1
This is the default setting (the value that is used if the server preference does not exist).
- If the spreadsheet has many fGetVals, this causes the query performance to decrease.
ALT2
This method puts those 'multiple subselects' into temporary tables, and joins those instead.
- This causes Controller to create a database query which joins multiple subselects.
- This is much faster if the spreadsheet has many fGetVals.
====================================================
Environment
** IMPORTANT - 'ENABLE_REPRUN_ALT' should be used with caution **
** It only works with some versions of Controller (for example 10.2.1) **
** Do not use it with Controller 10.3.1, because it will cause fGetVal formulae to all change to zero (0) **
'ENABLE_REPRUN_ALT' works with:
- Microsoft SQL
- Oracle
However, be aware that it is compatible to also use ENABLE_REPRUN_OPT simultaneously (at the same time as ENABLE_REPRUN_ALT) only when using Oracle (not SQL).
- If you enable both 'ENABLE_REPRUN_ALT = ALT2' and 'ENABLE_REPRUN_OPT = TRUE' (when using Microsoft SQL) then your report will retrieve zeros (0) when using fGetVals.
In other words:
- If using Microsoft SQL, then ENABLE_REPRUN_ALT cannot be combined with ENABLE_REPRUN_OPT
- If using Oracle, then ENABLE_REPRUN_ALT can be combined with ENABLE_REPRUN_OPT
Resolving The Problem
** IMPORTANT - 'ENABLE_REPRUN_ALT' should be used with caution **
** It only works with some versions of Controller (for example 10.2.1) **
** Do not use it with Controller 10.3.1, because it will cause fGetVal formulae to all change to zero (0) **
Create a server preference ENABLE_REPRUN_ALT and test it with the parameter ALT2
- Perform a before/after test for your spreadsheet(s) to be sure that it gives better performance.
------------------------------------------------------------------
NOTE: If using Microsoft SQL, then ENABLE_REPRUN_ALT cannot be combined with ENABLE_REPRUN_OPT
- If you enable both 'ENABLE_REPRUN_ALT = ALT2' and 'ENABLE_REPRUN_OPT = TRUE' (when using Microsoft SQL) then your report will retrieve zeros (0) when using fGetVals.
------------------------------------------------------------------
Steps:
The server preference is enabled on a per-database basis.
- Therefore it needs to be enabled for each individual database (for example 'production', 'test', 'development') that you want to apply it to.
1. Launch the Controller client
2. Choose the database (where you wish to make the change)
3. Logon as an administrator
4. Click "Maintain - Configuration - General"
5. Click 'Server Preferences'
6. Create a new entry:
- Variable name = ENABLE_REPRUN_ALT
- Variable Value = ALT2
7. Press 'Save'
8. Exit Controller
9. Re-launch the Controller client
10. Test.
Related Information
Was this topic helpful?
Document Information
Modified date:
19 December 2018
UID
swg22005347