Performance measurements large Exact Online companies with incremental (sync) APIs

Topic in progress

With the introduction of the *Incremental tables on Exact Online using the sync APIs, the processing duration and CPU power needed for Invantive SQL as a fraction of the total processing time has become a relevant factor for large Exact Online companies.

During the last weeks our engineers made many improvements to increase throughput. This topic provides an overview of the advances.

For more information on *Incremental tables and the relationship to the Sync APIs of Exact, please refer to Snel en met minder API-calls Exact Online uitlezen met de incrementele "sync API"-tabellen (Dutch).

Data Collection Approach

All measurements were done on a very large Exact Online subscription with one partition (company) containing approximately 8.5 million transaction lines.

Measurements are compared between Invantive SQL release 20.1.365 and 20.1.386.

Measurements on earlier releases of Invantive SQL are not available since preceding releases could not handle data volumes exceeding 4 GB of business data in an *Incremental table for one company during the encryption and compressing of interim results. This limitation has been lifted since then.

Measurements were done 2 weeks for start of VAT period, with no other stress on the client and server CPU and memory resources.

The performance measurements on the retrieval of *Incremental Exact Online tables have been done using URL requests on Invantive Bridge Online such as:

curl --url https://bridge-online.cloud/PATH/odata4/ExactOnlineREST.Incremental.GLAccountsIncremental --output %TEMP%\data.bin --basic --user john.doe@acme.com:secret

The bandwidth between the client server and the Bridge Online server is limited to 5 Gbps. The Bridge Online server (the “OData4 producer”) used in the test had 32 GB of internal memory, whereas the client server (the “OData4 consumer”) had 16 GB of internal memory. Both AWS-hosted servers were running a Xeon 8259CL CPU @ 2.50 GHz with respectively 8 and 4 virtual processors.

An Exact Online app registration was used which allowed 50.000 calls per division per day. This is sufficient for a download of 50 million rows from Exact Online into the Invantive SQL storage.

The following tables were analyzed:

  • AccountsIncremental
  • AddressesIncremental
  • ContactsIncremental
  • GLAccountsIncremental
  • ItemsIncremental
  • QuotationLinesIncremental
  • SalesInvoiceLinesIncremental
  • SalesOrderLinesIncremental
  • TransactionLinesIncremental

Test Size

The following tables lists the number of rows per table and uncompressed size:

Table #Rows (K) Size (MB)
AccountsIncremental 336 948
AddressesIncremental 345 366
ContactsIncremental 332 549
GLAccountsIncremental 600 0
ItemsIncremental 114 238
QuotationLinesIncremental 0 0
SalesInvoiceLinesIncremental 2.317 4.425
SalesOrderLinesIncremental 410 725
TransactionLinesIncremental 8.389 9.139

Measurements

The table belows presents per table the duration in seconds of the initial load and next load. Also, the throughput in rows/second is given between the two versions. The number of rows and the OData data volume does not change since the *Incremental tables always contain the full data set. These tables manage the use of the Exact Online Sync APIs and Deleted APIs internally in combination with previous downloads.

Measurements with ‘-’ were not completed.

Initial load

Table Duration 20.1.365 Duration 20.1.386 Throughput Throughput
AccountsIncremental 1170 1180 287 x
AddressesIncremental 446 x 774 x
ContactsIncremental 514 x 646 x
GLAccountsIncremental 1 1 600 x
ItemsIncremental 217 x 525 x
QuotationLinesIncremental 2 0 - -
SalesInvoiceLinesIncremental 5.611 5.661 x x
SalesOrderLinesIncremental 1.525 1.648 x x
TransactionLinesIncremental - 19.906 - x

Next load

The following tables list the duration of the load after the initial load, but when the OData4 is not used (typically 4 hours). Since practically no API traffic to Exact Online is needed, the duration is solely depending on internal efficiency of Invantive SQL, the serialization to OData4 and the bandwidth available:

Table Duration 20.1.365 Duration 20.1.386 Throughput Throughput
AccountsIncremental 244 252 x x
AddressesIncremental 121 111 x x
ContactsIncremental 156 132 x x
GLAccountsIncremental 1 1 x x
ItemsIncremental 50 44 x x
QuotationLinesIncremental 1 0 x x
SalesInvoiceLinesIncremental - 3.041 - x
SalesOrderLinesIncremental 218 228 x x
TransactionLinesIncremental - 18.786 - x

From OData4 Cache

When the same data is requested within the expiration time as configured on the database (typically 4 hours), the data is returned using the OData4 cache. There are no caches in this mechanism between recent versions:

Table Duration Throughput
AccountsIncremental 12 13
AddressesIncremental 4 7
ContactsIncremental 4 7
GLAccountsIncremental 0 0
ItemsIncremental 2 3
QuotationLinesIncremental 0 x
SalesInvoiceLinesIncremental 78 x
SalesOrderLinesIncremental 13 x
TransactionLinesIncremental - -