Itgenatr005: Invalid query syntax. Queries are limited to 500 or fewer 'OR' conditions

[rda]

Bij het kopieren van facturen uit Autotask naar Exact Online krijg ik de volgende foutmelding:

itgenatr005
Invalid query syntax. Queries are limited to 500 or fewer ‘OR’ conditions.

Het gaat om de query op BillingItems:

  select ive.invoiceNumber
  ,      gat.ID GLAccount
  ,      itm.Id Item
  ,      bim.Quantity
  ,      bim.ItemName Description
  ,      bim.InternalCurrencyTotalAmount AmountFC /* Totaal prijs, altijd alles in EUR. */
  ,      bim.InternalCurrencyRate UnitPrice
  from   invoices@inmemorystorage ive
  join   GLAccountsIncremental@eol gat
  on     gat.Code = '8101'
  join   BillingItems@at bim
  on     bim.InvoiceId = ive.invoiceId
  --
  -- Alles op 'Autotask Factuur'-artikel.
  --
  join   ItemsIncremental@eol itm
  on     itm.Code = 'Autotask Factuur'
  order
  by     ive.invoiceNumber
  ,      itm.Id
  ,      bim.InternalCurrencyTotalAmount desc

[forums]

De foutmelding behelst de sectie onder “Call conditions limit” op https://ww1.autotask.net/help/DeveloperHelp/Content/APIs/REST/General_Topics/REST_Thresholds_Limits.htm:

You can include up to 500 OR conditions in a single call to the API. If a call contains more than 500 OR statements, you will receive the API response, “Invalid query syntax. Queries are limited to 500 or fewer ‘OR’ conditions.”

Voor eenvoudiger herleidbaarheid zal de standaard foutcode itgenatr005 vervangen worden door itgenatr035 in dit geval.

Deze foutmelding treedt bij deze query op als er tenminste 500 verschillende invoiceIDs klaarstaan in invoices@inmemorystorage. Door een ingebouwde optimalisatie wordt automatisch gewisseld van het volledig ophalen van BillingItems naar gefilterd op die benodigd voor de verschillende invoiceIDs.

Het probleem zal waarschijnlijk ook op te wekken zijn via:

select 1 c
from   (select distinct invoiceid from (select invoiceid from BillingItems limit 2500)) ive
join   BillingItems bim
on     bim.InvoiceId = ive.invoiceId

De optimalisatie kan uitgeschakeld worden via een join_set hint, zoals:

select /*+ join_set(bim, invoiceid, 500) */ 1 c
from   (select distinct invoiceid from (select invoiceid from BillingItems limit 2500)) ive
join   BillingItems bim
on     bim.InvoiceId = ive.invoiceId

Dit zorgt er voor dat de optimalisatie alleen gebruikt wordt als er maximaal 500 verschillende invoiceId-waardes zijn. Zijn het er meer? Dan worden alle BillingItems opgehaald en 1-voor-1 gematcht. De snelheid gaat er hiermee meestal niet op vooruit; als er 100.000 billing items zijn binnen Autotask, dan worden alle 100.000 opgehaald. Dit duurt circa 5 minuten.

Een mogelijke tussenoplossing is om de beperking op join set te combineren met een filter dat invoiceid tussen de hoogste en laagste mogelijke waarde filtert.

Via een apart kanaal zal contact opgenomen worden om toegang te krijgen tot de data t.b.v. verdere analyse op een meer performante wijze.

[forums]

Een performance-optimalisatie zal in de volgende versie van de Autotask-driver verwerkt zijn. Deze optimalisatie verandert de criteria als er meer dan 500 elementen van ordenbare datatypes in een join set zitten naar een filter op minimum/maximum waardes.

Naar verwachting zullen de prestaties bij het overschrijden van 500 elementen met gemiddeld een factor 2 kunnen dalen. De achteruitgang van de prestaties kan ook aanmerkelijk groter zijn, maar ook kleiner. De prestaties zullen echter ten allen tijde tenminste zo goed zijn als alle rijen ophalen uit de tabel.

De Autotask performance-optimalisatie voor join sets zal naar verwachting binnen 3 werkdagen in productie genomen zijn op Invantive Cloud. Deze optimalisatie zal ook beschikbaar zijn vanaf release 23.0.62.