Come posso rendere più efficiente un query su PjtTimeTransactions in Exact Online?

Domande (nl)
, , ,
1

Recupero dati da Exact Online. Pare che l’API di Exact sia diventata molto più lenta. Di conseguenza, la mia semplice query ora richiede moltissimo tempo. Si tratta tipicamente di meno di 500 righe da recuperare, eppure ci mette tra i 15 e i 20 minuti…
Sto quindi cercando di capire come rendere la mia query più efficiente in modo che il recupero dei dati sia più veloce. Ecco la 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.