Problem
You find performance of updating the numbers of Invantive’s formulas in Excel (with Invantive Control for Excel) insufficient. This article describes how to collect data on the actual performance, including performance of the backend system like Visma or Exact Online.
Solution
A spreadsheet can contain thousands or tens of thousands of Invantive formulas. In most optimal scenarios, the throughput will be 1.000 formulas per second. However, the calculation in Excel itself or the backend system can drastically reduce throughput.
When you find performance insufficient, please register a ticket and state:
- Current duration in seconds.
- Expected or desired duration in seconds.
- Measurements as shown below.
- If possible include the spreadsheet with anonymized data.
Formula Performance Measurements using SQL
Invantive Control maintains statistics on the Excel formulas evaluated. These statistics are available using the view “UdfMeasurements@IC“. You can retrieve the measurements by following these steps:
- Wait for the formulas to have all been updated.
- Open the Query Tool from the Modeller ribbon.
- Enter:
select *
from udfmeasurements@ic
local export results as "c:\temp\udfmeasurements.xlsx" format xlsx
- And press F5 or select ‘Execute everything’ from the Edit menu (second option).
- Add the file c:\temp\udfmeasurements.xlsx to the ticket.
Formula Performance Measurements using Building Blocks
Invantive Control maintains statistics on the Excel formulas evaluated. These statistics are available as a building block. You can retrieve the measurements by following these steps:
- Wait for the formulas to have all been updated.
- Create a new Excel workbook using Ctrl+N or File → New.
- In the modeller ribbon enable the Invantive Control repository by applying the slider from
to
- Choose User-Defined Functions Measurements from the menu:
- Click on OK:
- Click once more on OK:
- Save the new workbook.
- Add the new workbook to the ticket.