This query creates an export for UPS from the deliveries history:

select 'key'
,      ACC.Name
,      CON.FullName
,      CON.Phone
,      ADR.AddressLine1
,      ADR.AddressLine2
,      ADR.AddressLine3
,      ACC.Postcode
,      ADR.City
,      ADR.Country
,      ACC.Code
,      DEL.DeliveryNumber
,      CON.Email
,      case 
       when CON.Email is not null
       then 'Y' 
       else 'N' 
       end 
       Ship_Not_Option
,      'Y' Ship_Not
,      'ABCDEFG' Description_Goods
,      '1' numberofpkgs
,      'PP' billing
,      'CP' pkgstype
,      'ST' service
,      '1' weight
,      null Shippernr
from   ExactOnlineREST..GoodsDeliveries del
join   ExactOnlineREST..Accounts acc
on     ACC.ID = del.DeliveryAccount
join   ExactOnlineREST..Addresses ADR 
on     ADR.ID = DEL.DeliveryAddress
join   ExactOnlineREST..Contacts CON 
on     CON.ID = DEL.DeliveryContact
where  DeliveryDate between $P{P_SHIPDATE_FROM} and $P{P_SHIPDATE_TO}
order  
by     DEL.DeliveryNumber

It takes many minutes to run. The number of deliveries and accounts grows with several hundreds each day. Addresses and contacts are mostly 1:1 with accounts. How can this query be optimized for speed in Invantive Control for Excel?

Probably this query is run at most once every day, since the deliverydate does not contain time. Therefore, the number of rows selected from ExactOnlineREST..GoodsDeliveries is several hundreds. Based upon the statistics given, the number of accounts, deliveryaddresses and contacts is also approximately several hundreds.

Normally, such a query would be optimized by a solution such as Exact Online query with joins runs more than 15 minutes, but that solution will not work here: the third value of a join_set(soe, orderid, 100) is the maximum number of rows on the left-hand side to be used with index joins. At this moment, the maximum number on the left-hand side is something like 125, based upon constraints on the URL length for OData requests to Exact Online. Please remember the actual OData query is a GET using an URL, not a POST with unlimited size for the filter.

The alternatives are:

1. Split volume
2. Data Cache
3. Data Replicator
4. Have SQL engine or Exact Online adapted :-)

Split Volume

In a separate query select the eligible GoodsDeliveries and put them in an in-memory or database table using for instance:

create or replace table gdy@inmemorystorage as select ... from ...

Then create a temporary table per 100 or similar rows such as:

create or replace table gdysubpartition1@inmemorystorage as select ... from ... where rowidx$ between 0 and 99
... etc for 100, 200, 300, 400, 500

And then run the query several times, each time with a different gdysubpartition1..gdysubpartition5 instead of the original from ExactOnlineREST..GoodsDeliveries.

Of course, you can also avoid the use of intermediate tables by using an inline view like:

 from (select * from goodsdeliveries where date... limit 100)

or alike.

Data Cache

When you run the query multiple times per day (unlikely, but I don't know), you might want to cache the Accounts in a relational database and update it every day.

You can also use a 'local memorize results clipboard andlocal save results clipboard to to save the last results to a file manually and later restore them usinglocal load results clipboard from ...andlocal insert results clipboard in table . And maybe theninsert into from exactonlinerest..accounts where datecreated > trunc(sysdate)`.

Data Replicator

With Data Replicator enabled, you can have replicas created and maintained automatically within an on-premise or cloud relational database for Exact Online API entities. For low latency, you will need to enable the Exact webhooks.

Have SQL Engine or Exact adapted

You can also register a request to have the SQL engine to allow higher number in the join_set hint, which would require addressing the EOL APIs in another way. Or register a request at Exact to also allow POST requests to the API with the filter in the body.