Handcrafted OData queries on Exact Online with Invantive

Question

We are currently running a number of hand-crafted and optimized OData queries on Exact Online using Python. This runs on several thousand of divisions. However, I want to migrate them to Invantive SQL for ease of maintenance.

But some of the optimizations like explicit orderby in the OData query are not forwarded to Exact Online by Invantive SQL; they just retrieve all data or the top x and then do an orderby.

Especially for maximum value determination that can be a lot slower.

Simple sample on small table:

https://start.exactonline.nl/api/v1/<<division>>/financial/Journals?$select=BankAccountIBAN,BankAccountDescription&$orderby=BankAccountIBAN desc&$top=5

Is there an alternative to optimize the actual OData queries executed by Invantive SQL?

Answer 1

You can either use the Data Replicator or send the hand-craft OData query through a native platform request, such as:

insert into NativePlatformScalarRequests
( url
, orig_system_group
) 
select replace('https://start.exactonline.nl/api/v1/{division}/financial/Journals?$select=BankAccountIBAN,BankAccountDescription&$orderby=BankAccountIBAN desc&$top=5', '{division}', code) 
,      'MYSTUFF-' || code
from   systempartitions@datadictionary 
limit  100 /* First 100 divisions. */

create or replace table exact_online_download_journal_top5@inmemorystorage
as
select jte.*
from   ( select npt.result 
         from   NativePlatformScalarRequests npt 
         where  npt.orig_system_group like 'MYSTUFF-%'
         and    npt.result is not null
) npt
join   jsontable
       ( null 
         passing npt.result 
         columns BankAccountDescription varchar2 path 'd[0].BankAccountDescription'
         ,       BankAccountIBAN varchar2 path 'd[0].BankAccountIBAN'
       ) jte

From here on you can use the in memory table, such as:

select * from exact_online_download_journal_top5@inmemorystorage

But of course you can also 'insert into sqlserver'.