[Access] Vervanger gezocht voor slome Access app

[Yagermeister]

Mijn vraag

Een aantal jaar geleden heb ik bij mijn huidige baas een begin gemaakt voor een simpele access database om hierin verschillende processen bij te kunnen houden en/of te automatiseren. Na een jaartje ofzo heb ik de backend omgezet naar een MariaDB database zodat het wat sneller liep. Dit heeft altijd redelijk prima gelopen echter zijn we nu op een punt beland waar we merken dat Access te traag aan het worden is en we dus aan het zoeken zijn naar een vervanger hiervoor.

1 van onze processen waarvoor we dit gebruiken is bijv de inkoop. Dit gedeelte van de app heeft een simpel form met daarin koppelingen met een 4-tal tabellen waaruit data gehaald wordt. Op zich werkt dit scherm wel prima echter is het hoofdscherm onderdeel van een kleine 1500 records waardoor het bladeren en zoeken vrij traag verloopt. Ik heb dit wel versnelt door een filter in te bouwen waarmee we kunnen aangeven of een product wel of niet interessant is. Dit veld wordt overigens ook bijgewerkt door het aantal verkopen zelf maar dat staat buiten dit gedeelte.


Relevante software en hardware die ik gebruik

Backend MariaDB 10.x.x
Frontend Access 2019

Wat ik al gevonden of geprobeerd heb

Ik heb al geprobeerd om de data die we zichtbaar hebben te verminderen helaas is dat niet mogelijk om nog meer te verminderen aangezien we die data net allemaal gebruiken. We zien nu in 1 oogopslag of we een product moeten inkopen en hoeveel stuks aanbevolen is. Al deze data staat verspreid in meerdere tabellen om niet 1 lomp grote database te krijgen. De backend zelf werkt ook zonder prima als ik handmatig de data opzoek.

Wat wij nodig hebben is eigenlijk een programma waarmee we op een (hopelijk) simpele manier een bergje formulieren en queries kunnen maken om zo deze app te versnellen. Helaas is mijn programmeer kennis te min om dit in PHP of Python te doen (of enig andere taal). We hadden al een programma Ninox gevonden wat er op zich interessant is maar of dit goed is weten we dus niet.

Wij zijn wel op zoek naar een (beginnend) programmeur die ons hierin kan helpen mocht er geen programma voor zijn. Deze programmeur zou dan 1 of 2 dagen in de week bij ons werken (het liefste dus iemand in het zuiden) om zo dit project en meerdere andere projecten mee op te pakken en ons te ondersteunen hierin maar die zoektocht is (helaas) nog gaande.

[Lustucru]

Het lijkt me sterk dat Access de oorzaak van de traagheid is. 1500 records is echt helemaal niks. De problemen die je ondervindt zul je -imho- bij elk ander alternatief ook vinden, domweg omdat de onderliggende gedachte gebreken heeft. En voor je laatste alinea is er V&A. Het klinkt als best een leuk klusje waar wel mensen voor te vinden zullen zijn.

[Yagermeister]

Lustucru schreef op dinsdag 16 maart 2021 @ 12:43:
Het lijkt me sterk dat Access de oorzaak van de traagheid is. 1500 records is echt helemaal niks. De problemen die je ondervindt zul je -imho- bij elk ander alternatief ook vinden, domweg omdat de onderliggende gedachte gebreken heeft. En voor je laatste alinea is er V&A. Het klinkt als best een leuk klusje waar wel mensen voor te vinden zullen zijn.

Het gaat zich niet om de programmeur maar bedankt voor die tip.

Het lijkt inderdaad dat 1500 records niet veel is maar gezien de hoeveelheid data (+- een 50 velden per product) is er toch echt vertraging hierin.

Om een voorbeeld te geven van 1 enkel product met de data die we gebruiken:

Hoofdform:
Het totaal aantal orders
Het totaal aantal orders die verkocht zijn in de laatste 90 dagen
Het totaal aantal orders die verkocht zijn in de laatste 60 dagen
Het totaal aantal orders die verkocht zijn in de laatste 30 dagen
De verkoopprijs
De verkoopprijs + 5% marge
Marge webshop 1
Marge webshop 2 (4 kolomen)
Website met meeste verkopen
De voorspelling hoeveel er verkocht gaan worden (5 kolomen)
Hoeveel stuks zijn onderweg
Hoeveel zijn er verkocht
Hoeveel hebben we er nodig (2 kolomen)
Status
en nog een paar kleinere velden

Dan hebben we de volgende subforms ook nog gekoppeld (die splits ik even niet uit)
Alle inkoop orders
Alle verkoop orders
Alle leveranciers met dit product
Prijsberekening
Detailvelden voorraad
Inkooptabel

De meeste sub tabellen hebben alle queries waaruit de data al voorbewerkt is om zo min mogelijk te hoeven rekenen bij het openen van het formulier. Er zijn een kleine 5 velden zoiets waar dit echter niet bij mogelijk is maar dat zou ook niet meer mogen uitmaken gezien de hoeveelheid data.

Van het hoofdformulier heb ik alle velden voor zover als mogelijk ook al omgezet naar vooruitberekende velden zodat ook hier geen berekeningen hoeven plaats te vinden. Wij gebruiken overgiens geen Dlookup of Dcounts meer. Dit is sinds de laatste versie weggehaald geworden door alles van te voren al te laten berekenen en dit op te slaan in de database. Een nadeel hiervan is wel de de data niet meer live is maar dat is op zich niet zo erg. Deze wordt nu elke 2 uren zoiets ververst op de achtergrond in de database rechtstreeks.

Je zou kunnen zeggen dat het wellicht teveel velden/data is echter is elk stukje net als zodanig nodig dat ik hier niets aan kan weg halen. Alle queries zijn ook gemaakt om zo min mogelijk data op te halen. Het probleem lijkt ook niet erin te zitten dat er teveel tabellen/velden zijn als deze open staat echter zodra je gaat bladeren duurt het echt soms een paar seconden voordat het scherm omspringt ofwel er is teveel data die opgeroepen moet worden.

Net van dit laatste stukje zou ongetwijfeld meer mogelijk moeten zijn om dit te versnellen maar ik weet dus niet wat. Hierdoor was ons eerste idee dan ook nog een andere soort codeless solution te kijken en ondertussen mogelijk een programmeur te pakken die wellicht dit kan verbeteren door de opbouw wellicht anders te maken.

[Voor 6% gewijzigd door Yagermeister op 16-03-2021 14:10]

[Lustucru]

1500 x 50 is nog altijd niks en een trage schermopbouw hoeft ook niet veroorzaakt te worden door een te grote dataload. Maar zonder de database te zien valt er weinig te zeggen waar je kunt optimaliseren. In elk geval moet je niet verwachten dat je probleem wordt opgelost door een ander platform neer te zetten.Jammer van de tijd die je erin gaat steken om dat te leren kennen. Die energie kun je beter besteden aan een analyse van het probleem of inderdaad, in het zoeken naar iemand die het voor je/jullie (of je/jullie kan helpen) kan oplossen.

[sig69]

Analyseer je queries eens. Misschien valt er met een aantal indexjes een flinke/afdoende winst te halen.

[BCC]

Hoeveel regels zitten er in de tabellen, zitten er indexen op en hoeveel ram heeft MariaDB beschikbaar? Een beetje mysql met indexen kan zo een paar miljoen regels per seconde doorstappen mits goed afgericht.

[Yagermeister]

Lustucru schreef op dinsdag 16 maart 2021 @ 18:15:
1500 x 50 is nog altijd niks en een trage schermopbouw hoeft ook niet veroorzaakt te worden door een te grote dataload. Maar zonder de database te zien valt er weinig te zeggen waar je kunt optimaliseren. In elk geval moet je niet verwachten dat je probleem wordt opgelost door een ander platform neer te zetten.Jammer van de tijd die je erin gaat steken om dat te leren kennen. Die energie kun je beter besteden aan een analyse van het probleem of inderdaad, in het zoeken naar iemand die het voor je/jullie (of je/jullie kan helpen) kan oplossen.

We gaan nog nergens tijd insteken als we niet weten of het wel nut heeft. Afgaande van de huidige reacties is dat in ieder geval een nee.

sig69 schreef op dinsdag 16 maart 2021 @ 18:17:
Analyseer je queries eens. Misschien valt er met een aantal indexjes een flinke/afdoende winst te halen.

Dat is een goede. Voor zover ik weet denk ik dat de meeste indexjes wel goed staan. Ik zou hier echter nog eens een goede blik op kunnen werpen aangezien het alweer een tijdje geleden is dat ik hiermee gewerkt heb. Ik ga er dan wel stiekem vanuit dat ik de indexen die ik in SQL heb staan ook gebruikt kunnen worden in access zelf.

BCC schreef op dinsdag 16 maart 2021 @ 18:22:
Hoeveel regels zitten er in de tabellen, zitten er indexen op en hoeveel ram heeft MariaDB beschikbaar? Een beetje mysql met indexen kan zo een paar miljoen regels per seconde doorstappen mits goed afgericht.

De inkoop/verkoop zijn de 2 grootste tabellen met elk een kleine 300k aan records verdeeld over een 10-tal kolommen. Dan het leveranciers gedeelte zal ergens rond de 100k zitten en de rest allemaal rond de 3 a 4k max. De database server heeft zo even uit mijn hoofd 32GB ter beschikking (pure VM waar niets anders op draait + SSD storage). Ik betwijfel of het probleem ook wel bij mysql ligt aangezien de database gewoon prima reageert als ik bijv phpmyadmin of datatables gebruik om diverse databases in te kijken. Wel moet ik dan erbij zeggen dat ik geen beeld heb of deze nog steeds lief reageert als ik ineens 10 tabellen op 1 pagina gooi (geen idee hoe ik dat doe) om dus te zien of dat wellicht toch het probleem is.

Ik ga in ieder geval eens stoeien met wat meer indexen. Ik heb er nu namelijk niet superveel wat het probleem mogelijk kan verklaren.

[Edit]
Toevoeging. Nu valt me net wel te binnen waarom ik in bepaalde gevallen geen indexen heb gemaakt. In sommige tabellen zoals bijv de inkoop en verkoop is SKU het veld wat alles aan elkaar knoopt. Het probleem wat ik echter altijd tegenaan liep is dat dit veld dus niet uniek is en daardoor ook geen index kan zijn. Mocht er iemand daar een oplossing voor weten dan hoor ik het graag.

[Voor 7% gewijzigd door Yagermeister op 16-03-2021 21:20]

[sig69]

Een index hoeft helemaal niet uniek te zijn hoor. Maar op de gok wat indexen toevoegen heeft geen zin natuurlijk. Ken je het EXPLAIN commando? https://mariadb.com/kb/en...MNS%20FROM%20tbl_name'%20

[Voor 75% gewijzigd door sig69 op 16-03-2021 22:48]

[Lustucru]

Yagermeister schreef op dinsdag 16 maart 2021 @ 21:17:
[...]
De inkoop/verkoop zijn de 2 grootste tabellen met elk een kleine 300k aan records verdeeld over een 10-tal kolommen. Dan het leveranciers gedeelte zal ergens rond de 100k zitten en de rest allemaal rond de 3 a 4k max.

Dit wordt een tikje serieuzer, maar het is niet de oorzaak.

De database server heeft zo even uit mijn hoofd 32GB ter beschikking (pure VM waar niets anders op draait + SSD storage). Ik betwijfel of het probleem ook wel bij mysql ligt aangezien de database gewoon prima reageert als ik bijv phpmyadmin of datatables gebruik om diverse databases in te kijken.

Dit riekt naar het probleem. Je hebt dus een aparte db-server en gebruikt Access als frontend? Gezien je opmerkingen over dlookup verwacht ik niet veel kennis van Access en neem ik even aan dat je je querys en je subforms vooral hebt gedefinieerd in access? In dat geval doet die database server zo goed als niks. Bij het opbouwen van de pagina wordt de hele tabel over de lijn getrokken en het arme frontendje mag uitzoeken wat hij daarmee aan moet. Zonder enige optimalisatie, nuttige indexen of wat dan ook. Access probeert nog wat te redden met local caching maar met enige pech gaat alles met een dynamische lokale cursor. Dat wordt traag, heel traag...

[Lustucru]

Anders gezegd: als deze analyse klopt dan kun je je backend optimaliseren tot je een ons weegt. Echt sneller wordt het er niet van.

[DJMaze]

Marge webshop N

Hmmm als ik dit zo lees heb je een professional nodig en €50.000+

Andere optie: omnichannel software

[Yagermeister]

sig69 schreef op dinsdag 16 maart 2021 @ 22:46:
Een index hoeft helemaal niet uniek te zijn hoor. Maar op de gok wat indexen toevoegen heeft geen zin natuurlijk. Ken je het EXPLAIN commando? https://mariadb.com/kb/en...MNS%20FROM%20tbl_name'%20

Ik heb toevallig gisteren nog even zitten knutselen en ook gevonden hoe ik dit moet doen. Ik gebruik overigens Navicat voor mijn werkzaamheden hierin. Dit is blijkbaar een andere tab waar je dan een aparte index kunt aanmaken. Ik heb het hier toevallig net met mijn baas over gehad toen hij de nieuwe versie deed testen maar hij gaf al meteen aan dat tussen de records switchen op zich niet zo erg is en dit gaat voor hem snel genoeg (+-1 sec max) om alle data op te halen. Het probleem ligt meer als hij gaat zoeken naar een product.

Dit is ook heel simpel te verklaren aangezien de tabel waarin access zoekt is uiteraard gigantisch groot met een ruime 30 kolommen en een kleine 4k records. Wat ik begrepen heb van access is dat de zoekfunctie de volledige database doet doorzoeken. Dit zorgt dus voor een enorme vertraging waar ik eerlijk gezegd niet zeker van weet hoe ik dit moet aanpassen.

Lustucru schreef op dinsdag 16 maart 2021 @ 23:02:
[...]


Dit wordt een tikje serieuzer, maar het is niet de oorzaak.


[...]


Dit riekt naar het probleem. Je hebt dus een aparte db-server en gebruikt Access als frontend? Gezien je opmerkingen over dlookup verwacht ik niet veel kennis van Access en neem ik even aan dat je je querys en je subforms vooral hebt gedefinieerd in access? In dat geval doet die database server zo goed als niks. Bij het opbouwen van de pagina wordt de hele tabel over de lijn getrokken en het arme frontendje mag uitzoeken wat hij daarmee aan moet. Zonder enige optimalisatie, nuttige indexen of wat dan ook. Access probeert nog wat te redden met local caching maar met enige pech gaat alles met een dynamische lokale cursor. Dat wordt traag, heel traag...

Ik heb inderdaad niet superveel kennis van access maar door de jaren heen heb ik wel veel dingen verbeterd waardoor dit nog steeds redelijk werkbaar is (zie hierboven het commentaar). Alle queries etc gebeuren inderdaad lokaal door access zelf maar hier zitten geen extreme dingen. Het is veelal 1 tabel wat steeds opgeroepen wordt door de query en dat is dan ook alles. Er is maar 1 subform waar een query inzit die 3 tabellen gebruikt maar deze is ook gelimiteerd en snel.

Het probleem waar ik dus net achter ben gekomen zit dus vooral in het zoeken als het scherm open staat. Deze tabel is te groot om makkelijk zoeken mogelijk te maken.

DJMaze schreef op dinsdag 16 maart 2021 @ 23:13:
[...]

Hmmm als ik dit zo lees heb je een professional nodig en €50.000+

Andere optie: omnichannel software

Dit zou een optie kunnen zijn maar dan gaan we niet daarkomen met 50k. Er komt steeds weer iets nieuws wat we willen gaan gebruiken en dan kunnen we weer de beurs trekken. Op zich werkt het nu ook wel echter zou het gewoon wat sneller mogen zijn.

[sig69]

Yagermeister schreef op woensdag 17 maart 2021 @ 12:08:
[...]
Het probleem ligt meer als hij gaat zoeken naar een product.

Dit is ook heel simpel te verklaren aangezien de tabel waarin access zoekt is uiteraard gigantisch groot met een ruime 30 kolommen en een kleine 4k records. Wat ik begrepen heb van access is dat de zoekfunctie de volledige database doet doorzoeken. Dit zorgt dus voor een enorme vertraging waar ik eerlijk gezegd niet zeker van weet hoe ik dit moet aanpassen.
[...]

Nogmaals: 4 k records is peanuts. Hoe zoek je in Access? Ik heb het al 20 jaar niet meer gebruikt, is er een standaard zoekfunctie (die dan dus blijkbaar je hele database/tabel doorploegt)? Of heb je een formulier met een tekstveld of iets dergelijks, waarna je zelf een query afvuurt?

[Yagermeister]

sig69 schreef op woensdag 17 maart 2021 @ 12:23:
[...]

Nogmaals: 4 k records is peanuts. Hoe zoek je in Access? Ik heb het al 20 jaar niet meer gebruikt, is er een standaard zoekfunctie (die dan dus blijkbaar je hele database/tabel doorploegt)? Of heb je een formulier met een tekstveld of iets dergelijks, waarna je zelf een query afvuurt?

Nou, wij gebruiken de standaard functie.

Nu ben ik echter zojuist ook even verder gaan graven aangezien @Lustucru me de hint ervoor gegeven heeft. De queries worden nu uiteraard lokaal gedraaid. Access heeft echter ook de pass-through query functie (wist ik ook niet) en als ik de query van het hoofdveld daarin gooi en hem dan laat zoeken gaat de snelheid van +-6 seconden terug naar ongeveer +- 1 seconde. Dat is dus een wereld van verschil.

Ik laat de baas hiermee testen om te zien wat dit opleverd en als dit goed bevalt ga ik ze allemaal daarin gooien.

[Lustucru]

Je zoekfunctie deugt dus niet. Haal een query op met enkel die velden die je wilt doorzoeken, bepaal de key van het resultaat en haal dan de rest op. Zoeken in 4k records? <0.1 sec max.

[Lustucru]

Yagermeister schreef op woensdag 17 maart 2021 @ 12:08:
[...]

Dit zou een optie kunnen zijn maar dan gaan we niet daarkomen met 50k. Er komt steeds weer iets nieuws wat we willen gaan gebruiken en dan kunnen we weer de beurs trekken. Op zich werkt het nu ook wel echter zou het gewoon wat sneller mogen zijn.

Dat is wat ik haat aan de IT-wereld. Roep 50K en iedereen neemt je serieus. Om eens goed naar je database te kijken zou ik denk ik een dag nodig hebben, een dag om aanpassingen te doen en een dag om het allemaal met een lintje erom heen weer over te dragen. Ruim gerekend...

[Yagermeister]

Lustucru schreef op woensdag 17 maart 2021 @ 16:11:
Je zoekfunctie deugt dus niet. Haal een query op met enkel die velden die je wilt doorzoeken, bepaal de key van het resultaat en haal dan de rest op. Zoeken in 4k records? <0.1 sec max.

Laat dit net het "probleem" zijn. De query zoals deze nu is haalt ook alleen de benodigde velden op. Met de nieuwe query zoals ik deze eerder vanmiddag in gebruik genomen heb, is dit van een ruime 6 sec terug gegaan naar onder de seconden. Meestal zie je niet eens de cursor verpspringen dus dat helpt gigantisch. Nu had ik als idee om dus overal pass-through queries van te maken maar helaas werkt dat niet in combinatie met subform's. Daar kreeg ik een melding van dat dit niet mogelijk was (iets te snel weggeklikt om nuttiger te zijn). Hier ga ik echter nog eens heen kijken. Wellicht dat ik dit kan afvangen door er misschien een view van te maken. Dan is de query ook al gedraaid op de database server.

Lustucru schreef op woensdag 17 maart 2021 @ 16:17:
[...]

Dat is wat ik haat aan de IT-wereld. Roep 50K en iedereen neemt je serieus. Om eens goed naar je database te kijken zou ik denk ik een dag nodig hebben, een dag om aanpassingen te doen en een dag om het allemaal met een lintje erom heen weer over te dragen. Ruim gerekend...

Ik betwijfel ook dat het zoveel moet kosten. Iemand die er verstand van heeft (en de tijd) zou dit zeer zeker binnen een vrij korte tijd geregeld krijgen aangezien de grootste winst ongetwijfeld zit in het optimaliseren van de queries is. Deze zijn allemaal zeer standaard en ook simpel opgebouwd. Vrijwel elke query is puur de data van een enkele tabel ophalen.

Voor nu lijkt het echter prima opgelost te zijn door de query van het hoofdformulier om te zetten naar een passthrough query waardoor het hele systeem een flink stuk sneller aanvoelt en werkt. Voor zover ik even kort met mijn baas hierover gesproken heb heeft hij hiermee genoeg tijdwinst om weer een tijdje vooruit te komen.

Later dit jaar willen we dit toch gaan omzetten naar iets anders zodat we eventueel ook van thuis uit aan de data kunnen komen of het middels een website/php/python iets gaat, om zo de verschillende systemen die we in gebruik hebben samen te krijgen onder 1 dak. Dat maakt het hele onderhoud ervan ook een stuk makkelijker.