CPU usage too high when auto-refresh formulae is on
As a user of .Stat DLM Excel add-in
When the Excel auto-refresh calculations option is on
When the user is retrieving data with the .Stat DLM Excel add-in
Then the .Stat DLM Excel add-in is using a lot of CPU
More inputs
When data is retrieved, it triggers Excel formula and VBA code every time cells are changed.
For large downloads (300 000 lines in the below example), automatic calculation is triggered dozens of time and saturates CPU on the machine.
The more CPU the stronger is the effect; We tried on AS-GEN-1 (40 CPUs), EM-SBX-DEV-6 (24 CPUs) and on a user's laptop (4 CPUs):
- On the user’s laptop the effect can be overlooked.
- On EM-SBX-DEV-6 the effect is visible but does not kill the machine.
- On AS-GEN-1, the system becomes totally unresponsive.
The solution team has been granted access on EM-SBX-DEV-6 in order to reproduce and fix this behaviour on a 24 CPUs machine.
Possible solutions
- Turning off event handler before updating the Excel file and turning back on once it is done may be an option from Microsoft.
- Alternatively writing cells in a batch can help, if not already the case.
Example file:
See: S:\Data\EM Practice Building Team\Solutions\DataWizard\EAG2020_TC_C1_TEST.xlsx
(The 37 Mb excel file could not be attached to the ticket as it is over the 10 Mb limit. If required ask @atoch for more technical information)