Ways to Improve Performance
Created by Damien Zwillinger, Modified on Mon, 23 Oct 2023 at 05:50 PM by Harry Lewis
TABLE OF CONTENTS
- Tip 1 - Use GI filters
- Tip 2 - Smart Refresh
- Tip 3 - List functions
- Tip 4 - Use arrays
- Tip 5 - Avoid volatile functions
- Tip 6 - Limit open workbooks
Performance improvement tips
There are a variety of factors that can impact the performance of a Velixo report. These include the speed of the connection to Acumatica, the speed of the server on which Acumatica sits, the design of the report, whether or not the report contains volatile Excel functions, etc.
In general, here are few suggestions that might help you:
Tip 1 - Use GI filters
If using generic inquires (which can return *a lot* of data), be sure to filter the data down to just what is actually needed. While Acumatica's OData feed is quite fast, if you try to retrieve 10s or 100s of thousands of records, it is still going to take a while.
Tip 2 - Smart Refresh
When using (non-grouped) generic inquiries, it is best to ensure that the default refresh method is configured for Smart Refresh (where only new data is retrieved from the database) rather than Full Refresh (which brings in all data which matches the specified filters).
Tip 3 - List functions
Use of the Velixo LIST functions can really be beneficial. These functions (such as EXPANDACCOUNTRANGE) return multiple values. For example, rather than listing our entire chart of accounts in our report (e.g., ~150 values), we can use one function to return the entire CoA. This makes our workbooks smaller, and they refresh faster.
Tip 4 - Use arrays
Along those same line, using Velixo's "array aware" capabilities can really speed things up. Let's consider the CoA example. WE have one functions that returns the entire CoA. We can then use one function (ACCOUNTNAME) to refer to the *entire* array and it will return the name of every account in our CoA. In this way, we have entered two functions (instead of 300) to get that same information. This can save a lot of processing by Excel and Velixo.
Tip 5 - Avoid volatile functions
Workbooks containing volatile Excel functions will cause Excel to recalculate the workbook more often. Typically, we don't notice this because most workbooks are fairly static - there's not much changing. In a report, however, recalculating can cause a delay as data is retrieved for Acumatica.
Tip 6 - Limit open workbooks
Keep in mind that when Excel recalculates, it recalculates *all* open workbooks. Thus, having multiple workbooks (especially reports) open can have a substantial impact on the performance of a report.
Was this article helpful?
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
We appreciate your effort and will try to fix the article