Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Autor: Klaus Giesen, Wuppertal
Der Wunsch nach Optimierung von Access-Datenbanken zur Beschleunigung der Informationsbeschaffung ist verständlich und liegt nahe. Schließlich soll eine Datenbankanwendung so schnell wie möglich laufen bzw. die Informationsbeschaffung so schnell wie möglich stattfinden – also eine hohe Performance bieten. Die grundlegende Voraussetzung für eine schnellere, optimierte Datenbankanwendung liegt dabei zum größten Teil im Know-how des Entwicklers. Der folgende Beitrag beschäftigt sich mit den unterschiedlichen Möglichkeiten zur Beschleunigung einer Access-Datenbank.
Optimierung ist die Einstellung von Größen, Eigenschaften und zeitlichen Abläufen zur Steigerung von Qualität und Geschwindigkeit von Prozessen. Diese Definition ist natürlich mit den Wünschen an eine Datenbankanwendung identisch. Dabei gibt es grundsätzlich zwei Ansatzpunkte für mögliche Anpassungen: die Hard- und die Software.
Bei vielen Windows-Anwendungen sind Leistungssteigerungen sehr einfach zu erreichen, indem Sie beispielsweise in Ihren PC eine höher getaktete CPU oder mehr Hauptspeicher einbauen.
Bei einer Access-Datenbank ist diese Vorgehensweise zwar empfehlenswert, aber oft nicht ausreichend. Der Grund dafür liegt nahe: Eine Datenbank-Anwendung hat primär die Aufgabe, große Datenmengen anzuzeigen und zu verarbeiten. Im Hinblick auf die Performance kommt es hier primär auf die optimale Organisation der Daten an. Dies bedeutet, dass schon beim Erstellen des Datenmodells, also dem Grundgerüst einer Datenbank, gravierende Fehler auftreten können. Daraus ergibt sich folgende Konsequenz:
Die Geschwindigkeitsoptimierung einer existierenden Access-Datenbank lässt sich nicht auf einen Schlag erreichen; den berühmten großen Wurf gibt es in diesem Zusammenhang einfach nicht.
Deswegen besteht auch der Optimierungsprozess aus einer Vielzahl von Einzelschritten, die systematisch mit dem nötigen Know-how nacheinander abgearbeitet werden müssen.
Das gewünschte Ziel ist also nur durch die Addition vieler kleiner Optimierungsschritte zu erreichen.
Jede Optimierung sollte bei Ihrem Computersystem beginnen. Zumindest sollten Sie überprüfen, ob Ihr System den gängigen Anforderungen entspricht, und dort gegebenenfalls nachbessern.
Falls Sie eine umfangreiche Access 2000-Anwendung beispielsweise auf einem 100 MHz Pentium mit 32 MByte RAM – so etwas war mal ein schneller Rechner! – laufen lassen wollen, sollten Sie erst dann ernsthaft weiterlesen, nachdem Sie Ihre Hardwareausstattung aktualisiert haben.
Der PC
Doch was heißt das konkret Nun, der heutzutage an jeder Ecke angebotene so genannte Multimedia-PC braucht es überhaupt nicht zu sein. Um mit Access vernünftig arbeiten zu können, sollten Sie aber mindestens über folgende Ausstattung verfügen:
Das ist nach aktuellen Maßstäben eigentlich sehr wenig und deshalb ohne Weiteres erschwinglich.
Die Rolle der Festplatte
Die Festplatte spielt hier übrigens eine wesentliche Rolle, da sie letztendlich der Flaschenhals für alle Lese- und Schreibvorgänge ist. Daher beachten Sie folgende Tipps:
Bild 1: Datenbank exklusiv öffnen
Parallel zu Optimierung der Hardware sollten Sie Ihre Access-Datenbank einer genaueren Untersuchung unterziehen. Hier versucht Access, Sie mit dem Assistenten zur Leistungsanalyse zu unterstützen.
Dieser Assistent kann entweder die gesamte Datenbank oder einzelne Datenbankobjekte analysieren und Optimierungsvorschläge machen. Falls Sie es wünschen, kann der Leistungsanalyse-Assistent außerdem einige änderungen automatisch vornehmen. Der Assistent ist weitgehend selbsterklärend und soll daher nicht weiter erläutert werden.
Dass der Assistent zur Leistungsanalyse seine klaren Grenzen hat und für einen engagierten Access-Anwender nur einen Einstieg bieten kann, liegt auf der Hand. Außerdem ist es ohnehin günstiger, die entsprechenden änderungen von Hand durchzuführen, da der Assistent nicht zwangsläufig nur sinnvolle Vorschläge macht.
Alleiniger Zugriff auf Datenbanken
Falls nur Sie alleine auf eine Access-Datenbank zugreifen, platzieren Sie diese in jedem Fall auf Ihrer lokalen Festplatte und nicht etwa auf einem Netzwerk-Laufwerk.
Gleichzeitig öffnen Sie die Datenbank zur exklusiven Verwendung.
Bild 2: Einstellung der automatischen Komprimierung beim Schließen der Datenbank
Klicken Sie dazu im Dialogfenster öffnen auf das nach unten gerichtete Dreieck neben der Schaltfläche öffnen und markieren Sie den Eintrag Exklusiv öffnen (siehe Bild 1).
Datenbank komprimieren
Verschiedene Aktionen wie z. B. das Löschen von Daten in Tabellen und das Zwischenspeichern von Daten in temporären Tabellen führen zum Aufblähen der Datenbank, da gelöschte Daten nicht komplett aus der Datenbank entfernt werden.
Durch das Komprimieren einer Access-Datenbank über den Menübefehl Extras ( Datenbank-Dienstprogramme ( Datenbank komprimieren und reparieren können Sie die angesammelten Fragmente wieder entfernen.
Praxis-Tipp
Sie können unter Access 2000 das Komprimieren automatisch beim Schließen der aktuellen Datenbank ausführen, indem Sie über Extras/Optionen das Dialogfenster Optionen öffnen. Aktivieren Sie dann im Register Allgemein das Kontrollkästchen Beim Schließen Komprimieren (siehe Bild 2).
Außerdem ordnet Access beim Komprimieren die Datensätze in einer Tabelle optimal an und organisiert die Indextabellen neu.
Deshalb können auch Abfragen durch das Komprimieren beschleunigt werden. Zusätzlich sollten Sie jedoch nach einer Komprimierung jede Abfrage erneut ausführen, damit diese auf Grundlage der aktualisierten Tabellenorganisation neu kompiliert wird.
Datenbank in MDE-Datei konvertieren
Bei der Konvertierung einer Datenbank in eine MDE-Datei wird der Zugriff auf die Entwurfsansicht von Formularen, Berichten und Seiten sowie auf den VBA-Code verhindert.
Darüber hinaus wird auch die Datenbankdatei verkleinert. Als Nebeneffekt dieser optimierten Speichernutzung ergibt sich eine Leistungssteigerung.
Die Konvertierung erfolgt wieder über das Menü Extras/Datenbank-Dienstprogramme. Wählen Sie dort den Befehl MDE-Datei erstellen aus.
Makros konvertieren
Konvertieren Sie eventuell vorhandene Makros zu VBA-Code, da VBA-Code wesentlich schneller als Makro-Code ausgeführt wird. Dazu benötigen Sie noch nicht einmal VBA-Kenntnisse, denn Access stellt auch hierfür einen Assistenten zur Verfügung.
Access konvertiert das Makro und erstellt ein neues Modul mit dem Namen Konvertiertes Makro- [Makroname].
Bild 3: Das Dialogfenster Konvertiere Makro
Wie bereits angedeutet, spielt die Datenstruktur einer Datenbank eine wesentliche Rolle für die Performance.
Die Erläuterung der Normalisierung einer Datenbank würde den Rahmen des vorliegenden Beitrags sprengen. Detaillierte Informationen zu diesem Thema finden Sie allerdings im Praxishandbuch, und zwar in Kapitel 3 des Beitrags Anwendungsentwicklung mit Access (Gruppe 2.2).
Nach dem Entwurf des Datenmodells betrachten wir im Folgenden die Optimierungsmöglichkeiten beim Tabellenentwurf.
Grundsätzliches
Jede Tabelle sollte zur eindeutigen Identifikation eines Datensatzes ein Primärschlüsselfeld besitzen. Die Anwendung kann so schnell auf den betreffenden Datensatz zugreifen. Außerdem ist ein Primärschlüsselfeld zwingend notwendig, wenn diese Tabelle Bestandteil einer Beziehung sein soll.
Auswahl der Datentypen
Wählen Sie die Datentypen so aus, dass diese von der Größe her soeben für ihren Anwendungszweck ausreichen und keinen unnötigen Speicherplatz verschwenden. Dazu zwei Beispiele:
Zum Speichern einer Ganzzahl stehen Ihnen die Feldgrößen Byte, Integer und Long Integer zur Verfügung.
Für diese drei Datentypen gelten die Eigenschaften aus Tab. 1.
|
Datentyp |
Wertebereich |
Speicherplatz |
|
Byte |
0 bis 256 |
1 Byte |
|
Integer |
-32.768 bis 32.767 |
2 Bytes |
|
Long Integer |
-2.147.483.648 bis 2.147.483.647 |
4 Bytes |
Tab. 1: Gegenüberstellung numerischer Datentypen mit ihren Eigenschaften
Betrachten Sie zunächst den Wertebereich. In vielen Fällen kann schon im Voraus abgesehen werden, wie groß der Zahlenwert maximal werden kann, der in einem Feld gespeichert werden soll. Bei der Abteilungsgröße kann zum Beispiel ohne Weiteres abgeschätzt werden, ob die Anzahl der Mitarbeiter in Zukunft auf über 256 steigen wird.
Falls Sie zu einem späteren Zeitpunkt feststellen, dass die Feldgröße für die aktuellen Anforderungen zu klein ist, können Sie die Feldgröße ohne Probleme bei Bedarf nachträglich vergrößern.
Bei Feldern mit dem Datentyp Text wird in der Regel eine Feldgröße von 50 Zeichen voreingestellt. Sie sollten bei jedem Feld einzeln prüfen, ob diese Feldgröße wirklich erforderlich ist.
Zum Speichern einer deutschen Postleitzahl macht dies beispielsweise überhaupt keinen Sinn, weil dazu fünf Zeichen ausreichen.
Je kleiner ein Feld, desto weniger Platz wird belegt. Dies nutzt der Performance.
Indizes
Alle Felder einer Tabelle, über die verknüpft, sortiert oder gefiltert wird, sollten zur Geschwindigkeitsoptimierung indiziert sein. Wenn Sie beispielsweise nach einem Feld suchen, das nicht indiziert ist, muss Access einen “table scan” durchführen. Dies bedeutet, dass die komplette Tabelle geladen und jeder Wert verglichen werden muss.
Beim Einsatz eines Index legt Access intern eine sortierte Indextabelle an, die schnell geladen und durchsucht werden kann. Jeder Eintrag dieser Index-Tabelle enthält einen Zeiger auf den Datensatz der Haupttabelle.
Bild 4: Mehrfelder-Index
Es gilt allerdings auch, dass das Anfügen und Löschen von Datensätzen bei indizierten Feldern länger dauert, weil auch die jeweiligen Indextabellen aktualisiert und neu sortiert werden müssen.
Deshalb sollten Sie auch nur die wirklich benötigten Felder der Tabelle indizieren.
Mehrfelder-Indizes
Eine Tabelle kann auch mehrere Indizes gleichzeitig haben. Ein solcher Mehrfelder-Index bietet sich immer dann an, wenn in einer Abfrage oft nach derselben Feldkombination gesucht oder sortiert wird.
Bei der Tabelle tblPersonal der Beispieldatenbank trifft dies auf die Felder txtNachname und txtVorname zu (siehe Bild 4).
Achten Sie aber darauf, zu einem Mehrfelder-Index nur die unbedingt notwendigen Felder hinzuzufügen.
Zusammenfassung
Das Abwägen der Vor- und Nachteile bei der Verwendung von Indizes stellt ein echtes Optimierungsproblem dar. Orientieren Sie sich an den folgenden Richtlinien:
Eingebundene Tabellen
Bei eingebundenen Access-Tabellen gibt es einige zusätzliche Besonderheiten zu beachten.
Die Verwaltung verknüpfter Tabellen in Access erfordert im Allgemeinen etwas mehr Aufwand. Der Grund dafür ist einfach in der Tatsache begründet, dass es sich in diesem Fall um eine externe Datei handelt und deshalb häufigere Dateizugriffe notwendig sind.
Die Beachtung folgender Richtlinien kann den Bearbeitungsaufwand bei eingebundenen Tabellen minimieren:
Bild 5: Ausgewählte Felder im Abfrageentwurf
Abfragen bieten Ihnen verschiedene Möglichkeiten zur Optimierung an. Da überdies Abfragen normalerweise in einer Datenbankanwendung in größerer Anzahl verwendet werden, ist dieser Abschnitt auch einer der Schwerpunkte dieses Beitrags.
Ausgabemenge von Abfragenbeschränken
Verzichten Sie darauf, beim Entwurf einer Abfrage das * (Symbol für alle Felder) in den Abfrageentwurf zu ziehen.
Bild 6: SQL-Anweisung als Datenherkunft für ein Kombinationsfeld
Fügen Sie statt dessen dem Abfrageentwurf nur die wirklich benötigten Felder hinzu (siehe Bild 5) und deaktivieren Sie die Option Anzeigen, wenn diese Felder nicht angezeigt werden sollen. Oft müssen beispielsweise Felder, nach denen gesucht wird, nicht im Ergebnis angezeigt werden.
Indizierung vonAbfragefeldern
Alle Felder, die beim Abfrageentwurf mit Kriterien verwendet werden, sollten indiziert sein – genauso wie die Felder auf den beiden Seiten einer Verknüpfung (falls Sie diese nicht über Primär- und Fremdschlüssel verknüpfen, die sowieso indiziert sein sollten).
SQL-Anweisungen als Abfragen speichern
Für Formulare, Berichte oder auch Steuerelemente wie Kombinations- oder Listenfelder können als Datenherkunft – neben Tabellen – auch SQL-Anweisungen oder Abfragen angegeben werden (siehe Bild 6).
Beim Speichern einer Abfrage erstellt Access einen “Execution plan” und speichert ebenfalls ab. Beim erneuten Aufruf der Abfrage kann Access auf die Erstellung eines erneuten Plans verzichten, was Zeit spart. Speichern Sie eventuell vorhandene SQL-Anweisungen daher als Abfragen. Wählen Sie anschließend anstelle der SQL-Anweisung den Namen der Abfrage als Datenherkunft aus.
Bild 7: Entwurfsansicht einer Unterabfrage
Bild 8: Ausdrücke in einer Abfrage
Datensätze in Abfragen zählen
Mit Hilfe der Count-Funktion können Datensätze in einer Abfrage gezählt werden.
Verwenden Sie in einem solchen Fall die Syntax Count(*) anstelle von Count([Feldname]).
Die folgende SQL-Anweisung zählt alle Datensätze in der Tabelle tblPersonal der Beispieldatenbank:
SELECT Count(*) AS [Anzahl von PersonalID] FROM tblPersonal;
Das gleiche Ergebnis liefert auch diese Anweisung:
SELECT Count(tblPersonal.PersonalID) AS [Anzahl von PersonalID] FROM tblPersonal;
Die Ausführung dieser Anweisung ist jedoch deutlich langsamer, weil hierbei das betreffende Feld auch auf Nullwerte überprüft wird.
Berechnete Felder in Unterabfragen
Unterabfragen sind Abfragen, die innerhalb einer Abfrage zum Beispiel als Kriterien verwendet werden können. Dabei wird in einer Auswahl- oder Aktionsabfrage eine weitere SQL-SELECT-Anweisung ausgeführt.
Mit Hilfe einer solchen Unterabfrage kann beispielsweise die durchschnittliche Anzahl der Mitarbeiter pro Abteilung berechnet werden. Mit diesem Durchschnittswert können dann alle Abteilungen ermittelt werden, die beispielsweise unterhalb des Durchschnitts liegen (siehe Bild 7).
Vermeiden Sie dabei die Verwendung berechneter Felder oder komplexer Ausdrücke innerhalb der Unterabfrage, weil dadurch die Ausführungsgeschwindigkeit der übergeordneten Abfrage deutlich verringert werden kann.
Dieser Hinweis gilt vor allem für die Verwendung der IIF-Funktion in Unterabfragen.
Ausdrücke in Abfragen
Oftmals sind für die Anzeige in einem Formular oder den Ausdruck in einem Bericht berechnete Ausdrücke notwendig, die auf Tabellendaten basieren. Bei der kleinen Personalverwaltung in der Beispieldatenbank sind dies das Alter und die Dauer der Betriebszugehörigkeit (siehe Bild 8).
Eine Lösung dieser Aufgabe wäre, die Berechnung in der dem Formular zugrunde liegenden Abfrage mit Hilfe eines Ausdrucks vorzunehmen.
Hinweis
Die Berechnung erfolgt mit Hilfe der benutzerdefinierten Funktionen Alter() und Dauer(), die Sie im Modul modAccOpt der Beispieldatenbank finden.
Anschließend werden dann die betreffenden Felder der Abfrage im Formular dargestellt.
Diese Lösung ist allerdings unter dem Gesichtspunkt optimaler Performance nicht optimal.
Bild 9: Ausdruck als Steuerelementinhalt
Bild 10: Gruppierte Abfrage
Besser – weil schneller – ist es, derartige Berechnungen in ungebundenen Steuerelementen des Formulars vorzunehmen.
Dort wird als Wert für die Eigenschaft Steuerelementinhalt die entsprechende Funktion aufgerufen, der als Parameter der Inhalt des zu berechnenden Feldes übergeben wird (siehe Bild 9).
Gruppierungen
Bei der Verwendung von Gruppierungen in Abfragen ist es sinnvoll, nur die unbedingt notwendigen Felder zu gruppieren. Dadurch wird die Ausführungsgeschwindigkeit der Abfrage optimiert.
Das Beispiel in Bild 10 zeigt als Ergebnis die unterschiedlichen Positionen in den einzelnen Abteilungen an.
Verwendung von Tabellenerstellungsabfragen
Wenn Sie häufiger Daten aus komplexen Abfragen benötigen, die sich nur selten ändern, erstellen Sie einfach mit Hilfe einer Tabellenerstellungsabfrage eine entsprechende Tabelle, in der diese Daten zusammengefasst werden.
Der Zugriff auf diese Tabelle – zum Beispiel als Datenherkunft für ein Formular oder einen Bericht – erfolgt dann schneller als die erneute Ausführung der Abfrage.
Weitere Optimierungsmöglichkeiten bei Abfragen
Es gibt noch einige weitere Möglichkeiten, die Performance durch die Anpassung von Abfragen zu verbessern.
Verknüpfte Felder in Abfragen
Verknüpfte Felder in Abfragen sollten den gleichen oder einen kompatiblen Datentyp verwenden. Eine häufig verwendete und sinnvolle Kombination sind beispielsweise die Feldgrößen AutoWert und Long Integer.
Einschränkende Kriterien
Einschränkende Kriterien, wie beispielsweise der folgende Ausdruck, sollten nicht in berechneten und nicht indizierten Feldern verwendet werden:
<=2000
Inklusionsverknüpfungen
Vermeiden Sie nach Möglichkeit Inklusionsverknüpfungen oder Outer Joins, da Access damit nicht so effektiv arbeiten kann.
Bild 11: Kreuztabellenabfrage in der Entwurfsansicht
Bild 12: Sortierte Abfrage als Datenherkunft für ein Formular
Optimale Verwendung von Operatoren
Für den Zugriff auf indizierte Felder in einer Tabelle ist in einer Abfrage die Verwendung folgender Operatoren optimal:
Vermeiden Sie die gleichen Operatoren nach Möglichkeit bei nicht indizierten Tabellenfeldern.
Verwendung von Aggregatfunktionen
Verwenden Sie möglichst keine Aggregatfunktionen wie DLookup oder DSum, um auf Daten in Tabellen zuzugreifen, die nicht Bestandteil einer Abfrage sind.
Falls der Zugriff auf Daten solcher Tabellen notwendig sein sollte, fügen Sie diese Tabellen nach Möglichkeit zur Abfrage hinzu oder erstellen Sie eine Unterabfrage.
Kreuztabellenabfragen
Verwenden Sie bei Kreuztabellenabfragen nach Möglichkeit fixierte Spaltenüberschriften, um deren Ausführung zu beschleunigen (siehe Bild 11).
Formulare und Steuerelemente bieten ebenfalls eine Menge Möglichkeiten zur Leistungssteigerung.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo