Bedankt voor controle. Dat is vervelend. Als ik mag gokken, dan lijkt het er op dat AFAS tussen pagina’s (take) niet meer gegarandeerd state vasthoudt en ook geen ordening standaard toevoegt en ook geen error geeft als application control als je het vergeet.
We hebben aan Invantive SQL een connectorattribuut toegevoegd analysis-enforce-row-uniqueness die indien geactiveerd van een resultset controleert of alle rijen uniek zijn. Zo nee, een error geeft.
Die attribuut kun je niet altijd aan laten staan omdat er sowieso tabellen zijn op talrijke platforms die geen set maar een bag zijn, maar is wel een manier om het te valideren of het probleem opgelost is en bij terugkomst het ook snel analyseerbaar te maken.
We hebben daarna een default sortering toegevoegd op de GetConnectoren. Ik heb nog wat vraagtekens of de sortering moet gaan over een unieke sleutel of dat die over een niet-uniek veld moet gaan. Ik vermoed dat bij sorteren over alleen een niet-uniek veld met lage cardinaliteit het probleem blijft bestaan, maar alleen minder vaak terugkomt. De AFAS documentatie beschrijft daar nog niks over, maar vanuit het gedrag van de AFAS API verwacht ik dat als je alleen sorteert op bijvoorbeeld een boolean veld je nog steeds dubbelen en verdwenen rijen zult meten. We gaan het zien.
De AFAS metadata bevat weinig informatie om te bepalen of iets een zinvolle sortering is. Je kunt niet zien of een veld verplicht is bijvoorbeeld, of het een onderdeel van een primaire/unieke sleutel is voor de view en of het veld in de AFAS database überhaupt geïndexeerd is. Zeker met grote tabellen wil je niet een order by uiteindelijk opwekken die begint met een aantal velden die niet geïndexeerd zijn. Dan moet hij telkens een ordening opbouwen vermoed ik.
Voor de sortering worden alle velden meegenomen mits:
- Als het een tekst is, alleen als de lengte bekend is en maximaal 250 tekens om te voorkomen dat op LOB’s gesorteerd wordt.
- Als het een getal is, alleen als er geen cijfers achter de komma staan.
- De rest volledig.
De volgorde van de velden in de sortering is:
- Indien beschikbaar
Administratie_nummer(3 cijfers). - Veld eindigt met
id(en heeft geen cijfers achter de komma). - Daarna de datum/tijd velden.
- Daarna de Guid’s.
- Daarna de Int64, Int32 en Int16.
- En daarna de rest.
Tenslotte wordt na toepassen van de volgorde het geschatte aantal bytes in de waardes beperkt tot 75% van 8062.
De toegevoegde sortering vertraagt de API’s wel erg lijkt het. De doorvoorsnelheid vanuit AFAS voor resultsets met paar honderdduizend rijen is een factor 5 lager dan zonder sortering.
Workaround voor het performanceverlies zou nog kunnen zijn om ook een filter toe te voegen op basis van de ordening, zodat de API call voor een pagina specifiek aangeeft vanaf welke waardes er opgehaald mag worden, maar dat vereist testen tegen het daadwerkelijk gedrag. Hetzelfde geldt voor automatisch splitsen in deelgroepen zoals je zelf handmatig al hebt gedaan. Dat kan op administratie als partitie, maar ook daarnaast of in plaats daarvan op een subpartitie zoals debiteurnummer. Het ontbreken van statistieken over de data voor de eerste keer gegevens ophalen maakt dit wat lastiger.
Al met al voelt deze aanpak voor mij nog steeds redelijk onbetrouwbaar op Profit 15. Uiteraard is het mogelijk om de sortering handmatig te gaan opgeven bij elke API call, maar dat betekent dat een gebruiker of consultant die Invantive SQL gebruikt verstand moet hebben van de AFAS API terwijl de belofte van Invantive SQL is dat alle draaiende onderdelen onder de motorkap blijven. Voor doorgewinterde AFAS-kenners is dat geen punt, maar veel gebruikers bouwen ook koppelingen tussen meerdere pakketten voor gegevensuitwisseling en dan is het macro- en micro-economisch beter als je met minder kennis meer resultaat kunt bereiken.
Mocht fout gaan door de toegevoegde sortering, dan kun je de toegevoegde sortering uitschakelen via de AFAS-specifieke connectorattribuut analysis-add-default-sort-get-connectors. Standaard heeft deze de waardes true, maar stelt je wilt geen sortering meegeven aan Get Connector API requests dan geef je eerst op:
set analysis-add-default-sort-get-connectors false
en daarna voer je de queries uit.