Troubleshooting
Problem
Customer is looking for ways to improve Excel link (spreadsheet report) performance. What is the server preference 'ENABLE_REPRUN_OPT', 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, the following test results were seen:
Environment | Report | Default | ENABLE_REPRUN_OPT = TRUE |
A | 1 | 315 | 92 |
A | 2 | 640 | 258 |
A | 3 | 1774 | 346 |
B | 1 | 124 | 127 |
B | 2 | 1238 | 1085 |
C | 1 | 70 | 62 |
C | 2 | 775 | 210 |
C | 3 | 540 | 360 |
This shows:
- In some environments, with some spreadsheet reports, enabling 'ENABLE_REPRUN_OPT' had little or no effect
- In some environments, with some spreadsheet reports, enabling 'ENABLE_REPRUN_OPT' gave performance increases of between approximately 2-5 times faster.
Example #2
In a different customer's system:
- 'ENABLE_REPRUN_OPT' = FALSE (disabled) => Report took 67 seconds
- 'ENABLE_REPRUN_OPT' = TRUE (enabled) => Report took 17 seconds (4 times faster)
Cause
Defect (reference APAR PI63404) in Controller 10.2.1 and earlier, which means that some spreadsheet reports run very slowly unless 'ENABLE_REPRUN_OPT' is enabled.
More Information:
ENABLE_REPRUN_OPT is a server preference which has been included in Controller for many years (since approximately 2006).
- It causes Controller to use a 'HASH JOIN' option for some of its SQL database queries ('selects').
- Specifically, it is a "SQL Query hint" - it tells Microsoft SQL to add a query hint ("HASH JOIN") to the query optimizer (whenever Controller asks some SELECTs for its XDB table queries)
If enabled, it will cause this change to occur for both of the following:
(1) Running Excel link reports
- In other words, when a user clicks 'F9' or 'F10' to run an XLSX spreadsheet report file.
(2) Report Generator reports
- In other words, the reports that are run inside the menu item "Reports - Run" inside the main Controller client (CCR.EXE).
Environment
ENABLE_REPRUN_OPT only works with Microsoft SQL databases.
- It does not work with Oracle.
Resolving The Problem
Fix:
Upgrade to Controller 10.3 or later.
- In these later versions, the benefits/effects of 'ENABLE_REPRUN_OPT' are enabled by default (even if the server preference is not defined in the database).
Workaround:
If using Controller 10.2.1 (or earlier), enable the server preference 'ENABLE_REPRUN_OPT' for your database.
IMPORTANT: It is recommended that you test performance thoroughly before and after the change, to ensure that all spreadsheet reports are at least as fast afterwards.
- This is because it is possible that some spreadsheets show better performance when *NOT* using 'ENABLE_REPRUN_OPT'
- In other words, some spreadsheets may be faster using the standard/default Microsoft SQL statistics (without the 'hint')!
Steps to enable ENABLE_REPRUN_OPT:
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_OPT
- Variable Value = TRUE
7. Press 'Save'
8. Exit Controller
9. Re-launch the Controller client
10. Test.
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21982182