Warum noch ein Access-Buch?
Für wen ist das Buch?
Jetzt bestellen
+ direkter Download des eBooks!
Nur EUR 59,95!
Fehler gefunden?
Bitte melden!
Wünsche an das Buch?
Her damit!
Was denken die Leser über dieses Buch?
Lesen Sie aktuelle Rezensionen!
Kapitel des noch nicht veröffentlichten Buchs zum Downloaden, Probelesen und Kommentieren
Beispieldatenbanken
Zusätzliches Material

Das Buch im HTML-Format

Für unbestimmte Zeit bieten Addison-Wesley und André Minhorst den kompletten Inhalt des Buchs als Download an. Schauen Sie rein und informieren Sie sich über den Inhalt! Und wenn Ihnen das Buch nützlich erscheint und Sie glauben, dass Sie etwas gelernt haben oder durch das Gelesene sogar etwas Zeit und somit Geld bei Ihrer Arbeit einsparen konnten, können Sie sich ja beim Autor und beim Verlag revanchieren - beispielsweise durch den Kauf dieses Buchs.

Am schönsten wäre es natürlich, wenn Sie das Buch direkt hier bestellen - Sie erhalten das Buch dann direkt vom Verlag, und der Autor und Verlag haben dann noch mehr davon, als wenn Sie es anderswo kaufen.

Danke für Ihr Interesse!

14.2 Abfragen

14.2.1 Abfragen und die ACE-Engine

Für die Optimierung von Abfragen kann die Kenntnis der bei der Verarbeitung von Abfragen durch die ACE- Engine verwendeten Technik hilfreich sein.

Abfragen entstehen auf unterschiedliche Weise:

  • durch Verwendung der Abfrage-Entwurfsansicht
  • durch Eingabe eines SQL-Ausdrucks als Datensatzquelle von Formularen und Berichten oder als Datensatzherkunft von Kombinations- und Listenfeldern
  • durch Zusammenstellung eines SQL-Ausdrucks in Form eines Strings und anschließende Verwendung in einer VBA-Prozedur
  • Wie auch immer die Abfrage entstanden ist, verwendet die ACE-Engine letzten Endes den dahinter stehenden SQL-Ausdruck.

    Kompilieren einer Abfrage

    Die Abfrage wird vor ihrer Ausführung zunächst kompiliert. Das heißt, dass die ACE-Engine die Basisinformationen aus der Abfrage ermittelt:

  • Basistabellen: Tabellen, die an der Abfrage beteiligt sind
  • Tabellenfelder aus den Ausgabefeldern der Abfrage
  • Vorhandene Indizes in den beteiligten Tabellen
  • Kriterien
  • Verknüpfungsfelder zwischen zwei Tabellen
  • Sortierfelder
  • Optimierung der Abfrage

    Anschließend optimiert die ACE-Engine die Abfrage auf Basis dieser Informationen. Dabei erstellt sie unterschiedliche Ausführungspläne und ermittelt daraus die schnellste Variante.

    Den größten Einfluss auf die Wahl des Ausführungsplans hat dabei die Analyse der Basistabellen und der Verknüpfungen zwischen diesen Tabellen.

    Um den Zugriff auf die Basistabellen zu optimieren, verwendet die ACE-Engine drei Tabellen-Zugriffsstrategien: Table Scan, Index Range oder Rushmore Restriction. Welche dieser Techniken die ACE-Engine benutzt, hängt von der Größe der jeweiligen Tabelle, der Anzahl der enthaltenen Indizes und der Art und Menge der Kriteriumsfelder ab.

    Beim Table Scan durchsucht die ACE-Engine jeden einzelnen Datensatz der Tabelle. Diese Methode wird angewendet, wenn Kriterienfelder nicht indiziert sind oder wenn die Kriterien die Ergebnismenge nur geringfügig einschränken.

    Die Index Range-Methode wird verwendet, wenn nur ein indiziertes Feld mit einem Kriterium versehen ist. Die Suche nach den gewünschten Datensätzen erfolgt über den Index dieses Kriterienfeldes.

    Die Rushmore Restriction kommt zum Zuge, wenn mehr als ein Kriterienfeld indiziert ist. Weitere Informationen finden Sie weiter unten in diesem Kapitel, Abschnitt »Abfragen mit Rushmore optimieren«.

    Auch die Beziehungen zwischen den Tabellen spielen eine Rolle bei der Optimierung von Abfragen. Für die Auswertung der Beziehungen werden fünf verschiedene Strategien verwendet, die sich Nested Iteration Join, Index Join, Lookup Join, Merge Join und Index-Merge Join nennen.

    Welche Strategie zum Zuge kommt, hängt von der Beschaffenheit der beteiligten Tabellen, Felder und Indizes, vom Vorkommen von Null-Werten und von anderen Faktoren ab.

    Weitere Informationen hierzu finden Sie in: Access 2002 Desktop Developer’s Handbook, Litwin, Getz, Gunderloy, Sybex

    Abfragen mit Rushmore optimieren

    more ist eine Technik zur Optimierung von Abfragen, die auf das Vorhandensein von mehreren Restriktionen auf indizierten Feldern abzielt. Die Rushmore-Optimierung funktioniert mit den folgenden Operationen:

    x Intersection: Diese Operation wird auf Abfragekriterien folgenden Aussehens angewendet, wobei alle Kriterienfelder indiziert sein müssen:

    Spalte1 = "Ausdruck1" AND Spalte2 = "Ausdruck2"

    Der Clou ist, dass die ACE-Engine bei der Rushmore-Optimierung nicht mit den eigentlichen Vergleichsoperatoren, sondern mit ihrem Index arbeitet. Dabei sucht die ACE-Engine zunächst nach allen Datensätzen, die die erste Bedingung erfüllen, und anschließend nach allen Datensätzen, die die zweite Bedingung erfüllen, und ermittelt schließlich die Schnittmenge daraus.

    x Union: Die zweite Operation zielt auf durch OR verknüpfte Kriterien nach folgendem Schema ab:

    Spalte1 = "Ausdruck1" OR Spalte2 = "Ausdruck2"

    Die Abfrage ermittelt hier ebenfalls alle Datensätze, die dem ersten, und alle Datensätze, die dem zweiten Kriterium entsprechen. Allerdings bildet sie hier nicht die Schnittmenge, sondern die Vereinigungsmenge der beiden Ergebnisse.

    more optimiert obige Abfragen deshalb so gut, weil es »Bitmaps« aus den Index-Werten erstellt und diese indiziert.

    Kombiniere, kombiniere ...

    Um die Abfrage wirklich optimal ausführen zu können, ermittelt die ACE-Engine den ungefähren Aufwand für die in Frage kommenden Tabellen-Zugriffsstrategien in Kombination mit den möglichen Verknüpfungsstrategien und wendet schließlich die am günstigsten erscheinende Variante an.

    Ausführung der Abfrage

    Wird die Abfrage ausgeführt, tritt noch ein sehr wichtiger Faktor auf: der Recordset-Typ. Diese Eigenschaft, die Sie beispielsweise in gespeicherten Abfragen im Eigenschaftsfenster einstellen können (siehe Abbildung 14.4), entscheidet maßgeblich über die Geschwindigkeit der Abfrage.

    Eine Abfrage mit dem Recordset-Typ Dynaset gibt beispielsweise ein Ergebnis zurück, das lediglich die eindeutigen Schlüsselfelder - soweit vorhanden - der in der Abfrage enthaltenen Tabellen enthält. Es werden nur zusätzliche Daten für die Datensätze in den Speicher geladen, die beispielsweise für die Anzeige im Formular benötigt werden.

    Abfragen des Recordset-Typs Snapshot laden die kompletten Daten - also alle Datensätze mit allen angegebenen Feldern - direkt in den Speicher. Das dauert natürlich erheblich länger - erst recht, wenn nicht alle Daten in den Speicher passen und ein Teil des Abfrageergebnisses auf der Festplatte zwischengespeichert werden muss.

    Abbildung 14.4: Einstellen des Recordset-Typs einer Abfrage

    Abfragestrategien unter der Lupe

    Access bietet die Möglichkeit, die bei einer Abfrage verwendeten Informationen (zumindest teilweise) auszuwerten. Mit der Anpassung eines Registry-Eintrags können Sie dafür sorgen, dass Access Informationen über die Durchführung von Abfragen in einer Textdatei ausgibt.

    Den entsprechenden Schlüssel müssen Sie zunächst in der Registry unter HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines hinzufügen. Legen Sie zunächst mit dem Kontextmenüeintrag Neu|Schlüssel einen neuen Schlüssel für den Schlüssel Engines an und nennen Sie diesen Debug. Wählen Sie dann auch dem Kontextmenü des neuen Schlüssels den Eintrag Neu|Zeichenfolge aus und nennen Sie den neuen Eintrag Jetshowplan. Stellen Sie dort den Wert auf On ein. Anschießend schreibt die ACE-Engine Informationen über die Ausführungspläne der Abfragen in eine Datei namens showplan.out. Diese Funktion ist nicht offiziell dokumentiert. Der Ausgabeort der Datei showplan.out liegt meist im aktuellen Datenbankverzeichnis, das Sie in den Access-Optionen unter Häufig verwendet|Standarddatenbankordner finden.

    Die Funktion lässt sich leider nicht auf eine einzelne Datenbank beschränken (es sei denn, Sie aktivieren und deaktivieren diesen Schlüssel per VBA von der entsprechenden Datenbank aus). Da diese Option demnach für alle Datenbanken gilt, wird die Datei Showplan.out schnell sehr groß und macht Access langsam; behalten Sie diese daher im Auge oder schalten Sie die Funktion nur bei Bedarf ein. Um die Funktion zu deaktivieren, entfernen Sie den Schlüssel entweder komplett oder stellen den Wert auf Off ein (das wirkt sich übrigens erst beim nächsten Start von Access aus).

    Das folgende Listing zeigt den Inhalt der Datei showplan.out für die Abfrage Bestellungszusammenfassung der Nordwind-Datenbank von Access 2007. Der erste Teil enthält die für die Auswahl der Strategie für den Zugriff auf die Tabellen ermittelten Informationen, der zweite Teil die Reihenfolge bei der Abarbeitung der Verknüpfungen.

    --- Bestellungszusammenfassung ---

    - Inputs to Query -
    Table 'Status der Bestellung'
        Using index 'PrimaryKey'
        Having Indexes:
        PrimaryKey 4 entries, 1 page, 4 values
         which has 1 column, fixed, unique, primary-key, no-nulls
    Table 'Bestellungen'
    Table 'Bestelldetails'
    Table 'Status für Bestelldetails'
        Using index 'PrimaryKey'
        Having Indexes:
        PrimaryKey 6 entries, 1 page, 6 values
         which has 1 column, fixed, unique, primary-key, no-nulls
    - End inputs to Query -

    01) Outer Join table 'Bestelldetails' to table 'Status für Bestelldetails'
         using index 'Status für Bestelldetails!PrimaryKey'
         join expression "[Bestelldetails].[Status-Nr]=[Status für Bestelldetails].[Status-Nr]"
    02) Group result of '01)'
    03) Outer Join table 'Bestellungen' to result of '02)'
         using temporary index
         join expression "Bestellungen.[Bestell-Nr]=[Gesamtpreis der Bestellung].BestellNr"
    04) Outer Join result of '03)' to table 'Status der Bestellung'
         using index 'Status der Bestellung!PrimaryKey'
         join expression "Bestellungen.[Status-Nr]=[Status der Bestellung].[Status-Nr]"
    05) Sort result of '04)'

    --- temp query ---
    - Inputs to Query -
    Recordset
    - End inputs to Query -

    01) Scan recordset
    02) Restrict rows of table 01)
         using index 'UNKNOWN'
         for expression "[_VersionHistory_F5F8918F-0A3F-4DA9-AE71-184EE5012880]=FK"
    03) Sort result of '02)'

    --- Bestellungszusammenfassung ---
    - Inputs to Query -
    Table 'Status der Bestellung'
        Using index 'PrimaryKey'
        Having Indexes:
        PrimaryKey 4 entries, 1 page, 4 values
         which has 1 column, fixed, unique, primary-key, no-nulls
    Table 'Bestellungen'
    Table 'Bestelldetails'
    Table 'Status für Bestelldetails'
        Using index 'PrimaryKey'
        Having Indexes:
        PrimaryKey 6 entries, 1 page, 6 values
         which has 1 column, fixed, unique, primary-key, no-nulls
    - End inputs to Query -

    01) Outer Join table 'Bestelldetails' to table 'Status für Bestelldetails'
         using index 'Status für Bestelldetails!PrimaryKey'
         join expression "[Bestelldetails].[Status-Nr]=[Status für Bestelldetails].[Status-Nr]"
    02) Group result of '01)'
    03) Outer Join table 'Bestellungen' to result of '02)'
         using temporary index
         join expression "Bestellungen.[Bestell-Nr]=[Gesamtpreis der Bestellung].BestellNr"
    04) Outer Join result of '03)' to table 'Status der Bestellung'
         using index 'Status der Bestellung!PrimaryKey'
         join expression "Bestellungen.[Status-Nr]=[Status der Bestellung].[Status-Nr]"
    05) Sort result of '04)'

    Listing 14.2: Inhalt der Datei showplan.out nach dem Durchführen einer komplexen Abfrage

    Wenn Sie beim Tuning Ihrer Abfragen ins Detail gehen möchten, kann die Ausgabe der ACE-Engine durchaus weiterhelfen. Wenn Sie der Abfrage Bestellungszusammenfassung beispielsweise ein Kriterium für das Feld Versanddatum hinzufügen und dieses Feld nicht indiziert ist, erhalten Sie im Ausführungsplan folgende Zeile als ersten Schritt:

    01) Restrict rows of table Bestellungen
         by scanning
         testing expression "Bestellungen.Versanddatum>#1/1/2006#"

    Das Schlüsselwort scanning bedeutet in diesem Zusammenhang, dass ein Table Scan durchgeführt wird - ein Index für dieses Feld ist nicht vorhanden.

    Besser wäre es, das Feld Versanddatum der zugrunde liegenden Tabelle Bestellungen mit einem Index zu versehen - auf diese Weise könnte die Rushmore-Technik verwendet werden. Die entsprechende Zeile in der Datei showplan.out sieht dann folgendermaßen aus:

    01) Restrict rows of table Bestellungen
         using rushmore
         for expression "Bestellungen.Versanddatum>#1/1/2006#"

    Nächster Abschnitt:

    14.2.2 Datenbank mit kompilierten Abfragen ausliefern

    © 2006-2008 André Minhorst Alle Rechte vorbehalten.