This blog compares the improvements made in join strategy for OData between release 17.32, early 20.0 releases and release 20.0.34 onward. Complex join criteria, especially with join sets, are folded into OData queries more often. In real-life cases this increases performance an order of magnitude for larger environments queried using one or more joins.
More than half of the queries by Invantive SQL-based products on cloud platforms are based upon OData 3 or 4. In this blog I compare the performance between release 17.32, early release 20.0 and release 20.0.34 for a real-life use case querying multiple tables. The differences are explained based upon architectural changes between the three releases. The sample is executed using an Exact Online client ID, throttled at 60 API calls per minute.
Performance is compared when executed against a test company A with 750 articles, 2.500 accounts, 6.000 sales orders and 60.000 sales order lines.
The same queries were also run on a test company B with 250.000 articles, 2.500 accounts, 2.500 sales orders and 6.000 sales order lines.
Release Changes
Release 20.0.0 incorporated a number of improvements:
- Do not overrun URL length to avoid incidental Bad Requests.
- Optimize filter predicates consisting solely of AND-ed or OR-ed elements.
Release 20.0.34 incorporated an additional number of improvements:
- Simplify filter predicates involving division which are already applied using the URL, leaving more space in the URL for other criteria and enabling join sets when a division is specified in an AND.
- Merge automatically introduced join sets IN filter predicates with other predicates AND-ed together to enable join sets when criteria are applied such as LineNumber = 1 in combination with applying the relationship’s join criteria.
Outcomes
The following table describes per query (vertically) and horizontally the rounded number of rows returned plus execution duration in seconds per release:
| Company | Query | #Row | 17.32.217 (s) | 20.0.32 (s) | 20.0.34 (s) |
|---|---|---|---|---|---|
| A | 1 | 1.500 | 10 | 10 | 10 |
| A | 2 | 3.000 | 6 | 6 | 6 |
| A | 3 | 25.000 | 72 | 77 | 219 |
| B | 1 | 2.250 | 13 | 13 | 14 |
| B | 2 | 2.000 | 6 | 6 | 6 |
| B | 3 | 5.000 | >86.400 | >86.400 | 378 |
The runtime varies per release, but also the number of time spent waiting on the cloud, the number of API calls and the maximum length of the URLs used:
| Company | Aspect | 17.32.217 | 20.0.32 | 20.0.34 |
|---|---|---|---|---|
| A | api Calls | 83 | 76 | 218 |
| A | Cloud Time (s) | 68 | 72 | 178 |
| A | Maximum Length URL | 2.200 | 2.204 | 2.204 |
| B | api Calls | N/A | N/A | 326 |
| B | Cloud Time (s) | N/A | N/A | 430 |
| B | Maximum Length URL | 2.200 | 2.205 | 2.205 |
These statistics were collected using:
select max(length(url))
, sum(duration_ms)
, count(*)
from SESSIONIOS@DataDictionary
Analysis
Query 1 - collect customers
The API calls for query 1 on all three releases share the same format for both companies, explaning within-company identical runtime:
.../bulk/crm/Accounts?$filter=Status%20eq%20'C'&$select=...
Runtimes are alike and all vary within the typical variation of factor 2 when querying cloud platforms.
Query 2 - collect sales orders in two time periods
The API calls for query 2 on all three releases use for both companies:
.../bulk/salesorder/SalesOrders?
$filter=OrderDate%20ge%20datetime'2020-01-01T00%3A00%3A00'%20and%20
OrderDate%20le%20datetime'2020-05-26T00%3A00%3A00'%20and%20
(Status%20eq%2020%20or%20Status%20eq%2021)
&$select=...
Runtimes are alike and all vary within the typical variation of factor 2 when querying cloud platforms.
Query 3 - join
The API calls for query 3 are different per release.
Release 17.32
The releases 17.32 and 20.0.33 can not optimize the OData queries and downloads all sales order lines and items in bulk:
.../bulk/SalesOrder/SalesOrderLines?$select=*&$filter=Division%20eq%20352403
.../logistics/Items?$filter=ItemGroupCode%20ne%20null&$select=*
As visible in the table, with a modest data volume a bulk download outperforms smarter approaches. With a large data volume of articles as in company B and a traditional page size of 60 instead of 1.000, the collection of all data requires more API calls than available and the query will not end within 24 hours.
Using a page size of 1.000 for articles, the expected runtime is expected to be approximately 600 seconds. With a larger daily quota of 50.000 API calls, the runtime is expected to be approximately 500 seconds, consuming 800 extra API calls. Please note that all tests were done on a single, so the total daily API volume of 5.000 calls was shared by all test cases, depleting the quota early during the tests with company B.
Release 20.0.34
The new release prefers with and without the new default join-size of 10.000 the use of join sets to retrieve the data using requests such as:
.../bulk/SalesOrder/SalesOrderLines?$select=*&
$filter=OrderID%20eq%20guid'e14428f4-09f6-49ed-b109-4f43f3fb88a2'%20or%20...
OrderID%20eq%20guid'7fd383a5-b4f5-4747-97b0-784ac8f72b6d'
.../logistics/Items?$select=*&
$filter=(ID%20eq%20guid'64c9ee3f-5057-4957-b468-ca3afd5d99e8'%20or%20...
%20ID%20eq%20guid'5ace12d9-bd58-4aa1-bd39-041af0d5ed35')
%20and%20ItemGroupCode%20ne%20null
Discussion
The runtime of release 2.0.34 scales linear in the volume of sales volume, but runtime is worse than on previous releases for company A. On company B, the new execution plan outperforms previous releases.
On low data volumes and in absence of sufficient statistics on throughput and expected data volumes, OData join runtime with release 2.0.34 can be higher than on previous releases for small data volumes.
However, Invantive SQL outperforms previous releases evaluating large data volumes. In terms of O(n), the new release is an improvement, but for optimal performance a query developer still needs to consider during design time of a query the data volumes to be handled and their distribution.
Automatic fine-tuning the moment between full table scans and join sets for lowering runtimes across all uses cases requires better statistics. Currently, on Exact Online only transaction line volume statistics are collected, without information about the statistical distribution information of individual column values in buckets or otherwise. Neither are statistics available per partition and table. For future releases of Invantive SQL we will work on improving the execution plan further.
Sample Used
The test uses the following SQL largely copied from the Get My Report module “Sales per Sales Representative“, with all bind variables bound to null, both disk and memory cache disabled and all run once during non-business hours:
--
-- Collect customer accounts.
--
create or replace table act@inmemorystorage
as
select act.*
from exactonlinerest..accountsbulk act
--
-- Only customer accounts can have sales orders.
--
where act.status = 'C'
and ( coalesce('%', '%') = '%'
or
act.accountmanagerfullname like '%'
)
--
-- Collect sales orders in a reference and current time period.
--
create or replace table sor@inmemorystorage
as
select 'current'
group_code
label 'Group'
, sor.*
from exactonlinerest..salesordersbulk sor
where sor.status in (20, 21)
and sor.orderdate
between to_date('2020-01-01', 'YYYY-MM-DD')
and to_date('2020-05-26', 'YYYY-MM-DD')
union all
select 'base'
group_code
label 'Group'
, sor.*
from exactonlinerest..salesordersbulk sor
where sor.status in (20, 21)
and sor.orderdate
between to_date('2019-01-01', 'YYYY-MM-DD')
and to_date('2019-05-26', 'YYYY-MM-DD')
--
-- Report sales per sales representative.
--
select act.* prefix with 'act_'
, sor.* prefix with 'sor_'
, sle.* prefix with 'sle_'
from ACT@InMemoryStorage act
join sor@inmemorystorage sor
on sor.invoiceto = act.id
and sor.division = act.division
join exactonlinerest..salesorderlinesbulk sle
on sle.orderid = sor.orderid
and sle.division = sor.division
join exactonlinerest..items itm
on itm.division = sle.division
and itm.id = sle.item
and itm.itemgroupcode = coalesce(:p_sol_item_group_code, itm.itemgroupcode)