Enkele weken geleden is een belangrijke uitbreiding doorgevoerd door ervoor te zorgen dat we de informatie uit de volgende tabel kunnen ophalen bij Loket:
Dit was een belangrijke uitbreiding om live te kunnen en te kunnen gaan verkopen richting veel salarisadministratiekantoren. Destijds heb ik onderstaand probleem ook al een keer aangedragen, maar zonder resultaat nog.
Om de juiste parameters mee te geven, en om de jaar/periode erbij te krijgen, joinen we nu via 3 tabellen. Zie onderstaande query die we nu gebruiken. Deze is echter onwerkbaar langzaam. Voor één werkgever met een paar honderd werknemers duurt het laden meer dan 24 uur terwijl ik alléén 2020 laad (heb hem daarna maar afgekapt). Als we grote administratiekantoren gaan binnenhalen met tientallen werkgevers dan moet dit veel sneller gaan, anders is het niet werkbaar.
Is deze query correct is? In de query probeer ik voor één werkgever, alleen 2020 te laden uit die tabel en die komt er al niet doorheen.
Zoals dit destijds een ‘showstopper’ was omdat de tabel niet beschikbaar was voor download is het nu een showstopper omdat we de gegevens niet over krijgen (duurt te lang en klapt er daarna uit).
Wellicht kunnen we kijken of we deze kunnen optimaliseren zodat het sneller gaat?
create or replace table loonstaat_2020@inmemorystorage
as
select prt.*
from LoketNlRest.Employment.EmploymentsByEmployerId(‘XXX’) ebe
JOIN LoketNlRest.PayrollPeriodData.PayrollPeriodDataByEmploymentId(ebe.id) ebi
on ebi.payrollPeriod_year between 2020 and 2020
JOIN LoketNlRest.Payslip.PayrollPeriodResultsByEmploymentIdAndYear_ContentPayrollPeriodsPayslipTypesPayrollComponentResults(ebe.id, ebi.payrollperiod_year) prt
ON ebi.payrollperiod_periodnumber = prt.payrollperiod_periodnumber
select /*+ ods(true, interval ‘0 seconds’) / count()
from loonstaat_2020@inmemorystorage
De query ziet er goed uit. Er zijn twee mogelijke problemen:
de grenzen van de Loket API zijn bereikt; deze heeft beperkingen voor grote downloads omdat er veel met punt queries gewerkt wordt
de Invantive optimizer haalt er niet het optimale uit.
Om te kijken welk van beiden speelt: gaarne de query opsplitsen in drie stappen en telkens hierbij een in-memory tabel maken met de resultaten. Die tabel gebruiken in de volgende stap.
Hoe is de loopduur dan?
En per losse query svp in sessionios@datadictionary kijken hoeveel rijen er bij komen.
Als dat nog steeds langzaam is dan is beste om gezamenlijk met Loket in gesprek te gaan voor het toevoegen van API’s die met lijsten en filters werken in plaats van punt-queries.
Een eenvoudiger query die hetzelfde probleem vertoont is:
select *
from PayrollAdministrationPayrollRunPeriodComponentResults
where prt_payrollComponent_key = 260
and epr_employerNumber = 21121
and prn_payrollPeriod_year = 2021
and prn_payrollPeriod_periodNumber = 1
limit 100
Deze Loket SQL query loopt enkele minuten, terwijl als de view uitgeschreven wordt naar SQL statements en de where clause ingevouwen, dan duurt het enkele seconden.
Na overleg intern is bevestigd dat de Invantive SQL Optimizer hier nog verbeteringsmogelijkheden qua executiepad onbenut laat. Mochten die verbeteringsmogelijkheden gerealiseerd worden, dan is niet zeker dat de prestaties voldoen aan de verwachtingen, maar dan is in goede samenwerking met Loket development ongetwijfeld er nog een mouw aan te passen.
De verbeteringsmogelijkheden zien we vooral in het “invouwen” van criteria in het executiepad. Het invouwen zorgt er voor dat waar het kan (transitiviteit) criteria op een hoger niveau ook lager gebruikt worden. Dergelijke optimalisaties zijn al veel voorhanden op tabel-gebaseerde queries, maar Loket gebruikt vrijwel uitsluitend tabelfuncties met parameters. Verbeteringen zullen ook doorwerken waarschijnlijk in een beperkt aantal Teamleader views en NMBRS die beiden ook veel puntqueries gebruiken en daardoor tabelfuncties.
De SQL optimizer van Invantive is er in twee SQL versies: de (huidige) versie 1 en de toekomstige versie 2. Versie 2 is significant dynamischer en verbetert vooral de prestaties van complexe queries en PSQL met SQL blokken.
Voor versie 1 onderzoeken we de mogelijkheden voor verbetering van de optimizer. Op basis daarvan kijken we of voor versie 2 deze aanpassing ook nodig is en hoe.
Einde van mei verwachten we beeld te hebben van de haalbaarheid. Als tijdelijke workaround is de aanbeveling om elke stap in de query naar een in-memory tabel te schrijven en die te gebruiken in de vervolgstap. Het zou dan significant sneller moeten zijn.
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.