ODBC-Tabellen in Access - Mechanismen und Peformance

Dieser Text geht auf Posting in den Newsgroups de.comp.datenbanken.ms-access und microsoft.public.de.access.clientserver vom 23.12.2001 zurück. – Obwohl das inzwischen 19 Jahre her ist, ist der Inhalt auch heute (2021) immer noch relevant.

Anlass zu diesem Artikel war die Frage nach den Vorteilen eines ADP (Access Data Project) gegenüber dem traditionellen Verfahren, Tabellen eines Server-DBMS über ODBC in eine Access-MDB-/ACCDB-Datenbank zu verknüpfen. Auf ADPs gehe ich in diesem Artikel kaum ein, die Verarbeitung und Performance von Abfragen in einem ADP ist jedoch in diesem Kontext im Wesentlichen identisch mit den hier beschriebenen Pass-Through-Abfragen.

In diesem Text wird davon ausgegangen, dass alle Tabellen auf einem MS-SQL-Server liegen und in eine Access-Datenbank über ODBC verknüpft sind. Die grundsätzlichen Mechanismen sind jedoch nicht spezifisch für Microsoft SQL Server, sondern für ODBC verknüpfte Datenbanken von anderen DBMS, wie Oracle, MySQL oder PostgreSQL weitgehend gleich.

RecordsetType: Dynaset vs. Snapshot

Bevor wir uns ein paar konkrete Abfragebeispiele anschauen, müssen wir erstmal einen Schritt Abstand nehmen und betrachten, wie die verschiedenen Recordset-Typen für Abfragen und Recordsets mit ODBC verknüpften Tabellen in Access funktionieren.

Standardmäßig öffnet Microsoft Access das Recordset aus einer Abfrage als ein beschreibbares Dynaset-Recordset („Schlüsselgruppencursor“). Dies ist eine mehrschrittige Operation. Der erste Schritt ist die Primärschlüssel-Werte der gesuchten Datensätze zu ermitteln. Der/die nächsten Schritte ist/sind die Werte der anderen Spalten aus der Abfrage abzurufen. Dies wird mit Select-Abfragen als Prepared-Statements (praktisch eine Art Stored Procedure) auf dem SQL Server, die den Primärschlüssel als Kriterium verwenden, erledigt. Um die Kommunikation zwischen Access und dem SQL Server zu optimieren, passiert dies üblicherweise in Batches a 10 Datensätze. – Ich habe in einem Text zu ODBC-Multi-Row-Fetch beschrieben, wie das funktionieren soll, es aber manchmal nicht tut.

Die andere verfügbare Option ist das schreibgeschützte Snapshot-Recordset. Eine Abfrage, die ein Snapshot-Recordset liefert, schickt ein SQL (oder mehrere, siehe unten) an den Server, um direkt die Werte aller Spalten, die in der Abfrage benötigt werden abzurufen.

Abfragen auf eine einzelne Tabelle

Eine Abfrage "SELECT spalte1, spalte2 FROM tabelle WHERE spalte1 = kriterium" wird entweder 1:1 an das Server DBMS durchgereicht (Snapshot) oder es wird erstmal übersetzt in "SELECT primärschlüssel FROM tabelle WHERE spalte1 = kriterium" und dann, wie oben beschrieben, werden die anderen Spaltenwerte mit nachfolgenden Abfragen (Dynaset). In beiden Fällen wird der wesentliche Teil der Abfrage auf dem SQL Server bearbeitet.

Nur bei einer Abfrage, in der eine Funktion auf eine der Spalten in der Where-Klausel angewendet wird, wie "SELECT * FROM tabelle WHERE AccessOrVbaFunction(feld) = kriterium" wird der komplette Datenbestand der Tabelle an Access zurückgeliefert und die Abfrage im Wesentlichen von Access bearbeitet. Die weitere Verarbeitung folgt dann den Regeln für Dynaset oder Snapshot.

Die einzige Ausnahme zu dieser Regel sind Funktionen, die in der Liste der unterstützen Skalarfunktionen in der ODBC-Spezifikation aufgeführt werden. Diese Funktionen werden an das Backend mit der {fn FunctionName()} Syntax durchgereicht und dann entweder in die entsprechende Funktion des Backend-DBMS oder, wenn das unterstützt wird, direkt ausgewertet. Das genau Verhalten hängt von dem jeweiligen DBMS, dem verwendeten ODBC-Treiber und deren Versionen ab.

Auch wenn der Funktionsaufruf an den SQL Server durchgereicht werden kann, wird die Abfrage wegen dem Funktionsaufruf für eine Tabellenspalte unter Performanceeinbußen leiden, wie ich in dem Text Abfrage-Performance-Tuning - Universelle Grundregeln erläutert habe.

Beim Abrufen von Daten über das Prepared-Statement optimiert Access zusätzlich noch für die Darstellung großer Datenmengen, denn es werden von Access nur jeweils 10 Datensätze abgerufen und erst wenn der Benutzer in der Ergebnismenge blättert, werden weitere Daten geholt.

Abfragen mit JOINs über zwei Tabellen

Bei JOINS über 2 Tabellen wird analog zu den Abfragen auf eine einzelne Tabelle verfahren, nur werden die Ergebnisdatensätze aus jeder Tabelle einzeln abgerufen und von Access zusammengeführt.

abgerufen und von Access in einer Ergebnismenge zusammengefasst. Dies geschieht nicht direkt durch die Ausführung der o.g. SQL-Statements, sondern diese Statements werden wieder als Prepared Statements aufgerufen.

Soweit so gut. Bei einfachen Abfragen, die quasi 1:1 übersetzt werden können ist die Performance von Linked Tables sehr gut und kaum von direkten Abfragen auf den Server zu unterscheiden, sogar mit dem zusätzlichen Kommunikationsoverhead durch die Dynaset-Fetches.

Was ist mit Updates?

Die Mechanismen, um Datensätze zu aktualisieren liegen außerhalb des Fokus für diesen Text. Trotzdem eine kurze Info.

Wenn ein Datensatz in einer Abfrage, die auf mehrere ODBC-Tabellen basiert, in Access aktualisiert wird, wird Access mehrere Update-Statements (eins für jede aktualisierte Tabelle) and den SQL Server senden. Die Update-Statements werden in eine Transaktion gekapselt, um die Datenkonsistenz sicherzustellen.

JOINs Über Mehr als Zwei Tabellen

Um jetzt nicht in übermäßigen Optimismus zugunsten der verknüpften ODBC-Tabellen zu verfallen, sollte man aber bedenken, dass der Performance- unterschied zu Ungunsten der verknüpften Tabellen immer größer wird, je komplexer die Abfrage wird. Und dabei erfolgt der Performance-Abfall der verknüpften Tabellen nicht linear, sondern exponentiell.

Schon bei einer Abfrage über drei Tabellen in Access-Join-Syntax (die hässlichen, verschachtelte Joins, was anderes versteht Access ja nicht) mit einer WHERE-Klausel auf nur einer Tabelle kann die Katastrophe schon perfekt sein. – Besonders eine Mischung aus LEFT und RIGHT JOINs, die der visuelle Abfragedesigner von Access gern erstellt, lösen schnell dieses Problem aus.

Wenn Access die Abfrage verarbeitet, schickt es erst den inneren, verschachtelten Teil ohne Kriterien an den Server. Dann wird eine zweites SQL-Statement, das den äußeren Teil repräsentiert, and den Server geschickt. – Wieder ohne Kriterien. – Erst dann, nachdem die Ergebnisse an Access übermittelt wurden, werden die Kriterien lokal angewendet.

Zur Verdeutlichung die SQL-Statements der oben erwähnten Abfrage. In Access wird folgende Abfrage ausgeführt (Das Statement wurde von Access-Query-Editor erstellt und nur zur besseren Lesbarkeit neu formatiert):

SELECT table1.column1, table2.column2, table3.column3 FROM (table2 RIGHT JOIN table1 ON table2.column1 = table1.column1) LEFT JOIN table 3 ON table2.column2 = table3.column2 WHERE (((table1.columnXY) = Criterion));

Wenn Access diese Abfrage an den Server sendet, wird sie in mehrere Teilabfragen aufgespalten. – Das gilt sowohl für Dynasets als auch für Snapshots, nur die abgefragten Spalten variieren dabei wieder.

Das Laufzeitverhalten dieser Teilabfragen kann bei größeren Datenbeständen verheerend sein, weil eine riesige Ergebnismenge gebildet und an den Client übermittelt wird, von der tatsächlich nur ein winziger Bruchteil benötigt wird.

Mit meinem Beispieldatenbestand wäre die Ergebnismenge der ersten Teilabfrage ca. 50 Millionen Datensätze. Die ganze Abfrage endet im Timeout. Mit deaktiviertem Timeout würde es wahrscheinlich länger als 45 Minuten dauern, bis Ergebnisse zurückkommen. - Solange möchte ich jetzt aber nicht warten.

Zur Klarstellung: Das hier beschriebene Verhalten von Abfragen über 3 oder mehr Tabellen ist ein Worst-Case-Szenario, dass in diesen Ausmaßen nicht grundsätzlich eintreten muss. Aber je komplexer eine Anwendung mit verknüpften ODBC-Tabellen in Access ist, desto größer ist die Wahrscheinlichkeit, dass gravierende Performanceprobleme auftreten, die auf diese Problematik zurückzuführen sind.

Den obigen Beispielcode habe ich für die ursprüngliche Fassung dieses Artikels in 2001 erstellt und mit den damals aktuellen Versionen von Access, SQL Server und ODBC-Treiber verifiziert. Heute (2021) kann ich die oben beschriebenen, furchtbaren Performanceprobleme bei dem Join über 3 Tabellen mit aktueller Software nicht mehr nachvollziehen.

Das heißt jedoch keinesfalls, dass die hier besprochenen Probleme nicht mehr relevant wären. Gerade diese Woche habe ich ein Video zur Verwendung des SQL Server Profilers aufgezeichnet, in dem ich zeige, wie man diese Art von Problemen analysieren kann. In jenem Video zeige ich ein sehr ähnliches Problem mit vergleichbar unangenehmer Auswirkung auf die Performance.

Lösung für Performanceprobleme wegen SQL-Übersetzung/Aufteilung

Pass-Through-Abfragen

Eine Pass-Through-Abfrage ist eine spezielle Art Abfrage in Access. Das wesentliche Merkmal von Pass-Through-Abfragen ist, das sie nicht von der JET/ACE-Datenbankengine verarbeitet werden. Stattdessen werden sie unverändert über ODBC an das Backend-DBMS durchgereicht (engl.: ‚passed through‘) werden. Der größte Nachteil von Pass-Through-Abfragen ist, dass sie per Definition schreibgeschützt sind. Wenn du Daten aus einer Pass-Through-Abfrage aktualisieren willst, kannst du nicht auf die eingebaute Datenbindungsfunktionalität in Access zurückgreifen, sondern musst die komplette Programmlogik zur Aktualisierung der Daten selbst schreiben.

Zum Vergleich der Performance einer Pass-Through-Abfrage habe ich eine mit einem SQL-Statement erstellt, das dieselben Ergebnisse liefert, wie das 3-Tabellen-Join oben:

SELECT table1.column1, table2.column2, table3.column3 FROM table1 AS t1 LEFT OUTER JOIN table2 AS t2 ON t2.column1 = t1.column1 LEFT OUTER JOIN table3 AS t3 ON t2.column2 = t3.column2 WHERE t1.columnXY = Criterion;

Diese Pass-Through-Abfrage liefert, liefert nach ca. 15 Sekunden (im Vergleich zu ~45 Minuten!) das korrekte Ergebnis von 46 Datensätzen.

Verknüpfte Views

Eine View (oder Sicht) ist die Definition einer Select-Abfrage, die in der Backend-Datenbank auf dem SQL Server gespeichert ist, analog zu einer gespeicherten Select-Abfrage in Access. Du kannst eine solche View erstellen und sie dann in das Access Frontend verknüpfen, als wäre es eine Tabelle. Um zu vermeiden eine große Anzahl Views für jede nur erdenkliche Abfrage erstellen zu müssen, ist es ratsam in Views nur Tabellenbeziehungen über Joins abzubilden, aber keine Kriterien, die nicht für diese Beziehungen relevant sind, einzuschließen.

Für die JET/ACE-Engine gibt es keinen Unterschied zwischen einer eingebundenen ODBC Tabelle oder View. Für die obige Abfrage bedeutet das, dass Access nicht weiß, dass die eingebundene View eigentlich eine Abfrage auf 3 Tabellen ist. Wenn Access also eine Abfrage auf diese View an den SQL Server weitergibt, wird diese weitestgehend unverändert gelassen, wie oben bei „Abfragen auf eine einzelne Tabelle“ beschrieben. Jegliche Kriterien, die in der Access-Abfrage verwendet werden, werden an den SQL Server weitergereicht und dort ausgewertet und somit die oben beschriebenen Performanceprobleme vermieden.

Fazit

Eine Client-Server-Applikation mit verknüpften ODBC Tabellen kann in Microsoft Access erstellt werden. Aber man darf sich keinesfalls der Illusion hingeben, man könne einfach die Tabellen der Serverdatenbank nach Access verknüpfen und dann in Access genauso arbeiten, wie man das von reinen Access-Anwendungen gewohnt ist. Es wird ein ganzer Haufen an Optimierungen fällig und einige Schwächen der JET/ACE-Engine bei der Zusammenarbeit mit Server-DBMS müssen umgangen werden, wenn man eine performante Anwendung erstellen möchte.

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

Microsoft Access –Verknüpften ODBC Tabellen – Mechanismen und Performance