Home > Artikel > Ausgabe 2/2017 > Indizierung in der Praxis

Indizierung in der Praxis

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

In jedem Lehrbuch zur Datenbankentwicklung erfahren Sie, wie wichtig die Indizierung von Tabellenfeldern ist. Das betrifft jene, die in Vergleichs- und Filterabfragen eingeschlossen werden sollen und außerdem die Felder, welche mehrere Tabellen über Indexfelder über Schlüssel miteinander verknüpfen. Ziel sind möglichst performante Abfragen der Datenbank. Wir untersuchen hier, wie groß der Performancezuwachs in der Praxis tatsächlich ist.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1702_Indizes.zip.

Wozu Indizierung?

In einer relationalen Datenbank gilt die Indizierung von mindestens den Verknüpfungsfeldern der Tabellen als das A und O. Sehen Sie sich dazu etwa das Datenmodell von Kunden und deren Bestellungen in Bild 1 an.

Die vier Tabellen der Kundendatenbank sind alle miteinander über Schlüsselfelder verknüpft

Bild 1: Die vier Tabellen der Kundendatenbank sind alle miteinander über Schlüsselfelder verknüpft

Zu jedem Kunden, identifiziert durch dessen ID, kann es mehrere Bestellungen geben. Das Feld KundeID der Tabelle bezieht sich auf die ID des Kunden. Damit ist die Verbindung hergestellt. Ähnlich verhält es sich mit den Tabellen zu Bestelldetails und Artikeln. Hier stellt jeweils die ID den Primärschlüssel dar, auf den sich die Datensätze einer Detailtabelle über einen Schlüssel xxxID verweisen. Die Bestelldetails machen das gleich zweimal.

Die Indizierung der beteiligten Schlüsselfelder ist immer dann zwingend, wenn Sie eine automatische Lösch- und Aktualisierungsweitergabe erreichen möchten. Access erlaubt die Einstellung dieser Optionen für eine Beziehung nur dann, wenn bereits Indizes auf die Felder gesetzt wurden. Das Löschen eines Kunden zieht dann das Löschen auch aller seiner Bestellungen samt Bestelldetails nach sich.

Doch oft ist Lösch- oder Aktualisierungsweitergabe gar nicht erforderlich oder erwünscht. Auch dann wird aber zu einer Indizierung geraten, weil dies der Performance zugutekommt. Sollen etwa die Bestellungen eines Kunden mit der ID 79 gefunden werden, so muss die Datenbank-Engine alle Datensätze der Tabelle tblBestellungen durchlaufen und die Werte von KundeID mit dieser Zahl vergleichen.

Bei Millionen von Bestelldatensätzen dauert dies recht lange. Ist das Feld KundeID aber indiziert, so zieht Access dessen Index zurate. Dieser erlaubt eine viel schnellere Suche nach dem Wert 79.

Soll gar zu einem Kunden ermittelt werden, welche Artikel er bereits bestellte, so ist die Tabelle tblBestelldetails und deren Feld ArtikelID zu durchsuchen. Da dies nur über den Umweg der Zwischentabelle tblBestellungen geschehen kann, sind nun schon zwei Tabellen verschachtelt komplett zu durchsuchen. Das potenziert die Suchdauer.

Der zweite Anwendungsfall für einen Index ist die unmittelbare Suche nach einem Feldinhalt. Möchten Sie etwa einen Kunden über dessen Namen finden, so muss Access wieder alle Datensätze scannen, um den oder die gewünschten zu erkennen. Sind die Namensfelder mit Indizes versehen, so verwendet die Engine diese zur Suche, was abermals schneller abläuft.

Soweit ist dies alles wahrscheinlich kein Fremdland für Sie!

Was ist ein Index?

Nehmen wir an, Sie hätten ein E-Book, wie das vorliegende, in dem Sie ein bestimmtes Thema finden möchten. Sie könnten nun eine Volltextsuche anstoßen, um nach einem Begriff zu suchen, und das dauert einige Zeit. Einfacher ist es da, sich eines Index im hinteren Teil des Buchs zu bedienen. Dort stehen zu jedem Begriff mehrere Seitenzahlen, über die Sie wahrscheinlich schneller zum Thema gelangen.

Ähnlich verhält es sich mit den Indizes einer Datenbank. Mache System weisen tatsächlich auch physisch einen Index auf. Zu einer DBase-Datenbank finden Sie etwa in der Regel neben der eigentlichen Datenbank (dbf) auch eine Datei mit der Endung idx, die lediglich die Indizes zu den Tabellen enthält. Intern speichert auch die Access Database Engine die Indizes in gesonderten Speicherbereichen, nur dass dies in der Oberfläche von Access nicht offenkundig ist. Indizieren Sie ein Tabellenfeld, so zeigt sich dies ja lediglich in der Entwurfsansicht am Eigenschaftenfeld Indiziert oder im Indizes-Dialog (Ribbon Entwurf | Indizes), wie in Bild 2. Intern hat Access aber Indextabellen angelegt.

Der Indizes-Dialog zur Tabelle tblKunden im Entwurf

Bild 2: Der Indizes-Dialog zur Tabelle tblKunden im Entwurf

Um zur Analogie eines Buches zurückzukehren: Suchen Sie etwa nach Löschweitergabe, so suchen Sie erstens im Inhaltsverzeichnis nach der ersten Seite des Index. Dort finden Sie die Begriffe alphanumerisch sortiert vor. Sie blättern zur Seite mit dem L als Überschrift und finden in der Liste den Begriff Löschweitergabe mit drei angefügten Seitenzahlen. Sie inspizieren diese Seiten nacheinander, um die gewünschten Informationen zu erhalten. So ähnlich arbeitet auch ein Index unter Access.

Allerdings sind diese internen Indextabellen wesentlich komplexer und hochoptimiert, damit sich die Zahl der Sprünge zu Einträgen und Untereinträgen so gering hält, wie möglich. Die hier verwendeten Algorithmen sind eine Wissenschaft für sich und tragen Namen, wie B-Tree. Ein mögliches vereinfachtes Modell bei aufsteigender Sortierung für einen Index:

K ->143

L -> 188

M -> 296

188 -> n -> 2354

188 -> o -> 2389

2389 -> d -> 7843

2389 -> e -> 7911

An der Speicherstelle 7911 sind wir immerhin schon bei Loe angekommen, den ersten drei Buchstaben von Löschweitergabe. Das Spiel setzt sich auf ähnliche Weise fort, bis der Begriff vollständig ermittelt ist. Dort stehen dann ein oder mehrere Zeiger auf den Speicherplatz der Datensätze innerhalb der eigentlichen Tabelle.

Für den Begriff braucht es deshalb vielleicht nur 15 Sprünge zwischen Speicherstellen, bis er im Index ermittelt ist. Dem entgegen stünde ein Durchlaufen aller Tausender Datensätze einer Tabelle, wenn auf einen Index für das Feld verzichtet würde.

Es ist also sehr plausibel, dass ein Index die Performance gewaltig erhöht. Zumindest gilt das für das Lesen von Tabellen. Bei Anfügen oder Ändern eines Datensatzes hingegen kommt es zu einem Nachteil: Nicht nur die Daten in der Tabelle selbst müssen geändert werden, sondern auch der Index! Sein ganzer Aufbau muss dem neuen Tabelleninhalt angepasst werden, damit die Suche effizient abläuft.

Imgrunde müssten dazu sämtliche Speicherstellenverweise neu angeordnet werden, damit die Buchstaben oder Zahlen wieder korrekt sortiert in ihm vorliegen. In der Realität passiert das so aber nicht, sondern nur in Teilen, weil es die Performance stark senken würde. Zu einer kompletten Neuordnung der Indizes kommt es erst dann, wenn Sie die Funktion Komprimieren und Reparieren für die Datenbank anstoßen!

Ein Test mit umfangreicher Tabelle

Um zu überprüfen, wie sich die Indizierung in der Praxis auswirkt, verwenden wir erst einmal eine Tabelle mit sehr vielen Datensätzen. Die Wahl fiel auf den OpenThesaurus, eine Tabelle, die synonyme Begriffe enthält. Die Tabelle der Beispieldatenbank kommt aus dem gleichnamigen Open Source-Projekt und enthält etwa 125.000 Datensätze. Bild 3 zeigt einen Ausschnitt der Tabelle.

Die Tabelle OpenThesaurus in der Datenblattansicht

Bild 3: Die Tabelle OpenThesaurus in der Datenblattansicht

In Bild 4 finden Sie den Entwurf der Tabelle. Die Begriffe stehen in der Spalte Ausdruck. Jene Begriffe, die synonym sein sollen, haben alle die gleiche IDGroup. Man muss also nur nach einem Begriff suchen, die IDGroup desselben auslesen, um über diese und eine Abfrage alle anderen Begriffe zu erhalten. Jeder Datensatz enthält darüber hinaus eine eindeutige ID.

Die Tabelle OpenThesaurus in der Entwurfsansicht

Bild 4: Die Tabelle OpenThesaurus in der Entwurfsansicht

Es ist naheliegend, dass eine Indizierung der Felder Ausdruck (für die Suche nach einem Begriff) und IDGroup (für das Auffinden synonymer Begriffe) hier Pflicht ist. Wir haben dies für die Tabelle so gemacht und jeweils in die Eigenschaft Indiziert im Entwurf die Auswahl Ja (Duplikate möglich) eingestellt.

Um die Performance mit einer Tabelle ohne Indizes zu vergleichen, enthält die Beispieldatenbank zusätzlich die gleiche Tabelle ohne Indizierung, die den Namen OpenThesaurus2 trägt. Und eine weitere nennt sich OpenThesaurus3. Sie leitet sich von der zweiten ab, hat aber für das Feld Ausdruck keine Unicode-Kompression eingestellt. Access stellt diese von Haus aus auf Ja ein. Alle Zeichen von Textfeldern werden unter Access ja als Unicode abgespeichert, um auch Sonderzeichen und ausländische Schriftzeichen abbilden zu können.

So ein Unicode-Zeichen nimmt zwei Bytes ein, was den Speicherbedarf gegenüber ANSI (ein Byte) verdoppelt. Aus diesem Grund wurde die Kompression dieser Doppelzeichen eingeführt, die den physischen Speicherbedarf reduziert. Man sollte nun annehmen, dass die Komprimierung und Dekomprimierung der Texte zusätzliche Rechenzeit benötigt und die Performance senken könnte. Auch das überprüfen wir im Folgenden. Eine Testprozedur im Modul mdlTest hat nun prinzipiell den Code aus Listing 1. Die Zeit wird über die Variable T gemessen. Sie kommt aus der VBA-Funktion Timer, die die aktuelle Zeit in Sekunden als Single-Wert mit drei Nachkommastellen zurückgibt. Das Recordset wird auf die Tabelle OpenThesaurus geöffnet und als Filter bestimmt, dass nur die Datensätze mit dem Ausdruck Akronym berücksichtigt werden sollen. In einer Schleife wird das Ergebnis durchlaufen und jeweils in der Variablen V abgespeichert. Nach dem Schließen des Recordsets gibt die letzte Zeile der Prozedur die Differenz der aktuellen Zeit zu der eingangs in T gespeicherten im VBA-Direktfenster aus.

Sub PerformanceRead()

     Dim T As Single

     InitEngine

     T = VBA.Timer

     Set rs = CurrentDb.OpenRecordset("SELECT * From OpenThesaurus WHERE Ausdruck='Akronym'", dbOpenDynaset)

     Do While Not rs.EOF

         V = rs!Ausdruck.Value

         rs.MoveNext

     Loop

     rs.Close

     Debug.Print VBA.Timer - T

End Sub

Listing 1: Performance-Test für den OpenThesaurus

Damit die Bedingungen für jeden Aufruf der Prozedur annähernd gleich sind, ist oben noch der Aufruf einer Hilfsroutine InitEngine eingeschoben (Listing 2). Was macht diese? Die erste Zeile setzt die Anweisung Idle für die Engine von Access ab, wobei als Parameter ein dbFreeLocks übergeben wird. Das bewirkt, dass eventuell noch ausstehende Datensatzsperrungen aufgehoben werden. Bedenken Sie, dass die Engine nicht nur Ihre Tabellen bearbeitet, sondern auch die Systemtabellen von Access! Im Hintergrund hat sie unter Umständen also noch etwas zu erledigen.

Sub InitEngine()

     DBEngine.Idle dbFreeLocks

     DBEngine.BeginTrans: DBEngine.CommitTrans dbForceOSFlush

     DBEngine.Idle dbRefreshCache

     DoEvents

End Sub

Listing 2: Initialisieren der Datenbank-Engine

Die zweite Zeile leitet eine sogenannte Nulltransaktion ein. Erst startet die Engine eine Datentransaktion mit BeginTrans. Dann beendet sie diese, ohne dazwischen irgendetwas getan zu haben, mit CommitTrans. Hier ist der Parameter dbForceOSFlush angegeben. Er weist die Engine an, alle noch ausstehenden Schreibvorgänge zu beenden und physisch in die Datenbankdatei zu schrieben. Sonst könnten solche Datenvorgänge möglicherweise nur im Cache der Engine stehen!

Die dritte Zeile löst wieder die Methode Idle aus, diesmal jedoch mit dem Parameter dbRefreshCache. Das füllt den Cache der Engine wieder mit temporären Daten auf, die etwa für die Systemtabellen benötigt werden. Abschließend bewirkt das DoEvents, dass alle Oberflächenaktionen, wie das Neuzeichnen von Access oder dem VBA-Editor, abgeschlossen werden. Damit ist jedes Mal ein gewisser Ausgangszustand hergestellt.

Wir kommen auf eine Ausführungszeit von 15,7 Millisekunden. Angesichts der 125.000 Datensätze ist das enorm schnell. Access musste ja innerhalb dieser einen Ausdruck finden. Aber nun wird es spannend: Wir ersetzen die Tabelle im Recordset durch die OpenThesaurus2, die ja keine Indizierung vorsieht. Hier spielt sich die Sache in 46,9 Millisekunden ab. Das ist zwar dreimal langsamer, aber immer noch ein akzeptabler Wert. Wir hätten eigentlich ganz andere Größenordnungen erwartet!

Hinweis: Alle Messungen hier wurden mehrfach durchgeführt. Die Angaben sind Mittelwerte, so dass sie als reproduzierbar gelten können.

Beim Test mit der OpenThesaurus3, also der Tabelle ohne Unicode-Kompression auf das Feld Ausdruck, kommen wir zu keinem abweichenden Ergebnis. Offenbar spielt die Berechnung zur Konvertierung von komprimierten Daten nach Unicode keine Rolle. Der Vorgang scheint so optimiert zu sein, dass er nicht mehr messbar ist.

Die Frage ist aber, ob die gleichen Gesetzmäßigkeiten gelten, wenn sich die Tabellen nicht lokal in der Datenbank befinden, sondern in einem verknüpften Backend, das gegebenenfalls sogar in einem Netzwerkverzeichnis steht, ein Umstand der bei vielen Datenbanken sogar die Regel darstellen dürfte?

Alle Testtabellen der Datenbank

In der Backend-Datenbank 1702_Indizes_BE.accdb befinden sich alle Tabellen, über die wir hier sprechen. Sie sollte sich im gleichen Pfad befinden, wie die Frontend-Datenbank 1702_Indizes.accdb. Die Backend-Tabellen sind alle in das Frontend verknüpft. Die Neuverknüpfung findet übrigens automatisch statt, wenn Sie das Frontend öffnen, weil das Intro-Formular der Beispieldatenbank die Prozedur RelinkTables im Modul mdlTest aufruft.

Alle Tabellen stellen sich nun im Navigationsbereich dar, wie in Bild 5. Lediglich Die Tabelle OpenThesaurus befindet sich im Frondend selbst. Alle anderen stehen verknüpft im Backend. Es hat sich herausgestellt, dass es performancetechnisch keinen Unterschied ausmacht, ob sich eine Tabelle im Frontend befindet, oder in einer Backend-Datei auf demselben System. Das ändert sich naturgemäß, wenn Sie über ein Netzwerk auf die Datei zugreifen. Das senkt die Performance der Lese- und Schreibvorgänge erheblich.

Alle Tabellen der Beispieldatenbank im Navigationsbereich

Bild 5: Alle Tabellen der Beispieldatenbank im Navigationsbereich

Wenn Sie alle Tests nachvollziehen möchten, so speichern Sie eine Kopie des Backends auf einem anderen Rechner im Netzwerk. Das kann etwa auch ein NAS sein. Die Neuverknüpfung mit diesen Tabellen müssen Sie dann manuell durchführen. In der Abbildung sind alle Tabellen, deren Namen hinten ein N tragen, solche aus dem Netzwerk-Backend. Sie sind nach dem Neuverknüpfen manuell abweichend benannt worden, um hier eine gewisse überschaubare Systematik zu erreichen. Auf alle Tabellen wird dann nacheinander ein Test-Set losgelassen, das in den Prozeduren PerformanceRead und PerformanceWrite steht.

Komplettes Test-Set

Listing 3 zeigt die Prozedur PerformanceRead für Lesevorgänge ausschnittsweise mit zwei Testroutinen. Sie übergeben ihr als Parameter einen Namen der sieben OpenThesaurus-Tabellen. Dieser wird dann in den Zeilen zum Öffnen der Recordsets verwendet. Als Öffnungsmodus für diese ist dbOpenDynaset in der Variablen eType hartkodiert. Sie können Diesen Typ wahlweise auch in dbOpenSnapshot ändern, wenn Sie vermuten, dass dies andere Ergebnisse zeitigen könnte. Das ist durchaus wahrscheinlich. Denn im Modus Snapshot werden die Datensätze der Theorie nach nicht sequenziell eingelesen, sondern auf einen Schlag. Da dieser Modus aber eher selten zur Anwendung kommt – etwa, um absolut sicherzustellen, dass die Daten schreibgeschützt sind – und sonst keinen Vorteil gegenüber dem Dynaset aufweist, haben wir auf Performance-Messungen verzichtet.

Sub PerformanceRead(sTable As String)

     Dim rs As dao.Recordset

     Dim V As Variant

     Dim eType As dao.RecordsetTypeEnum

         

     eType = dbOpenDynaset

     

     InitEngine

     T = VBA.Timer

     Set rs = CurrentDb.OpenRecordset("SELECT * From " & sTable _

         & " WHERE IDGroup=23629", eType)

     Do While Not rs.EOF

         V = rs!Ausdruck.Value

         rs.MoveNext

     Loop

     rs.Close

     Debug.Print "Long=", VBA.Timer - T

     InitEngine

     T = VBA.Timer

     Set rs = CurrentDb.OpenRecordset("SELECT * From " & sTable _

         & " WHERE Ausdruck='Akronym'", eType)

     Do While Not rs.EOF

         V = rs!Ausdruck.Value

         rs.MoveNext

     Loop

     rs.Close

     Debug.Print "Str=", VBA.Timer - T

     ....

End Sub

Listing 3: Ein Teil der Testprozedur PerformanceRead

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!