Microsoft Access - Abfrage-Performance-Tuning

Performance-Tuning von Microsoft Access-Abfragen ist oft wie eine schwarze Kunst. Es handelt sich dabei entweder um fundierte Vermutungen, wildes Herumprobieren, oder um die Verwendung obskurer Analysetools, wie z. B. das JetShowPlan-Flag zur Ausgabe des Abfrageausführungsplans der Datenbank-Engine.

Es gibt jedoch ein paar grundlegende, universelle Regeln zur Performanceoptimierung von Datenbankabfragen, die allgemein für (fast) jede Abfrage gelten und auf alle relationalen Datenbankmanagementsysteme (DBMS) anwendbar sind, die die Abfragesprache SQL verwenden.

Ich habe ein Video aufgenommen, das alle hier besprochenen Performanceoptimierung demonstriert. Du kannst also wählen, ob du lieber das Video ansiehst oder den Text hier liest.

Testumgebung

Um Nebeneffekte wie Netzwerklatenz und Bandbreitenschwankungen, gleichzeitige Serverlast und Datenbank- und Betriebssystem-Caching auszuschließen, verwende ich eine Kopie der Nordwind-Beispieldatenbank auf meinem lokalen Computer. Damit sich die Optimierungen tatsächlich messbar auswirken, habe ich die Datenbank mit einer beträchtlichen Anzahl von Datensätzen gefüllt (100K Kunden, 1,5M Aufträge). Trotzdem sind in diesem Szenario die Unterschiede zwischen den Varianten der Abfragen nur Sekunden oder sogar Millisekunden. Bedenke, dass diese Unterschiede in einer realen Produktionsumgebung mit mehreren Benutzern, die alle gleichzeitig dieselbe Datenbank und Infrastruktur nutzen, exponentiell größer sein werden.

Alle Kriterienspalten in den folgenden Beispielen sind indiziert. Dies ist generell für die meisten Spalten empfohlen, die du häufig in Abfragekriterien verwendest. Wenn Sie die folgenden Empfehlungen auf nicht indizierte Spalten anwenden, haben die Optimierungen viel weniger oder gar keine Auswirkungen.

Textvergleich mit LIKE

Bei der Suche nach teilweisen Übereinstimmungen von Text verwenden wir in SQL den LIKE-Operator. Oft werden Abfragen mit LIKE so geschrieben, dass sie jeden Teil der Daten in der gesuchten Spalte finden. Dadurch wird aber verhindert, dass die Datenbank-Engine einen Index verwendet, um die Übereinstimmungen zu finden.

Wenn es die Anforderungen zulassen, konvertiere die Abfrage so, dass sie beginnend vom Anfang der Daten in dem Feld sucht. Dann wird der Index verwendet, und die Abfrage wird deutlich schneller.

Es gibt keinen messbaren Leistungsunterschied mehr zu einer Abfrage, die auf vollständige Gleichheit prüft.

Um die Größenordnung der Unterschiede in einem realen Szenario zu veranschaulichen, habe ich die Ausführungszeiten der obigen Abfragen mit einer Netzwerk-Backend-Datenbank (gleiches Datenvolumen) und ohne Caching verglichen.

LIKE '*schmidt*' - Ausführungszeit: 30 Sekunden

LIKE 'schmidt*' - Ausführungszeit: 1,6 Sekunden

Nachfolgende Ausführungen derselben Abfrage verhalten sich dann recht ähnlich wie die Beispiele mit einer lokalen Datenbank, da die Daten zwischengespeichert sind und nicht erneut über das Netzwerk von der Festplatte gelesen werden müssen.

Ungleichheitsoperator vermeiden

Die Verwendung des Ungleichheitsoperators oder die Umkehrung einer Gleichheitsprüfung mit dem Schlüsselwort NOT verhindert ebenfalls, dass die Datenbank-Engine beim Suchen der übereinstimmenden Werte einen Index verwendet.

Wenn du eine solche Abfrage so umschreiben kannst, dass sie entweder den Operator „kleiner/größer als“ oder eine Liste der gesuchten Werte verwendet, ermöglicht sie die Verwendung eines Indexes und wird viel schneller ausgeführt. In meinen Beispieldaten liefern die beiden folgenden Abfragen genau die gleichen Ergebnisse.

Natürlich ist die Verwendung von hartcodierten Werten in einer Abfrage oft keine ideale Lösung.

Mehrere Tabellen

Aufbauend auf den vorangegangenen Beispielen, ändern wir die hartcodierten Werte für die Shipper ID in eine Abfrage, die ein boolesches Attribut in der Tabelle "Shippers" verwendet, um indirekt die Id(s) der relevanten Versender-Datensätze zu ermitteln.

Hier ist die suboptimale Variante unter Verwendung des Ungleichheitsvergleichs, hier geschrieben als NOT IN.

Wie oben wird der Ansatz, die Kriterien zu invertieren, um explizit die gesuchten Datensätze abzufragen, anstatt die nicht gesuchten auszuschließen, die Abfrageperformance deutlich verbessern.

Die Abfrage kann auch mit einer EXISTS-Klausel geschrieben werden. Allerdings betreten wir hier das Terrain der schwarzen Magie, da es keine harte und klare Regel gibt, wann man welche verwenden sollte.

SELECT * FROM Orders o WHERE EXISTS ( SELECT 'x' FROM Shippers s WHERE o.[Shipper Id] = s.Id AND s.GeneralParcelService = -1) -- Execution Time: 2.39 seconds

Viel klarer ist die Situation, wenn man die Sub-Select-Anweisungen mit einer Abfrage vergleicht, die einen JOIN verwendet, um die gleichen Ergebnisse zu erhalten. In fast allen Fällen von Access-Abfragen wird ein JOIN viel schneller ausgeführt als eine vergleichbare Abfrage mit einem Sub-Select.

Benutzerdefinierte VBA-Funktionen

Es ist ein weit verbreiteter Mythos, dass die Verwendung von benutzerdefinierten VBA-Funktionen in Abfragen immer die Abfrage langsam macht. - Das ist zwar nicht ganz falsch, aber viel zu allgemein formuliert. Wenn sie richtig eingesetzt werden, schaden Funktionen der Performance deiner Abfrage nicht.

Hier ist eine Abfrage, die eine VBA-Funktion in der Where-Klausel der Abfrage verwendet.

SELECT * FROM Customers WHERE FormattedAddress([Country/Region],[State/Province], [ZIP/Postal Code], [City], [Address]) LIKE '*34131 Kassel*' -- Execution Time: 3.42 seconds

Die Funktion FormattedAddress führt lediglich eine einfache String-Verkettung durch, um eine Adresse für den Druck eines Adressetiketts zu formatieren, angepasst an die Konventionen des Ziellandes.

Public Function FormattedAddress(ByVal Country As Variant, _ ByVal State As Variant, _ ByVal PostalCode As Variant, _ ByVal City As Variant, _ ByVal StreetAddress As Variant) _ As String Dim retVal As String Select Case Country Case "United States" retVal = Nz(StreetAddress, "") & vbCrLf & Nz(City, "") & ", " & Nz(State, "") & " " & Nz(PostalCode, "") & vbCrLf & UCase(Country) Case "Germany" retVal = Nz(StreetAddress, "") & vbCrLf & Nz(PostalCode, "") & " " & Nz(City, "") & vbCrLf & UCase(Country) Case Else retVal = Nz(StreetAddress, "") & vbCrLf & Nz(PostalCode, "") & " " & Nz(City, "") & " " & Nz(State, "") & " " & vbCrLf & UCase(Nz(Country, "")) End Select FormattedAddress = retVal End Function

Wenn du eine beliebige VBA-Funktion verwendest, um die Daten in der Tabelle zu verarbeiten und dann die Ergebnisse mit den Kriterien zu vergleichen, können wieder keinen Indizes verwenden und benötigen außerdem viel Rechenleistung, um die Daten aus jeder Zeile der Tabelle zu verarbeiten.

Die Verwendung der einzelnen Kriterienwerte auf den reinen Tabellenspalten führt zu einer viel besseren Abfrage-Performance.

SELECT FormattedAddress([Country/Region],[State/Province], [ZIP/Postal Code], [City], [Address]) FROM Customers WHERE [Zip/Postal Code] = '34131' AND City = 'Kassel' -- Execution Time: 0.84 seconds

Die Verwendung des Funktionsaufrufs als Ausgabespalte hat keinen nennenswerten Einfluss auf die Performance, da er nur für die wenigen Zeilen ausgeführt wird, die in der Ergebnismenge enthalten sind.

Eingebaute Funktionen

Funktionen, die in die Access/VBA-Umgebung eingebaut sind, werden schneller ausgeführt, da sie in C++ geschrieben sind. Es gibt auch einige Funktionen, die direkt in die ACE/JET-Datenbank-Engine eingebaut sind, was ihre Ausführung in einer Abfrage weiter optimiert.

Dies ändert jedoch nichts an den grundlegenden Problemen bei der Verwendung von Funktionen in Abfragen. Sobald Sie eine Funktion auf Tabellendaten anwenden, müssen alle Daten in der Tabelle verarbeitet werden, um die Bedingung auszuwerten. Das ist eine so große Bremse für die Performance, dass die etwas effizientere Funktionsausführung kaum auffällt.

Wenn die Abfrage mit dem Start- und Enddatum des Datumsbereichs geschrieben wird, wird sie viel schneller ausgeführt.

Wenn du den Datumsbereich dynamisch generieren willst, z.B. um immer die Bestellung des aktuellen Monats anzuzeigen, kannst du Funktionen in der Where-Bedingung verwenden. Allerdings darfst du diese nur auf die Werte der Abfragekriterien anwenden, nicht auf die Daten in der Tabelle.

SELECT * FROM Orders WHERE [Order Date] >= DateSerial(Year(Date()), Month(Date()), 1) AND [Order Date] < DateSerial(Year(DateAdd('m',1,Date())), Month(DateAdd('m',1,Date())), 1) -- Execution Time: 0.95 seconds

Optimieren von Funktionen in Abfragekriterien

Manchmal lässt es sich nicht vermeiden, eine Funktion auf Tabellendaten zu verwenden. Ein Beispiel ist eine Abfrage unter Beachtung von Groß- bzw Kleinschreibung in den Kriterien. Access (die ACE/JET-Engine) vergleicht Textdaten grundsätzlich ohne Berücksichtigung der Groß-/Kleinschreibung. Wenn du die genaue Groß-/Kleinschreibung des Kriteriums beachten willst, musst du die StrComp Funktion verwenden.

Wir können das Problem entschärfen, indem wir die Kriterien für Berücksichtigung der Groß-/Kleinschreibung mit denselben Kriterien kombinieren, die nur den Standardvergleich verwenden. Dies führt dazu, dass die Datenbank-Engine zunächst eine optimierte Abfrage auf die Basiskriterien ausführt. Erst in einem zweiten Schritt wird die Funktion für die verbleibenden Datensätzen in der Zwischenergebnismenge aufgerufen und hat somit nur einen sehr geringen Einfluss auf die Performance.

Testen der Performance

Es ist ein sehr häufiger Fehler, die Performance einer Anwendung nur mit einer kleinen Anzahl von Beispielsätzen zu testen. Das läuft immer rasend schnell und liefert keine aussagekräftigen Performance-Metriken.

Wenn du die Performance deiner Anwendung realistisch einschätzen willst, benötigst du eine realistische Datenmenge. Frag deinen Kunden nach der Menge der Daten, die er plant in der Datenbank der Anwendung zu speichern. Erstell dann Beispieldaten, die vom Volumen her den erwarteten Produktionsdaten entsprechen. Bei extrem großen (SQL Server) Datenbanken ist das vielleicht nicht machbar. Generiere dann zumindest so viele Daten, dass die Datenbank wirklich arbeiten muss, um Abfragen zu verarbeiten. Eine Abfrage, die für die Verarbeitung von 10 Millionen Datensätzen optimiert ist, wird wahrscheinlich auch die effektivste Abfrage sein, wenn sie 10 Milliarden Zeilen bewältigen muss.

Fazit

Abfrageoptimierung ist ein komplexes Thema und erfordert Erfahrung, um es zu beherrschen. Dieser Text und das dazugehörige Video zeigen dir die ersten Schritte auf diesem Weg. Wenn du die hier gezeigten Beispiele einfach auswendig lernst, kannst du bereits viele Abfragen verbessern, die dir bei deiner Arbeit begegnen werden. Außerdem schaffst du damit die Grundlage, um ein fundiertes Wissen über die Leistungsoptimierung von Abfragen aufzubauen.

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.
Die Emailliste wird bei Mailchimp in den USA gespeichert. Diese Auftragsverarbeitung ist vertraglich geregelt. Weitere Details in der Datenschutzerklärung.

Vorteile des Newsletter-Abos

Abfrage-Performance-Tuning - Universelle Grundregeln