Topic in progress
With the introduction of the *Incremental tables on Exact Online using the sync APIs (see Faster Exact Online download using incremental "sync API"-tables), 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 Faster Exact Online download using incremental "sync API"-tables.
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
A more extensive explanation on using curl to download the OData feed is available as Pre-load data in OData-cache to avoid Power BI timeouts.
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 | - | - |