Hoe maak ik Exact Online uren query op PjtTimeTransactions efficiënter?

Vragen
, ,
1

Ik haal data op uit Exact online. De API van Exact is blijkbaar veel langzamer geworden. Hierdoor duurt mijn simpele query nu heel lang. Het gaat typisch om minder dan 500 regels die opgehaald worden en daar is hij tussen de 15 en 20 minuten mee bezig…
Ik ben dus op zoek naar hoe ik mijn query efficiënter kan maken zodat het ophalen van de data sneller gaat. Dit is de query:

select ProjectCode
,      ProjectDescription
,      ItemDescription
,      Employee
,      Date
,      sum(Quantity)
from   ( select Hourstatus
         ,      ProjectCode
         ,      ProjectDescription
         ,      ItemDescription
         ,      Quantity
         ,      employee
         ,      date
         ,      to_char(Date, 'YYYY') year
         ,      to_char(Date, 'IW') week 
         from   ExactOnlineREST.Project.PjtTimeTransactions
       )
where  hourstatus <> "2" 
and    year > "2021" 
and    week = $X{'Overview Per Person'!C1} 
and    week < '53'
group
by     projectcode
,      ProjectDescription
,      ItemDescription
,      Employee
,      date
order
by     Employee
,      date
,      projectcode
,      ItemDescription
2

Vermijden filteren op afgeleide velden

In deze real-time query op Exact Online worden een aantal filters toegepast op afgeleide velden. Er wordt eerst bijvoorbeeld een jaar berekend met een to_char en vervolgens hierop gefilterd. Daardoor is het niet mogelijk om het filter op jaar door te geven naar de server (zogenaamde “server-side filtering” of zoals het bij Power BI heet: “query folding”).

Lees voor meer informatie: How to filter data from OData query on Azure Data Factory? - 3 van forums.

Beter is om het andersom te doen: maak van de parameter een waarde(bereik) in het juiste datetime data type.

Vervang daarom:

and    year > "2021"

door een where binnen de sub-query zoals bijvoorbeeld:

where  Date between to_date('20210101', 'YYYYMMDD') and to_date('20211231', 'YYYYMMDD')

Hierdoor zal alleen data uit 2021 nog opgehaald worden in plaats de rijen van alle jaren waarna lokaal alleen 2021 doorgelaten wordt. Dit zal hoogstens 10x sneller worden.

Het kan eens 50x sneller worden door de weekberekening mee te nemen, bijvoorbeeld door in plaats van 20210101 en 20211231 de begin- en einddatum van de gewenste week mee te geven. De volgende where-clause wordt dan overbodig:

and    week = $X{'Overview Per Person'!C1}

Vermijden impliciete conversie

In de query wordt waardes vergeleken met kolommen. Hierbij is het datatype aan de linker- en rechterzijde verschillend:

where  hourstatus <> "2" 
...
and    year > "2021" 
...
and    week < '53'

Doordat de datatypes links een getal zijn en rechts een tekst (waar ook “A” in zou kunnen staan), moet voor de kolom aan de linkerzijde een afgeleide gemaakt worden. Eigenlijk staat er dus:

where  to_char(hourstatus) <> "2" 
...
and    to_char(year) > "2021" 
...
and    to_char(week) < '53'

Los van de lokaal extra benodigde rekentijd leidt dit ook tot het probleem hierboven genoemd. Vermijd daarom impliciete conversies. Een betere manier om het te schrijven is:

where  hourstatus <> 2

De snelheid zal hiermee maximaal pakweg 2x verder stijgen, maar waarschijnlijk minder omdat year en week al eerder getackled zijn.

Hoeveel sneller is het na deze aanpassingen?

3

Inmiddels is er ook een tot 1.000x snellere versie van PjtTimeTransactions beschikbaar gekomen. De tabel heet PjtTimeTransactionsIncremental. Zie ook:

4

Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.