Access/SQL applicatie is traag...

[Verwijderd]

First: ik ben geen developer en het kan dus gebeuren dat ik een domme opmerking maak, bij voorbaat excuses...

Bij een klant van ons moeten ik samen met de ontwikkelaar van een CRM applicatie de trage werking van de applicatie oplossen. Ik heb een kort gesprek gehad en hieruit vloeien een aantal opmerkingen voort welke ik even tegen de GoT community aan wil houden.

Probleem: de CRM applicatie is op tijden niet vooruit te branden, 30 seconden wachttijd om naar een ander venster te gaan is geen uitzondering. Netwerkbelasting en belasting van de server hebben we al bekeken en deze is niet extreem hoog te noemen.

Access opent de database die op een SQL server staat via een ODBC koppeling. Volgens de ontwikkelaar is één van de redenen dat de app. op tijden zo traag is dat Access de database open houdt. Volgens de ontwikkelaar is er niets meer aan performance te halen binnen de applicatie, we zullen over moeten naar b.v. .NET maar dit is een langdurig proces.

De ontwikkelaar geeft aan dat een losse SQL server het probleem op zou moeten lossen, maar zoals ik al aangaf is deze niet extreem zwaar belast. De testomgeving van de ontwikkelaar is altijd single user, kan het niet simpel een tabel o.i.d. zijn die open blijft staan waardoor de andere gebruikers deze vertraging merken?

- Zijn er tweaks welke de snelheid van Access kunnen verhogen, timeouts, andere routines, optimalisatie?
- Is het inderdaad zo complex om van Access naar .NET te gaan?
- Hoe groot is de kans dat het inzetten van een losse SQL server het probleem oplost?
- Is de multiuser omgeving het probleem, kan ik de ontwikkelaar nog andere tips geven?

[silverstorm]

Ik ben zelf ook niet een echte programmeur. Dus ik kan je niet alles vertellen, maar een deel wel.

Zijn er tweaks welke de snelheid van Access kunnen verhogen, timeouts, andere routines, optimalisatie?
=> Niet echt. Querys beter uit te laten voeren, kan wat opleveren. Verder zijn er mij geen echte "tweaks" bekend.
- Is het inderdaad zo complex om van Access naar .NET te gaan?
=> Zelf ben ik geen .NET-mannetje, dus ik weet het antwoord hier niet op.
- Hoe groot is de kans dat het inzetten van een losse SQL server het probleem oplost?
=> Als de server de vraag om antwoord te lever nu goed aankan (dus geen uitschieters naar stijging van 20%CPU of enorm geheugengebruik), lijkt me het niet noodzakelijk om over te gaan naar een nieuwe server.
- Is de multiuser omgeving het probleem, kan ik de ontwikkelaar nog andere tips geven?
=> Access en multi-user is (vaak) een ramp. Ik ben tijdje als functioneel beheerder van Access geweest. De meeste databases draaien/draaiden beter op een andere basis.

[Verwijderd]

Access97 aan SQL2000 heb ik hier ook nog draaien. In afwachting van het herschrijven van alle software naar één nieuw solide pakket in VB .NET zit ik er ook aan vast dus.

Ik heb gemerkt dat je winst kan boeken door de volgende zaken:
- Kopieer de .mdb file van access naar het systeem van de gebruiker. Pas de shortcuts aan en je zult een gigantische boost vinden t.o.v. een .mdb op een netwerkshare.
- Vervang zoveel mogelijk access queries door stored procedures op de server. Alles wat je op de server doet is wezenlijk sneller dan de ellende die je op je hals haalt als je access queries laat uitvoeren.
- Probeer zo weinig mogelijk tabellen en queries aan een scherm te koppelen. Hoe meer je koppelt hoe zwaarder het allemaal wordt en zeker in combinatie met een .mdbtje op een netwerkshare stort je performance door de grond.
- Gebruik Views op de server ipv queries in access als basis voor andere queries en handelingen. SQL server is gewoon wezeloos veel sneller dan het immer vreselijk slome access.

Vergeet niet dat access bij sommige bewerkingen (met name sorteren) gewoon de hele tabel wil hebben en dat je dus regelmatig je tabellen opschoont. maak van je tabellen dus history versies met een apart interface om er in te zoeken. een miljoen records is voor SQL server niet zo'n issue maar access ziet door de nullen de één soms niet meer. Uiteraard moet je niet vergeten dat je indexen op tabellen moet zetten op die dingen waaorp je zoekt en sorteert.

Met deze eenvoudige wijzigingen hebben we van een pakket met 50 gebruikers waar de performance ongeveer was zoals jij hem beschrijft weer een prima werkbaar pakketje gemaakt, en zo heel moeilijk was het allemaal écht niet. Ik heb een flink deel zelf gedaan en ik ben ook helemaal geen programmeur.

[Verwijderd]

Access opent de database die op een SQL server staat via een ODBC koppeling.

en

De ontwikkelaar geeft aan dat een losse SQL server het probleem op zou moeten lossen, maar zoals ik al aangaf is deze niet extreem zwaar belast.

lijken mij in tegenspraak.

Access is eigenlijjk 2 producten in één. Een (nogal belabberde) database backend en een aardige Frontend. Ik neem aan dat je applicatie in ieder geval gebruikt maakt van Access als frontend. Echter je eerste opmerking over het gebruik van ODBC geeft aan dat de gebruikte backend niet meer die van access zelf is. Als je toch de backend van Access gebruikt is het upsizen van je access database naar SQL Server inderdaad een goed idee. Maar als je via ODBC al wat anders gebruikt dus niet.

Daarnaast is Access is in principe ontworpen voor single user situaties. Ga je het met meer dan 5 man gebruiken loop je al snel tegen grote problemen aan. Maar dit probleem zit voornamelijk in de backend. Door de Access backend te vervangen door SQL Server kun je toch flinke performance winnen terwijl de frontend behouden kan blijven. Overstappen op .NET kost een stuk meer tijd dan enkel het vervangen van de backend maar heeft zeker bij grotere aantallen gebruikers zeker zin. Maar of het noodzakelijk is voor een betere performance is moeilijk in te schatten zonder kennis van het product, het aantal gebruikers enz enz.

Dat de ontwikkelaar altijd single user test is natuurlijk vreemd. Het programma moet multiuser werken. Dat betekent óók dat je in je ontwerp met multi user problemen rekening moet houden en dus dat soort problemen zou moeten testen Natuurlijk, een ontwikkelaar kan moeilijk tientallen gebruikers simuleren maar de basis zaken als locking en race condities zouden zeker getest moeten zijn.

Ook zegt netwerkbelasting niet alles. Bij access gaat het juist vaak ook om kleine pakketjes (bv of een record wel of niet geupdate mag worden) Als het dan lang duurt voordat Access zekerheid heeft dat het mag merk je ook een grote vertraging.

Nu maar eens de vragen:

kan het niet simpel een tabel o.i.d. zijn die open blijft staan waardoor de andere gebruikers deze vertraging merken?
Natuurlijk. Maar bij het bewerken van data in access zul je altijd gegevens open moeten hebben. Tenzij je zo'n beetje alle controls van access negeert en zelf de gehele database communicatie gaat regelen. In dat geval ben je véél beter uit met bv .NET Ik kan niet in de code van de ontwikkelaar kijken maar ik neem aan dat de meest grote fouten wel verholpen zijn. En openen in 30 seconden is nog altijd openen. Het duurt dus erg lang maar het wordt niet volledig geblokkeerd. Bij een tabel die intern gelocked blijft zou je het scherm helemaal niet meer te zien krijgen.

- Zijn er tweaks welke de snelheid van Access kunnen verhogen, timeouts, andere routines, optimalisatie?
Ja, maar geen magische oplossingen. Meestal is het een kwestie van uitzoeken wat exact het probleem veroorzaakt en dan komt er meestal ook automatisch een oplossing uit. Vaak door problematische routines te herschrijven met concurrency in het achterhoofd maar soms ook door botweg zaken lokaal uit te voeren en pas later op de gedeelde database te doen.
- Is het inderdaad zo complex om van Access naar .NET te gaan?
Ja, Access heeft net zoveel gemeen met NET als een Man met een Vrouw. Erg weinig dus.
- Hoe groot is de kans dat het inzetten van een losse SQL server het probleem oplost?
Hangt sterk af van het exacte probleem en de hoeveelheid aanpassingen aan de ACCESS code. Maar dit is zeker een stap in de goede richting.
- Is de multiuser omgeving het probleem, kan ik de ontwikkelaar nog andere tips geven?
Waarschijnlijk wel en tips voor de ontwikkelaar. Testen, testen testen.

[StevenK]

Je geeft aan dat Access via ODBC gaat naar de SQL Server.

1. Is die SQL Server er één als in 'MS SQL server' of is 't één of andere database-server die een SQL dialect praat ? In het eerste geval kan Access namelijk ook native tegen het ding praten.
2. Bij tijd en wijle traag zou ik analyseren door naar zowel de hoeveelheid netwerkverkeer te kijken, want het kan best zijn dat de boel zo brak geprogrammeerd is dat voor een simpele search de hele database over de lijn wordt getrokken, omdat de access-via-odbc niet weet of kan weten dat het ook server-side kan.
3. Daarna zou ik met SQL profiler kijken wat er precies in de database gebeurt wanneer de boel traag is. Het is bijvoorbeeld mogelijk dat een bepaalde actie een lock zet, waar de rest op wacht.

[Koffie]

Move PNS > PW

[P_de_B]

Zoals StevenK al zegt, je moet er eerst achter zien te komen wat er precies traag gaat. Je kunt hiervoor de SQL Server Profiler gebruiken, maar je zou ook een aantal van de queries in Query Analyzer kunnen uitvoeren en dan het executieplan bekijken. Dit geeft goed aan waar precies de bottleneck in een bepaalde query zit. Beide tools maken deel uit van de 'client tools' die op iedere SQL Server cd staan.

Hier kun je zien hoe je het executieplan moet bekijken. Het belangrijkste is dus eerst er achter komen _wat_ er precies traag is.

[Boss]

Het hangt er ook een beetje vanaf hoe die schermen eruit zien. Ik heb laatst een database-versnel klusje gehad met ongeveer hetzelfde probleem als wat jij beschrijft. Er werd gebruik gemaakt van schermen met daarop 6-7 tabbladen. Op al die tabbladen in totaal meer dan 25 comboboxes met verwijzingen naar andere tabellen. Het openen en open houden van zoveel tabellen en indexen kon Access niet aan (was zelfs nog een doc over te vinden op MSDN, kan ik wel voor je opzoeken als je t nodig hebt).

Oplossing was om het hoofdscherm in 2-en te delen zodat niet al die indexen tegelijkertijd geopend hoefden te worden. Snelheidsprobleem volledig opgelost.

En daarnaast wat hierboven ook genoemd is: Als het een MS-SQL server is, gebruik dan absoluut geen ODBC. Dat is wel de manier om het langzaam te krijgen. Als het een MS-SQL server is kan je het beste de database omzetten met de Upsize Wizard. Let er wel op dat je dit goed moet testen, omdat niet al je queries vlekkeloos omgaan.

Mocht je geen MS-SQL server hebben maar een andere, vervang die dan indien mogelijk. Ook voor de ontwikkelaar zal dat in de toekomst een stuk makkelijker werken. Mocht geld een probleem zijn hierbij dan kan je ook de gratis MSDE downloaden en installeren, waarmee je tot ongeveer 8 gebruikers ook nog prima aan de slag kan.

Multi-user is zeker het probleem niet, mits de 'client' overal lokaal staat en niet iedereen dezelfde frontend gebruikt. Een losse SQL server gaat het probleem ook niet oplossen.

[Verwijderd]

Allen bedankt voor de reacties!

Zoals ik al aangaf ben ik geen dev. en heb ik maar weinig inzage in de interne structuur van de applicatie. Ik kan dus geen uitsluitsel geven over openstaande tabellen o.i.d.

Wel is het zo dat het feit dat de ontwikkelaar niet met deze oplossingen en vragen kwam een bewijs voor mijn vermoeden dat de beste man niet zo kundig is als hij aangeeft. Voorlopig gaan we proberen de MDB's lokaal te zetten we zullen zien of het verbetering brengt.