Improved Very Large Company Volume Performance on Exact Online

Wiki (en)
, , , , ,
1

This blog explains new performance features on Exact Online when working with thousands of companies using Invantive SQL-based products. Typical deployment scenario is Power BI and/or Power Query.

Invantive SQL-based products such as Invantive Control for Excel, Data Replicator, Cloud for Power BI or Get My Report are typically used by two different audiences on Exact Online:

  • entrepreneurs with 10 or less legal entities,
  • accountants with 1.000 or more managed companies.

Invantive is typically only found at smaller accounting and entrepreneural firms as OEM-component of another product.

During the last year, we have seen an increased use of our products by finance professionals less acquainted with the detailed concepts of the Exact Online APIs through Power BI and Power Query.

With Invantive it is very easy to retrieve (or upload) data from thousands of companies in one statement. Even better, it is actually harder to limit the companies included than downloading data across all companies. Limiting the number of Exact Online companies is typically done by specifying the so-called ā€œdivisionā€ numbers to include as in:

select *
from   transactionlinesbulk
where  division = <A DIVISION NUMBER>

Or set a range of companies as in:

use X, Y, Z

Or even more advanced, select companies with some common criteria:

use select code from systemdivisions where name like '%Roermond%'

All these three cases request solely the data for the partitions needed.

However, with the increased use of Power BI and Power Query across a limited ranges of companies out of thousands of managed companies, we typically see Power BI/Query requests in OData4 such as:

give me all transaction details from company with short name X, Y and Z

Which is automatically translated in Invantive SQL such as:

select *
from   transactionlinesbulk
where  divisionshortname = X 
       or
       divisionshortname = Y 
       or     
       divisionshortname = Z

The execution plan automatically generated by Invantive SQL for real-time data retrieval is non-optimal when your subscription has 3.000 companies:

  • Retrieve all transaction details of 3.000 companies.
  • Throw away all details for 2.997 companies.
  • Pass the rest over to the user.

Of course, when combined with Invantive Data Replicator, the data is already cached in a SQL Server, Oracle, PostgreSQL or MySQL database and retrieval is lighting fast.

However, for real-time retrieval starting with release 20.0.34 and release 20.1.47 BETA on Invantive Cloud and Invantive Bridge Online, a more-efficient execution plan is determined in case you specify constraints on companies. These improvements will be incorporated in all future 20.0 and 20.1 releases for all product lines.

These constraints can take the form of:

  • filter on division number
  • filter on multiple division numbers using equals or IN.
  • similarly filter on short names, label, Chamber of Commerce number, VAT number, etc.

For example, the execution plan for retrieval of details on companies with short name X, Y and Z is reduced to:

  • Retrieve all transaction details of the three companies specified in the SQL.
  • Pass them to the user.

Even better, when you specify impossible values, the execution plan ensures instant response times, such as using:

select *
from   transactionlinesbulk
where  divisionlabel 
       in 
       ( 'I have chosen a non-existing label'
       , 'Another company not existing'
       )

The execution of this query ends immediately, returning no data at all.

Find more inspiration on optimizing performance by understanding the structure of Invantive Cloud on Invantive Cloud Structure.

An overall starting point for Power BI optimization techniques is available on Overview of Power BI Performance and Download Size Improvement Techniques.

1 like