Sådan optimerer du ydeevnen af ​​Access-databaser i dybden

Når en Access-database begynder at køre langsomt, giver forbindelsesfejl eller fryser lejlighedsvis, er det ikke tilfældigt. Access-ydeevnen afhænger af både dens interne design og serveren, netværket og hvordan vi bruger formularer, forespørgsler og VBA . Den gode nyhed er, at der er mange ting, du kan prøve, før du overvejer at skrotte alt og omskrive applikationen.

I virkelige miljøer er det meget almindeligt at starte med en Access-applikation, der er vokset gennem årene: lagerbeholdning, ordrer, kvalitetskontrol, rapporter ... alt sammen i den samme fil, nogle gange med SQL Server eller Oracle bagved, andre gange med ren Access. Læg dertil flere brugere, ODBC-forbindelser og ressourcekrævende formularer, og du har den perfekte opskrift på klager over langsom ydeevne . Lad os roligt, men direkte, se på, hvordan du maksimerer ydeevnen af ​​dine Access-databaser i forskellige scenarier.

Analyser og diagnosticer ydeevne i Access

Før du begynder at finjustere målinger, kode eller hardware, er det første skridt at måle og diagnosticere, hvad der sker . Uden i det mindste en vis analyse er det nemt at spilde timer på at optimere de ting, der har mindst indflydelse.

Access inkluderer et mindre kendt, men meget nyttigt værktøj: Performance Analyzer . Dette værktøj er kun tilgængeligt for desktopdatabaser (ikke for .adp-projekter eller Access-webapplikationer) og giver Access selv mulighed for at gennemgå strukturen og dataene og foreslå ændringer.

Performance Analyzer gennemgår tabeller, forespørgsler, formularer, rapporter og relationer og genererer tre typer resultater: anbefalinger, forslag og ideer. Anbefalinger er "nemme" ændringer uden større bivirkninger, som Access kan anvende automatisk. Forslag involverer potentielle kompromiser, som du bør læse omhyggeligt i notefeltet. Idéer er forbedringer, som du selv skal implementere trin for trin.

For at bruge den skal du blot åbne databasen, gå til fanen Databaseværktøjer og starte Performance Analyzer . Derfra kan du vælge specifikke objekter (f.eks. en ny tabel, du lige har oprettet) eller vælge alle objekter til en komplet gennemgang. Når du har kørt analysen, kan du markere optimeringerne som Anbefalinger eller Forslag og klikke på "Optimer" for at få Access til at anvende dem med det samme.

Ud over Performance Analyzer er det en god idé at bruge fejllogfiler, forespørgselsanalyse og overvågning af netværks- og serveradfærd . Blot at registrere den tid, det tager for hver forespørgsel at udføre eller hver formular at åbne, hjælper med at identificere specifikke flaskehalse.

Komprimering og reparation: essentiel vedligeholdelse for Access

Selvom det ofte overses, er komprimering og reparation af en Access-database essentiel vedligeholdelse . Ved daglig brug fyldes .mdb- eller .accdb- filen op med tom plads, ineffektive indeks og ubrugte data, hvilket resulterer i øget diskplads og langsommere svartider.

Komprimering reorganiserer filens interne lager for at eliminere fragmentering og genvinde spildplads. Efter mange indsættelser, opdateringer og sletninger vokser filen uden nødvendigvis at øge mængden af ​​nyttige data. Komprimering reducerer denne døde plads, gør dataene mere kompakte og fremskynder læse- og skriveoperationer.

Reparationsprocessen forsøger derimod at rette skader og fejl i tabeller, indekser eller interne strukturer, der kan være forårsaget af uventede nedlukninger, hardwarefejl, netværksafbrydelser eller systemnedbrud. Reparation af en database hjælper med at forhindre progressiv korruption og datatab , hvilket er afgørende for applikationer, der bruges dagligt.

Disse er komplementære processer: komprimering fokuserer på ydeevne og størrelse, mens reparation fokuserer på dataintegritet . Det er normalt at komprimere ret ofte, især efter store ændringer (massesletning, import osv.), og at reparere, når der begynder at dukke fejlmeddelelser, usædvanlige nedbrud eller problemer med at åbne bestemte objekter op.

Før du komprimerer eller reparerer Access eller andre eksterne værktøjer, er det vigtigt at oprette en fuld sikkerhedskopi af filen . Du kan gøre dette manuelt ved at kopiere .mdb/.accdb-filen til et andet drev eller planlægge automatiske sikkerhedskopier med sikkerhedskopieringssoftware. Denne sikkerhedskopi er dit sikkerhedsnet, hvis noget går galt under processen.

Manuel udførelse er enkel: Åbn Access uden at indlæse databasen, gå til Filer > Info, og vælg "Komprimer og reparer database ". Vælg derefter filen, og lad Access gøre sit arbejde. For at reducere behovet for manuel indgriben kan du aktivere indstillingen "Komprimer ved lukning" i Filer > Indstillinger > Aktuel database, så Access automatisk komprimerer databasen, hver gang du lukker den.

Hvis du vil gå et skridt videre, kan du automatisere komprimering og reparation med makroer eller VBA og planlægge dem til at køre på et bestemt tidspunkt eller når bestemte betingelser er opfyldt (for eksempel når en bestemt filstørrelse er nået).

Genkendelse af, hvornår Access skal optimeres

Der er flere symptomer, der nærmest er et råb om hjælp fra din database. Hvis Access tager længere tid at åbne, køre simple forespørgsler eller indlæse velkendte formularer, er databasen sandsynligvis fragmenteret eller dårligt optimeret . Du kan også se periodiske fejlmeddelelser, midlertidige frysninger eller fejl i at gemme poster.

En anden vigtig indikator er den uforholdsmæssigt store vækst i filstørrelsen . Hvis .mdb/.accdb-filen bliver ved med at blive større, uden at du tilføjer mange nye data, er den en klar kandidat til komprimering. Det samme gælder, hvis du bemærker, at visse tabeller tager lang tid at åbne eller filtrere data, selv med få poster.

Udover at undersøge selve filen er det værd at kontrollere computerens og netværkets samlede ydeevne : mål RAM-ydeevne , diskfragmentering, CPU-forbrug, belastningen på filserveren eller SQL-serveren (hvis relevant) og forbindelseskvaliteten (latens, pakketab osv.). En veldesignet Access-database kan køre frustrerende langsomt, hvis netværket er dårligt.

Der er indbyggede værktøjer, der hjælper med denne diagnose: Access' ydeevneovervågning, fejlloggennemgang, forespørgselsanalyse og filteregenskaber . Og selvfølgelig observation af faktisk brug: hvilke handlinger udføres hyppigst, hvilke formularer bruges oftest, hvilke rapporter tager lang tid at generere osv.

Optimer adgang med SQL Server-backend eller andre servere

Når Access fungerer som en klient til en database som SQL Server eller Oracle, kommer klient/server-logikken i spil . Den gyldne regel er klar: minimer returture til serveren og udnyt dens processorkraft i stedet for at hente data tilfældigt til klienten.

Access-projekter (.adp) og Access-databaser med tabeller, der er linket til SQL Server, bruger OLE DB eller ODBC til at oprette forbindelse til serveren . Access fungerer typisk med opdaterbare snapshot-postsæt: den indlæser de nødvendige data i en lokal cache og tillader derfra browsing, filtrering eller ændring uden at anmode serveren om hver brugerhandling.

For at udnytte denne model fuldt ud er det bedst at udføre det tunge arbejde på serveren : brug lagrede procedurer, triggere, brugerdefinerede funktioner og velskrevne SQL-forespørgsler. Fordi en lagret procedure er prækompileret og findes i SQL Server, vil den typisk udføres hurtigere og mere ensartet end den samme logik, der sendes fra Access i dynamiske SQL-stykker.

På samme måde giver triggere dig mulighed for at anvende komplekse forretningsregler direkte på tabeller (f.eks. forhindre visse rabatter på produkter under en bestemt pris), hvilket undgår behovet for at replikere denne logik på tværs af flere Access-formularer. Brugerdefinerede funktioner kan returnere tabeller eller skalarværdier og forenkle komplekse forespørgsler ved at indkapsle logikken.

Et andet vigtigt punkt er at begrænse mængden af ​​data, der overføres fra SQL Server til Access . Brug af visninger, veldefinerede WHERE-klausuler, serverfiltre og postgrænser (egenskaben MaxRecords eller knappen Maksimal postgrænse) reducerer netværkstrafikken og belastningen på klienten betydeligt. Access giver dig mulighed for at definere serverfiltre via egenskaben ServerFilter i formularer og rapporter, så filtrering udføres, før poster hentes.

Der er flere specifikke egenskaber, der hjælper med at reducere unødvendige ture til serveren. For eksempel styrer `PerformResync` , om Access gensynkroniserer posten efter opdateringer. Deaktivering af det i scenarier, hvor det ikke er nødvendigt, reducerer yderligere anmodninger. Egenskaben `Hent standardværdier` bestemmer, om standardværdier hentes fra serveren for nye poster, så snart formularen åbnes. Hvis du ikke har brug for dem, kan du indstille den til `Nej` for at forhindre dette kald.

Design og optimer SQL Server-databasen

Det er ikke meget nyttigt at have en velafstemt Access-frontend, hvis SQL Server-databasen er dårligt designet . Der er en række principper, der gør en forskel for ydeevnen.

For det første, en tabelstruktur uden redundante data og med god normalisering . Ved at undgå unødvendig duplikering, tydeligt definere primære og fremmednøgler og anvende referentiel integritet (fremmednøgler) reduceres uoverensstemmelser og forespørgselsoptimeringsværktøjet fungerer bedre.

Det hjælper også at vælge den rigtige datatype og en rimelig minimumsstørrelse for hver kolonne. Brug af mindre typer, når det er muligt (f.eks. små heltal i stedet for store heltal, realistiske tekstlængder), reducerer diskpladsen og forbedrer hastigheden af ​​joinforbindelser og søgninger.

Indekser er et andet nøgleelement: det er tilrådeligt at indeksere felter, der bruges til sortering, sammenføjning eller anvendelse af kriterier i forespørgsler, både i SQL Server og Access. For mange indekser eller dårligt designede indekser kan dog have en negativ indflydelse på indsættelses-, opdaterings- og sletningshandlinger. Nøglen er at finde den rette balance og undgå overlappende indekser, der redundant dækker præcis de samme kolonner.

I datalager- eller rapporteringstunge miljøer kan indekserede visninger repræsentere en betydelig ydeevneforøgelse, da de gemmer det materialiserede resultat og accelererer summer, gennemsnit og aggregeringer på store mængder. Opdateringshandlinger på de underliggende tabeller er dog dyrere, fordi visningen skal vedligeholdes.

Det er også meget nyttigt at tilføje et tidsstempelfelt (timestamp/rowversion) til tabeller med mange kolonner eller høj opdateringsfrekvens. SQL Server kan bruge dette felt til hurtigt at bestemme, om en post er ændret, i stedet for at sammenligne alle kolonner, hvilket forbedrer både ydeevne og pålidelighed i miljøer med flere brugere.

Endelig hjælper periodisk komprimering og reorganisering af indekser og data i SQL Server (genopbygning/reorganisering) med at holde forespørgsler agile og sparer diskplads, især hvis der er mange skrivehandlinger.

Sammenkædede tabeller, OLE DB, Oracle og andre kilder

Når Access opretter forbindelse til andre kilder, f.eks. ekstern SQL Server, Excel, tekstfiler, HTML eller Oracle, er det meget vigtigt, hvordan denne forbindelse er konfigureret. Sammenkædede tabeller, der er oprettet med guiden Sammenkædning, kan udnytte sammenkædede servere eller OLE DB-kilder på serveren , hvilket giver SQL Server mulighed for at udføre noget af fletnings- og filtreringsarbejdet direkte.

Hvis du linker til en anden SQL Server-instans, anbefales det generelt at oprette en linket server i SQL Server og vælge denne indstilling i guiden , så selve systemet optimerer serverkombinationerne. Men hvis du opretter forbindelse til filer som Excel , tekst eller HTML, er det normalt bedre at bruge en OLE DB-datakilde baseret på Transact-SQL, som i disse tilfælde giver bedre ydeevne end en generisk linket server.

I blandede miljøer, for eksempel et program, der eksporterer rapporter fra Oracle 11g og fra Access ved hjælp af Crystal Reports eller et andet bibliotek, er Access ofte betydeligt langsommere end Oracle til at returnere data . Dette skyldes normalt forskelle i ODBC/OLE DB-drivere, tabelstruktur, indekser og frem for alt fordi Access ikke er designet til den samme mængde eller arbejdsbyrde som en velkonfigureret Oracle-server.

I disse tilfælde er der flere strategier: optimering af de forespørgsler, der bruges af rapporten, justering af ODBC-forbindelsen, reduktion af antallet af returnerede kolonner og rækker, og om muligt flytning af beregninger og aggregeringer til serveren i stedet for at udføre dem på klienten. Hvis hullet stadig er betydeligt, er det værd at overveje, om Access er den rette backend, eller om det bør begrænses til en grænseflade, mens dataene altid befinder sig i en mere robust motor.

Lettere formularer, underformularer og kontrolelementer

Formularer er nok den del af Access, hvor brugeren mærker langsomheden mest. En tung formular med mange billeder, underformularer og unødvendige kontrolelementer kan tage flere sekunder at åbne , selv med få data.

En meget effektiv strategi er at forhindre formularen i automatisk at indlæse data, når den åbnes, hvis der ikke er behov for det. Du kan designe den, så den i starten ikke har nogen postkilde eller et filter, der ikke returnerer noget, og tilføje en "Søg"- eller "Indlæs data"-knap, der udløser forespørgslen, når brugeren anmoder om det. Dette er især nyttigt for tabeller med tusindvis eller millioner af poster.

Det er også en god idé at opdele store felter, såsom billeder eller meget lange tekster, i underformularer eller sekundære formularer, der kun åbnes, når brugeren klikker på knappen "Mere information". Dette holder hovedformularen let og giver en meget mere jævn brugeroplevelse.

I applikationer med fokus på intensiv dataindtastning (f.eks. ordreindtastning) anbefales det at bruge specifikke formularer med egenskaben DataEntry indstillet til Ja , så de kun viser en ny tom række og ikke behøver at hente og vise eksisterende poster.

På kodeniveau indlæses en formular uden sit eget modul hurtigere og bruger mindre hukommelse. Hvis du ikke har brug for hændelsesprocedurer i den formular, kan du fjerne det tilknyttede modul ved at indstille HasModule til Nej. Og hvis du har brug for logik, kan du overveje at placere funktionerne i standardmoduler og kalde dem fra hændelsesprocedurer med udtryk (offentlige funktioner) i stedet for at have snesevis af unødvendige formularmoduler.

Listebokse og kombinationsbokse spiller også en rolle: det er meget mere effektivt at basere dem på gemte forespørgsler end på lange SQL-strenge, der er integreret i RowSource-egenskaben. Desuden er det tilrådeligt kun at inkludere de strengt nødvendige kolonner og sikre, at det første viste felt og dets afhængige felt er indekseret.

Rapporter og dataadgangssider

I tilfælde af rapporter opstår der normalt problemer ved håndtering af store datamængder. Forsøg på at udskrive en massiv rapport på én gang kan overbelaste Access og printeren . En fornuftig løsning er at opdele rapporter i delmængder (efter dato, efter region, efter afdeling osv.) ved hjælp af visninger, lagrede procedurer eller filtre i rapportens datakilde.

Når man arbejder meget med planlagte rapporter (ugentligt, månedligt, kvartalsvis), er det meget praktisk at generere og distribuere Snapshot-filer (.SNP) fra Access . Rapporten produceres én gang, og brugerne kan se den med Snapshot Viewer, hvilket eliminerer behovet for at køre den ressourcekrævende forespørgsel hver gang.

For dataadgangssider, der vises i Access eller Internet Explorer, er nøglen til hurtig indlæsning at reducere antallet af kontrolelementer og undgå overlappende objekter . Det hjælper også at bruge sammenkædede span-kontrolelementer i stedet for tekstbokse til ikke-redigerbare data og at minimere brugen af ​​bitmaps og tung grafik.

På grupperede sider anbefales det at indstille ExpandedByDefault til False for gruppen på topniveau for at forhindre den i at forsøge at udvide alt fra starten, og at bruge lave værdier for DataPageSize for at indlæse færre poster pr. blok. Hvis der er en-til-mange-relationer, er det bedst at basere hver sektion direkte på tabeller i stedet for komplekse forespørgsler, medmindre du specifikt har brug for at udføre filtrering eller aggregering på forhånd.

Filtre, søgning og avanceret brug af indekser

Formularfiltre og serversidede formularfiltre er meget praktiske, men de kan tage en evighed at vise listen over værdier, hvis der er mange poster . Access giver dig mulighed for at styre denne funktionsmåde fra databaseindstillingerne, hvor du kan angive, om disse lister skal udfyldes fra et lokalt snapshot, serveren, begge eller ingen af ​​delene, samt definere, hvor mange poster der er nødvendige for at stoppe med at vise lister over værdier.

For hver tekstboks i en formular giver egenskaben FilterLookup dig mulighed for at finjustere tingene yderligere. Hvis en liste tager for lang tid eller ikke er nyttig, kan du indstille den til Aldrig. Hvis det er vigtigt at se forskellige værdier til filtrering, kan du indstille den til Altid eller Databasestandard og derefter justere de globale grænser.

Parallelt hermed gør en god indekseringsstrategi en kæmpe forskel. Et indeks er simpelthen en struktur, der ordner værdierne i en eller flere kolonner og giver dig mulighed for at finde rækker meget hurtigere , ligesom indekset i en bog. Du bør fokusere på kolonner, der ofte optræder i WHERE-klausuler, JOIN-kombinationer og ORDER BY-klausuler.

Der findes flere typer nyttige indekser: unikke indekser , som forhindrer dubletter; primære nøgleindekser , som normalt er unikke og tjener til entydigt at identificere hver række; og klyngede indekser , som fysisk sorterer tabellen på disken i henhold til nøglen og tilbyder meget hurtig adgang, selvom der kun kan være ét pr. tabel.

Den generelle anbefaling er kun at oprette indeks, hvor de virkelig tilfører værdi , og undgå duplikerede definitioner (to indeks, der indeholder de samme kolonner i samme rækkefølge). Og test selvfølgelig altid: opret et indeks, mål dets indflydelse på kritiske forespørgsler, og verificer, at de ekstra skriveomkostninger er acceptable.

Ved at kombinere disse teknikker – regelmæssig vedligeholdelse, godt tabeldesign, servereffektive forespørgsler, lette formularer, intelligent brug af indekser og justeringer af filtre og kontrolelementer – kan en Access-database gå fra at være en flaskehals til et agilt og stabilt værktøj, selv i miljøer med flere brugere og backends som SQL Server eller Oracle.

Med fornuftig planlægning, forbedringsiterationer og lytning til reel brugerfeedback (det, som en tekniker anser for at være "hurtigt nok", kan stadig være frustrerende for brugeren), er det fuldt ud muligt at få Access til at køre problemfrit og stabilt uden tilfældige ODBC-fejl, kontinuerlige nedbrud og svartider under de tre sekunder, der gør hele forskellen i daglig brug.