Mogelijkheid om uitsluitend de wijzigingen in te laden vanuit Exact Online naar Excel

Vragen
,
2

(Bericht verplaatst van categorie “Klussen” naar “Vragen (nl)”.)

Zonder aanmelden is niet haalbaar omdat de data uit Exact Online moet komen. Ik zou adviseren om eerst te beginnen met optimalisatie zodat het vlotter werkt. Deze bijvoorbeeld aan de volgende alternatieve query:

select sle.OrderNumber
,      sle.LineNumber
,      sle.DeliveryDate
,      year(sle.DeliveryDate)
,      month(sle.DeliveryDate)
,      sle.DeliveryStatus
,      sle.ItemCode
,      sle.ItemDescription
,      sle.Quantity
,      sle.OrderStatus
,      sle.QuantityDelivered
,      sle.QuantityInvoiced
,      sle.NetPrice
,      sor.OrderedByName
,      sor.Description
,      itm.ItemGroupCode
,      itm.ItemGroupDescription
from   ExactOnlineREST..SalesOrderLinesBulk sle
join   ExactOnlineREST..SalesOrders sor
on     sor.Division    = sle.Division
and    sor.OrderNumber = sle.OrderNumber
join   ExactOnlineREST..Items itm
on     itm.Division = sle.Division
and    itm.code     = sle.ItemCode
and    itm.ItemGroupCode in (1,16)
where  sle.DeliveryStatus = 12
order 
by      sle.LineNumber

De gemaakte aanpassingen zijn hieronder beschreven.

Overbodige left outer weghalen

De left outer zijn weggehaald omdat in de oorspronkelijke in de where stond: c.ItemGroupCode in (1,16). Dat kan uitsluitend waar zijn als er ook een artikel gevonden is, want anders was ItemGroupCode niet gedefinieerd (null).

Verplaatsen filter

Het filter op ItemGroupCode hoort in het logische datamodel dicht bij de join te staan, namelijk in de on. Daardoor wordt het ook makkelijker om juist met of zonder left outer te werken. Dit filter op artikelgroep is daarom naar de on op Items (artikelen) verplaatst.

Vereenvoudigen tabelnamen

Als een deel van de elementen van de tabelnaam al leidt tot een unieke match, dan hoeft maar een deel opgegeven te worden. Daarom is bijvoorbeeld ExactOnlineREST.SalesOrder.SalesOrderLines vervangen door ExactOnlineREST..SalesOrderLines.

Vervangen tabelaliassen

De tabelaliassen a, b en c zijn niet erg veelzeggend. Daarom zijn ze vervangen door aliassen volgens de Invantive methode.

SQL functies in kleine letters

Structureel gebruik van kleine letters met hoofdletters verhoogt de leessnelheid en herkenning van problemen blijkt uit onderzoeken. Daarom zijn YEAR en MONTH vervangen door year en month.

Volgorde kolommen in ON

Advies voor betere leesbaarheid is om de specificaties op te schrijven bij gelijk-joins met links de naam van de tabel die gekoppeld wordt en rechts de herkomst, daarom bijvoorbeeld:

sor.OrderNumber = sle.OrderNumber

Administraties meenemen

Waarschijnlijk gaat deze SQL query over maar één administratie, maar het is altijd verstandig om fouten te voorkomen door een uniek kenmerk van de administratie mee te nemen in de joins. Vandaar:

join   ExactOnlineREST..Items itm
on     itm.Division = sle.Division
and    itm.code = sle.ItemCode

Gebruik bulk API

Het filter deliveryStatus = 12 behelst alleen open verkooporderregels. Dit zal bij gelijkblijvende omzet een redelijk constant aantal regels zijn. Omdat deliveryStatus zowel via SalesOrderLines als SalesOrderLinesBulk server-side filterbaar is, is overgestapt op de *Bulk versie.

Echter, voor SalesOrders en Items heeft gebruikmaken van de *Bulk API voor een vlotte uitvoering geen nut. Omdat Invantive SQL standaard voor lage aantallen rijen terugvalt op een join set worden puntsgewijs plukjes bijpassende verkooporders en artikelen opgehaald. Zelfs al zijn er 100 miljoen verkooporders of artikelen, dan nog zal bij bijvoorbeeld 250 regels hoogstens 250 keer een punt-query uitgevoerd worden op elk. Nog sterker; Invantive SQL bundelt meerdere verzoeken per API call waardoor de rate limits niet gauw overschreden worden. De bundelfactor hangt af van de beschikbare ruimte (zeg 1500 tekens) en de lengte van het zoekcriterium (zeg hier 60 tekens).

Meer achtergrond over join sets is te vinden in de SQL grammar en:

Verwachting

De snelheid van de Exact Online API’s varieert nogal per dag, maar uitgaande van 500 open verkooporderregels, 3 regels per order en 10 verschillende artikelen zal de duur van uitvoering ongeveer 5 seconden zijn:

  • 1 seconde voor 1 API call voor de 500 verkooporderregels,
  • 2 seconden voor 4 API calls voor de 150 verkooporders,
  • 1 seconde voor 1 API call voor de 10 artikelen,
  • 1 seconde voor de joins.

Als de Exact Online API’s zwaarder belast zijn dan zal dit mogelijk 3x zo lang duren.