Ways to Improve Performance

Applies to:

• Velixo NX
• Velixo Classic
• Acumatica, Cegid, MYOB

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.