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?