[Access] Waar zit de grens?

[Verwijderd]

Voor mijn werk heb ik een prachtig personeelsmanagementsysteem gebouwd. Aangezien ik er tussendoor (devven is niet mijn hoofdtaak) mee bezig ben geweest heb ik besloten om het maar in Access te gaan maken.

Nu is Access best leuk, maar die Jet engine maakt me een beetje bang. Waarom?

Aan de personeelsregistratie zit ook een werkdagenplanning gekoppeld. Elke shift dat een werknemer werkt is een record. Nu werken er zo'n 200 man bij ons bedrijf, die elk ongeveer 3 shifts per week draaien. Dit komt dan neer op een recordgroei van 200 * 3 * 52 = 31200 records per jaar. Tel daar nog verlofdagen (deze worden ook geregistreerd) bij op en mijn schatting is 40000 records.

Vanuit deze werkdagenplanning wordt er voor een bepaalde shift een planning op papier gedestilleerd. Dit gebeurt mbv een aantal flinke queries met subselects.

code:

SELECT a.Achternaam, a,Voornaam
FROM Agent a, Planning p
WHERE a.AgentID NOT IN(
SELECT AgentID
FROM Ziekte
WHERE [Datum waarvoor planning wordt gemaakt] BETWEEN Startdatum AND Einddatum)
AND a.AgentID NOT IN(
SELECT AgentID
FROM Afwezig
WHERE [Datum waarvoor planning wordt gemaakt] BETWEEN Startdatum AND Einddatum)
AND p.Datum = [Datum waarvoor planning wordt gemaakt]
AND p.Shift = [Shift waarvoor planning wordt gemaakt]
AND a.StatusAgentID <> 0
AND p.Status = 1;

Dit is de vereenvoudigde versie van de query, irl gaat er nog een query overheen met een TOP 6 predikaat.

Nu draait alles nog snel genoeg, maar ik heb bange vermoedens voor de toekomst. Kan Jet tabellen van deze grootte aan? Gaan forse queries zoals hierboven niet buitengewoon veel tijd kosten? Op dit moment zal het aantal concurrent users niet hoger worden dan 4, dus voor zover ik verwacht zal dat niet voor problemen gaan zorgen. Ook de andere tabellen blijven qua grootte binnen de perken, alleen deze tabel verstoort m'n nachtrust.

Wellicht hebben andere Tweakers ervaring met grote tabellen binnen Access, of oplossingen om problemen te omzeilen. Ikzelf denk aan het verplaatsen van records die in het verleden liggen naar een soort van archief tabel. Dan zou de grootte van de tabel beperkt kunnen blijven tot 20.000 records.

[farlane]

Wij hadden eens een (gammele) databaseapp geproduceerd in Access die bij ongeveer 300.000 records kuren begon te vertonen.

Dat was een slecht opgezette db met veel temptabellen, DELETE en INSERTS en lotsa VB code.

Access werkt op zich best goed, maar als je echt iets wilt hebben dat niet teveel onderhoud vergt zou ik naar de MSDE overstappen.

[Orphix]

Ik verwacht ook niet zoveel problemen met deze aantallen. Besef ook dat er over 2 of 3 jaar zeer waarschijnlijk weer een computer/server staat die 2x zo snel is.

[Sybr_E-N]

Je moet natuurlijk wel een zo optimaal mogelijke db maken, zodat je niet al te ingewikkelde queries hoeft uitvoeren.

[Verwijderd]

Ik zou verwachten dat Access tot 100.000 rijen probleemloos gaat. Dit hangt natuurlijk af van een aantal factoren. Zo drijven joins op grote tabellen Access al snel tot wanhoop. Ook Like queries willen wel eens traag werken.
Maar het grote voordeel is wel dat áls het te traag wordt je heel makkelijk naar SQL Server kan switchen . (Tenzij je natuurlijk in VBA werkt, maar dat is überhaupt af te raden)

[ Voor 0% gewijzigd door Verwijderd op 15-09-2002 18:49 . Reden: Nulletje vergeten ]

[sandergar]

Tenzij je natuurlijk in VBA werkt, maar dat is überhaupt af te raden

Why? Waarom raad je het af en wat is dan beter om een niet te ingewikkelde applicatie te maken?

Als je Access/VBA applicatie gebruik maakt van gelinkte tabellen dan maakt het toch niet uit of je data in Access tabellen of SQL tabellen is opgeslagen? Of praat ik nou wartaal?

[Verwijderd]

farlane schreef op 15 september 2002 @ 17:04:
Wij hadden eens een (gammele) databaseapp geproduceerd in Access die bij ongeveer 300.000 records kuren begon te vertonen.

Dat was een slecht opgezette db met veel temptabellen, DELETE en INSERTS en lotsa VB code.

Access werkt op zich best goed, maar als je echt iets wilt hebben dat niet teveel onderhoud vergt zou ik naar de MSDE overstappen.

Woei! Dat klinkt veelbelovend! Temptabellen heb ik (op dit moment) maar eentje, en ook INSERTS komen niet erg vaak voor!

Orphix schreef op 15 september 2002 @ 17:40:
Ik verwacht ook niet zoveel problemen met deze aantallen. Besef ook dat er over 2 of 3 jaar zeer waarschijnlijk weer een computer/server staat die 2x zo snel is.

Hmmm.... er staan nu P3@450 mHz. Zou natuurlijk leuk zijn als er betere pc's komen, maar dat wordt nog maar afwachten !

Sybr_E-N schreef op 15 september 2002 @ 18:25:
Je moet natuurlijk wel een zo optimaal mogelijke db maken, zodat je niet al te ingewikkelde queries hoeft uitvoeren.

De database is (bijna) helemaal in 3NF. De ingewikkelde queries komen doordat de zieke werknemers, en de werknemers die bijzonder verlof hebben niet op de planning terecht mogen komen. Helaas heeft dit wel een aantal subqueries tot gevolg.

Verwijderd schreef op 15 september 2002 @ 18:49:
Ik zou verwachten dat Access tot 100.000 rijen probleemloos gaat. Dit hangt natuurlijk af van een aantal factoren. Zo drijven joins op grote tabellen Access al snel tot wanhoop. Ook Like queries willen wel eens traag werken.
Maar het grote voordeel is wel dat áls het te traag wordt je heel makkelijk naar SQL Server kan switchen . (Tenzij je natuurlijk in VBA werkt, maar dat is überhaupt af te raden)

De queries die ik op de grote tabellen loslaat bevatten gelukkig geen joins / LIKE. Het aparte is: we hebben hier al een knoert van een SQL server staan. Ik ben alleen bang dat ik:

a. Van automatisering geen toestemming krijg om daar mijn database op te draaien. Er draait nu een database op van een belcomputer. Vele honderduizenden telefoonnummers en miljoenen gegevens van gebruikers (werknemers logging). Helaas is een dronken koorddanser met parkinson nog stabieler dan dat apparaat...

b. Mijn code bevat een brok VBA. Ik neem aan dat je bedoelt dat ik de queries allemaal in DAO had moeten maken? Een optie die ik op de langere termijn wel overweeg.

sandergar schreef op 15 september 2002 @ 19:55:
[...]

Why? Waarom raad je het af en wat is dan beter om een niet te ingewikkelde applicatie te maken?

Als je Access/VBA applicatie gebruik maakt van gelinkte tabellen dan maakt het toch niet uit of je data in Access tabellen of SQL tabellen is opgeslagen? Of praat ik nou wartaal?

Mijn applicatie bestaat nu sowieso al uit een front- en een backend. Ik overweeg zelfs om de frontend lokaal te installeren en de backend op het netwerk te laten staan. Nieuwe updates moeten dan automagisch worden geplaatst.

[farlane]

Verwijderd schreef op 16 september 2002 @ 10:27:
Helaas is een dronken koorddanser met parkinson nog stabieler dan dat apparaat...

LOL

*Ahem..* Terug naar serieus modus ...

[cameodski]

Als je de juiste indexen aanbrengt, moet een beetje RDMS miljoenen records aan kunnen.
Tenzij je natuurlijk erg complexe queries met veel JOINS gaat gebruiken.
Maar de complexiteit van jouw query valt nog best mee.
Ook zou het handig kunnen zijn om bijvoorbeeld ziekte en verlof in één tabel te zetten. Dat scheelt weer een subquery.

[LordLarry]

Access staat niet bekend om z'n hoge aantallen records die die aan zou moeten kunnen. Bij echt grote getallen schijnt het echt mis te gaan. Ik heb hier helaas even geen urls over

[KO]

Ik heb hier een tabel met 1884452 records(heb helaas geen server voor een screenshotje), zolang je goede indexen heb werkt het goed

[Verwijderd]

Ik heb aardig wat databases gemaakt in Microsoft Access. 40.000 records is geen enkel probleem. Zeker met maar 4 concurrent gebruikers. De grens ligt op 100.000. Maar dan wel met meerdere gebruikers die tegelijk lezen, schrijven en verwijderen. Boven dit aantal kun je performance problemen met locking krijgen. Met alleen lezen en toevoegen zonder dat records elkaar in de weg zitten kun je wel > 250.000 records in een Access database hebben. Verder is migreren naar SQL Server altijd mogelijk. Dit is zelfs erg eenvoudig met de gratis Upsizing tool. Je kunt dan trouwens nog steeds met Access als front-end blijven werken. Maak hiervoor in Access link tables naar SQL Server. Gebruiksgemak van Access en kracht van SQL Server. Specifieke vragen? Mail of post gerust.

Cheers.

[Verwijderd]

Verwijderd schreef op 16 september 2002 @ 13:27:
Ik heb aardig wat databases gemaakt in Microsoft Access. 40.000 records is geen enkel probleem. Zeker met maar 4 concurrent gebruikers. De grens ligt op 100.000. Maar dan wel met meerdere gebruikers die tegelijk lezen, schrijven en verwijderen. Boven dit aantal kun je performance problemen met locking krijgen. Met alleen lezen en toevoegen zonder dat records elkaar in de weg zitten kun je wel > 250.000 records in een Access database hebben. Verder is migreren naar SQL Server altijd mogelijk. Dit is zelfs erg eenvoudig met de gratis Upsizing tool. Je kunt dan trouwens nog steeds met Access als front-end blijven werken. Maak hiervoor in Access link tables naar SQL Server. Gebruiksgemak van Access en kracht van SQL Server. Specifieke vragen? Mail of post gerust.

Cheers.

(ik post hier m'n reactie, dan hebben anderen er ook wat aan )

OK. Op dit moment zitten er zo'n 2000 records in de tabel. De gehele handeling (1 DELETE query, dan 1 INSERT, dan 1 UPDATE, dan nog een keer 1 INSERT en een UPDATE: Allemaal op een temptabel. Gegevens komen uit de planningstabel) duurt 7 seconden. Daarnaast moeten de twee INSERT-queries dynamisch opgebouwd worden (Er wordt een TOP x query uitgevoerd worden, waarbij de x een waarde is die ik zelf vooraf wil bepalen. Helaas kan dat in Access niet dus worden de INSERT queries opgebouwd uit twee Strings en een getal. Dit samen duurt dus 7 seconden. Ik ben benieuwd hoe dit zich gaat verhouden als er meer records in de tabel komen.

De tabel Planning ziet er zo uit (een (i) betekent een index):

PlanningID(PK), AgentID(i), Datum(i), WerktijdID(i), Soort, Aanwezig, Aftrek

AgentiID verwijst naar de werknemer, Datum is de datum dat de werknemer ingepland is, WerktijdID verwijst naar de shift die de werknemer draait (in de tabel Werktijd zitten de kolommen Van en Tot). Soort, Aanwezig en Aftrek zijn niet van belang voor het maken van de Planning.

Zijn dit de goede indexen? Bedenk mezelf net dat het misschien ook wel verstandig is om de tabel Agent op bepaalde plekken te indexeren, zeker als deze ook nodig zijn voor het maken van de planning...

Daarnaast: Migreren naar SQL Server. Zoals ik al aangaf hebben we zo'n beestje staan. Als ik een paar mensen lief aankijk is er misschien wel wat mogelijk. Ik neem aan dat je dan gewoon ODBC connecties aanmaakt? De reden dat ik twijfel aan de haalbaarheid hiervan is dat ik niet overal gebruikmaak van DAO. Daarnaast heb ik ook queries met stukjes "code" ertussendoor. zoals:

code:

SELECT a.Achternaam, a.Voornaam
FROM Agent a
WHERE a.DID < DateAdd("m",a.Eerstewerkdag, a.DuurContract);

Slikt SQL Server dit ook... Ik betwijfel het!

Thnx voor alle info alvast! Heeft me alweer een stuk wijzer gemaakt...

[chicky]

Ik heb zelf nog eens de CD gids geript, hier kom je uit op ongeveer 7.000.000 records in de grootste tabel. Dit was geen probleem voor de stabiliteit. Echter wel voor de snelheid, die terugging naar nul.

MS Access is min of meer stabiel tot een totaal db grootte van 2Gb. Als je daar onder blijft, blijft de db het doen, maar heb het dan niet of de prestaties.

Wat je zelf al zegt is om records uit het verleden weg te schrijven naar bestanden buiten de eigenlijke db. Dit is een goede manier, maar als de nog eens data uit deze records wilt halen, waat dit zeker ten koste van de snelheid.

Succes.

[Verwijderd]

chicky schreef op 18 september 2002 @ 10:52:
Ik heb zelf nog eens de CD gids geript, hier kom je uit op ongeveer 7.000.000 records in de grootste tabel. Dit was geen probleem voor de stabiliteit. Echter wel voor de snelheid, die terugging naar nul.

MS Access is min of meer stabiel tot een totaal db grootte van 2Gb. Als je daar onder blijft, blijft de db het doen, maar heb het dan niet of de prestaties.

Wat je zelf al zegt is om records uit het verleden weg te schrijven naar bestanden buiten de eigenlijke db. Dit is een goede manier, maar als de nog eens data uit deze records wilt halen, waat dit zeker ten koste van de snelheid.

Succes.

Records uit het verleden zullen incidenteel nog wel opgevraagd worden, maar dan is snelheid geen issue. Plaats ik die records in een 2e backend, die ik weer koppel aan m'n frontend... Et.. Presto!

[megamuch]

mijn ervaring is dat een access DB groter dan 1 miljoen records het meestal voor gezien houdt. 1 miljoen en meer niet. Maar da's mijn persoonlijke ervaring.

[Martin Sturm]

De voornaamste beperking bij Access is het aantal gelijktijdige gebruikers. Zodra dit 10 of meer is, kun je beter asap overstappen op een ander systeem, ook al zijn je tabellen nog relatief klein .

[Verwijderd]

Martin Sturm schreef op 19 september 2002 @ 13:08:
De voornaamste beperking bij Access is het aantal gelijktijdige gebruikers. Zodra dit 10 of meer is, kun je beter asap overstappen op een ander systeem, ook al zijn je tabellen nog relatief klein .

Er zijn als het goed is niet meer dan 4 concurrent users. Binnenkort maar even het aantal connecties beperken...