Troubleshooting
Problem
Customer is experiencing slow Excel-based report performance. Is there a way to speed up how long it takes to refresh spreadsheet reports with Controller?
Symptom
Slow report performance when using Excel-based reports.
- Typically these are Controller Excel link reports, which use fGetVal formulae, and are triggered/run via F9.
Cause
There are several different ways to improve Excel report performance in Controller:
- Method #1 - Make sure that the spreadsheet report(s) are as efficiently designed as possible, and take advantage of all the features available to speed them up
- Method #2 - Ensure that the RDBMS database (SQL / Oracle / DB2) itself is regularly optimised.
- For example, ensure that the 'statistics' are updated every night, and the databases are regularly re-indexed etc.
- Method #3 - Increase computing power (e.g. servers and client PCs)
- Method #4 - Upgrade to the latest version of Controller
- Method #5 - Ensure that your SQL database server setting 'Max Degree of Parallelism' is tuned correctly.
- Method #6 - If using SQL 2014 (or later), check your database compatibility settings (or configure "Legacy Cardinality Estimation" to be "ON")
- Method #7 - Reduce the size of the XACCLOCK database table
- Method #8 - Check if you are using cc.fCompCurr() formulae
- Method #9 - (Rare) If using DB2, check your current INTRA_PARALLEL setting
- NOTE: This is rarely needed to be changed. In most customer situations, the default setting is best.
- Method #10 - Check if you are using cc.fStatus() formulae
- (in case you are suffering from defect APAR PH04948)
- Method #11 - If using DB2, consider modifying the DB2 indexes from their default settings
- Method #12 - If using SQL, check the SQL setting 'Cost Threshold of Parallelism'
- Method #13 - If using SQL 2014 (or later), clear all SQL cached execution plans
- Method #14 - If using SQL, check the SQL setting 'Maximum server memory'
- Method #15 - (rare) If using SQL, and your XDB tables are massive, then consider partitioning your XDBxx tables.
Resolving The Problem
Method #1 - Efficient spreadsheet report(s)
There are a number of ways to optimise the layout and design of reports. The best starting point to understanding this process is to read the Proven Practice document "Efficient Report Design". Which can be found here:
Alternatively, read the attached document 'Technote 1347497 - Creating Excel Link reports with efficient design - Nov2007.pdf'
- One of the document's suggestions is to change the names of the sheets (in your Excel spreadsheet) to ensure that the calculations are performed in the most efficient order. For example, the names could be:
- a.datasheet - contains all Controller functions
- its name ensures that it is the first to be calculated.
- b.report
- This is a subsequent sheet (which contains references to a.datasheet)
- c.report
- etc
- a.datasheet - contains all Controller functions
- It is very important to ensure that all of your Controller functions (e.g. fGetVal formulae) are only located on one sheet (the first one, for example called 'a.datasheet').
- If you do not obey this rule (in other words, you have fGetVal formulae inside 2 or more sheets, and make references between sheets) then the same fGetVal function(s) may get executed 2 or more times each! This is caused by the way that Microsoft Excel's decision tree works.
- Another suggestion is to use Optimise2 (ERO).
- Optimise2 is explained inside a separate Technote (#1347048)
- To summarise, Optimise2/ERO can often give great benefit (for example 3 times faster report speed) with very little effort
- When utilising Optimise2, you can also optionally use BULKCOMPRESS. If possible, disable BULKCOMPRESS to speed up your reports.
- Bulkcompress is explained inside a separate Technote (#1588640)
- Its job is to reduce out-of-memory issues by compressing data on the client side. However, it will cause some performance (speed) loss.
- In one real-life example (using Controller 10.4.1 and Oracle) the report ran over twice as slow (290 seconds compared to 105 seconds) when BULKCOMPRESS was enabled.
- Another suggestion is to try the 'ENABLE_REPRUN_OPT' Server Preference
- ENABLE_REPRUN_OPT is explained inside separate Technote #1982182
- To summarise, ENABLE_REPRUN_OPT can often give great benefit (for example 3 times faster report speed) with very little effort. However, its downside is that is (potentially) could cause some reports to actually perform slower. Therefore, care must be taken to test it thoroughly.
- Another suggestion is to try the 'ENABLE_REPRUN_ALT' Server Preference
- ENABLE_REPRUN_ALT is explained inside separate Technote #2005347
- To summarise, ENABLE_REPRUN_ALT can often give great benefit (for example up to 10 times faster report speed) with very little effort. However, its downside is that is (potentially) could cause some smaller reports to actually perform slower. Therefore, care must be taken to test it thoroughly.
- Another suggestion is a warning that if you use complex Microsoft formulae (for example 'VLOOKUPS') then this can cause performance issues
- Consider minimising the use of VLOOKUPS
- If you fetch data for more than one year, there will also be a performance penalty to pay as Controller will fetch all data for the report in one single query.
- It is possible that the use of dimensions in a report will make an impact on the time it takes to run it (as well as on the load of the database server).
In addition to the advice above, the spreadsheet should not be excessively large. Specifically, IBM recommends that each spreadsheet contains no more than 10,000 fGetVal formulae.
- IBM have found that many more fGetVals (e.g. 50,000) can work OK in some environments, but it is not recommended.
Also, try to minimise the "Used Range" in your Excel report.
- To save memory and reduce file size, Excel tries to store information about the area only on a worksheet that was used. This is called the "used range". Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. This can cause performance obstructions and file-size obstructions.
- You can check the visible used range on a worksheet by using CTRL+END. Where this is excessive, you should consider deleting all the rows and columns below and to the right of your real last used cell and then saving the workbook. TIP: Create a backup copy first. If you have formulas with ranges that extend into or refer to the deleted area, these ranges will be reduced in size or changed to #N/A.
Also, consider changing the Excel recalculation mode from 'automatic' to 'manual'.
- For more information on why this can help, see separate IBM Technote #2006150
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #2 - Ensure that the RDBMS database (SQL / Oracle) itself is regularly optimised.
- The Controller users themselves can perform a regular (for example once a week or once a month) database optimisation from inside the application.
- This is a manual process, done whilst in single-user mode.
- In addition, the I.T. department can schedule regular 'automatic' optimisations. The process depends on the type of database server that you are using - SQL 2000, SQL 2005 or Oracle.
- Microsoft SQL - see the separate IBM Technote #1396973 'Controller Microsoft SQL database optimisation and maintenance Proven Practice / best practice'
- Oracle - see the separate IBM Technote #1346962 'Best Practices for Oracle database with Controller'
- DB2 - see the separate IBM Technote #1981728 'Controller DB2 database optimisation and maintenance Proven Practice / best practice'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #3 - Increase computing power (e.g. servers and client PCs)
Controller Excel reports typically cause strain on both the CPU of the client and the database (e.g. SQL) server.
- Most spreadsheets cause the greatest load on the client PC (or Citrix/TS server). Tests have shown that some reports can get huge performance boosts by upgrading the client
- In one example, using a dual-core Intel Xeon 5160 @ 3GHz (compared with a standard P4 2.4GHz) as the client PC, caused speed increases of approximately 50% for one particular spreadsheet
- In another example, when the client PC was upgraded from an old VMWare image (with 2Gb RAM) to a modern (12GB RAM, 8 cores, 2,2GHz) real PC the "Analyzing Sheet" section of the report took only 17 seconds (compared to 33 seconds on the old client). In other words, the performance of this section of the report was doubled.
- However, complex multi-period (e.g. 12 month) 'budgeting' reports may impact the SQL server the most
- In one example, a report ran in 55 seconds when using a test virtual SQL server based on 1 CPU core
- The same report ran in 15 seconds after upgrading the SQL server to 2 CPU cores (but keeping the other hardware - e.g. application server - the same).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #4 - Upgrade to the latest version of Controller
Later versions of Controller often provide performance improvements.
- For example, Controller 8.4 (released November 2008) contains several performance improvements over previous releases.
- In 8.4, some complex Excel reports can run 10% to 20% faster (compared with Controller 8.3).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #5 - Ensure that your SQL database server setting 'Max Degree of Parallelism' is tuned correctly.
For example, in one real-life case, a customer was using an 8-CPU SQL 2005 server, and found the following:
- Report took 96 seconds when 'Max Degree of Parallelism' set to 1
- Report took 26 seconds when 'Max Degree of Parallelism' set to 2
- Report took 26 seconds when 'Max Degree of Parallelism' set to 0 (use all available / infinite CPUs)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #6 - If using SQL 2014 or later
Check your database compatibility settings (or configure "Legacy Cardinality Estimation" to be "ON").
- For more details, see separate IBM Technote #2015368.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #7 - Reduce the size of your XACCLOCK database table
In some systems, the XACCLOCK table can grow very large. This affects some database indexing tasks, which can cause slow performance when running some Controller-related SQL queries.
- For more details, see separate IBM Technote #1374227.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #8 - Check if you are using 'fCompCurr' formulae
In some situations, fCompCurr formulae can cause slowdowns. Therefore try to:
(a) minimise the number of fCompCurr formulae used
(b) if possible, only have one period (used throughout the spreadsheet) for fCompCurr.
- For more details, see separate IBM Technote #0733275.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #9 - Slow Excel link spreadsheet performance (when using DB2) caused by default INTRA_PARALLEL setting (intra-partition parallelism is disabled)
Enable intra-partition parallelism (INTRA_PARALLEL) on your DB2 server. Afterwards (if necessary) modify the 'MAX_QUERYDEGREE' setting too.
- NOTE: This is rarely needed to be changed. In most customer situations, the default setting is best.
- For more details, see separate IBM Technote #0735173.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #10 - Check if you are using 'fStatus' formulae
Some versions of Controller contain defect APAR PH04948. This causes fStatus formulae to trigger slowdowns. Therefore try to minimise the number of fStatus formulae used
- For more details, see separate IBM Technote #0738809.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #11 - Consider modifying the default DB2 database table indexes
In some situations, it can be beneficial to modify the default DB2 indexes.
- For more details, see separate IBM Technote #0740825.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #12 - If using SQL, check the SQL setting 'Cost Threshold of Parallelism'
(RARE) In some situations, you may need to modify the setting for 'Cost Threshold of Parallelism'
- For more details, see separate IBM Technote #0792025.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #13 - If using SQL 2014 (or later), clear all SQL cached execution plans
Delete all SQL cached execution plans, using the following SQL command: DBCC FREEPROCCACHE
- For more details, see separate IBM Technote #0792049.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Method #14 - Ensure that your database server setting 'Maximum server memory' is tuned correctly.
Make sure that your SQL instance(s) leave some RAM spare for other processes (such as the Windows operating system).
- For more details, see separate IBM Technote #1498586.
Method #15 - Consider partitioning your XDB tables
This should only be done in very rare circumstances. For most customers, this is unnecessary.
- For more details, see separate IBM Technote #3422901.
Related Information
1982182 - 'ENABLE_REPRUN_OPT' Server Preference
1396973 - Controller Microsoft SQL database optimisatio
1981728 - Controller DB2 database optimisation and main
2005347 - 'ENABLE_REPRUN_ALT' Server Preference
2006150 - Slow performance for Excel-link reports when
2015368 - Very slow Excel-link performance caused by Mi
1374227 - Reducing the size of Controller database by deleting "period locking …
0733275 - Very slow Excel link spreadsheet report (F9) speed, caused by fCompCu…
0735173 - Slow Excel link spreadsheet performance (when using DB2) caused by de…
0738809 - Very slow Excel link spreadsheet report (F9) speed, caused by fStatus…
0740825 - Very slow Excel link spreadsheet report (F9) speed, caused by default…
0792025 - Slow Excel link report performance caused by 'Cost Threshold of Paral…
0792049 - Slow Excel link report performance caused by old/out-of-date (cached)…
1498586 - Intermittent performance problem when using powerful 64-bit SQL serve…
1588640 - How to reduce "Out Of Memory" errors when using Excel Link and ERO, b…
3422901 - Very slow Excel link spreadsheet report (F9) speed, caused by large X…
Historical Number
1037260
Was this topic helpful?
Document Information
Modified date:
25 February 2020
UID
swg21347497