Abfragen von Access zum SQL Server

Wussten Sie, dass „Query“ ins Deutsche übersetzt „Abfragen“ heißt Natürlich wussten Sie das. Es ist ja auch die korrekte Bezeichnung, wenn es um Datenbanken geht. Aber wussten Sie auch, dass „Query“ ebenso für „Rückfrage“ und sogar für „Reklamation“ steht ähnlich ist es mit dem Verb: „to query“ wird als „abfragen“, aber auch als „bezweifeln“ und „in Frage stellen“ verstanden. Nun sollen die Ergebnisse von Access-Abfragen natürlich weder bezweifelt noch in Frage gestellt werden; auch nicht, wenn die Abfragen auf einen SQL Server zugreifen. Aber die Antwortzeiten solcher Abfragen werden Sie in manchen Fällen durchaus bezweifeln. Auch die Vorgehensweise, mit der Access die Daten vom SQL Server anfordert, kann in Frage gestellt werden.

Access bietet mit seinen Abfragen ein umfangreiches Tool, um Daten aus verschiedenen Tabellen miteinander zu verknüpfen, auf die wesentlichen Daten zu reduzieren und sie aufzubereiten. Doch die Abfragen haben ihre Tücken, wenn sie auf eingebundene Tabellen des SQL Servers zugreifen.

Der Grund hierfür ist recht einfach: Die eingebundenen Tabellen werden von der Jet-Engine nicht als SQL Server-Tabellen, sondern wie Access-Tabellen gehandhabt. Jede Abfrage wird über die Jet-Engine an den SQL Server übergeben. Und die Jet-Engine entscheidet, welche Daten in welcher Menge vom SQL Server angefordert werden. Das kann bei einfachen Abfragen eine gute, bei etwas komplizierteren Abfragen eine eher schlechte Entscheidung sein.

„Kompliziert“ ist in einem solchen Fall relativ. Die Abfragen müssen nicht unbedingt komplex sein; ein einfaches SELECT auf eine Tabelle mit einem Verweis auf ein Formularfeld in der WHERE-Bedingung reicht schon, um die Jet-Engine dazu zu veranlassen, sicherheitshalber alle Daten der dieser Abfrage zugrundeliegenden SQL Server-Tabellen anzufordern.

Bei kleinen Tabellen mag das nicht relevant sein. Aber Sie arbeiten ja mit dem SQL Server als Backend, weil Sie große Datenmengen bearbeiten wollen. Insofern sollten Sie prüfen, wann in welchem Umfang Daten über das Netzwerk angefordert werden. Doch dazu müssen Sie zunächst wissen, wie die Jet-Engine bei der Anforderung der Daten vorgeht.

Der Abfrageoptimierer

Für jede Access-Abfrage erstellt die Jet-Engine einen Ausführungsplan, anhand dessen der Access-Abfrageoptimierer die optimale Vorgehensweise zur Ausführung der Abfrage ermittelt.

Dies gilt sowohl für Abfragen auf Access-Tabellen, wie auch auf in Access eingebundene SQL Server-Tabellen.

Gerade bei Abfragen auf SQL Server-Tabellen ist das Ergebnis des Abfrageoptimierers entscheidend für die Art und Weise, wie die Daten vom SQL Server angefordert werden.

Im Grunde genommen gibt es für die Ermittlung der Daten vom SQL Server drei Varianten:

• Die Abfrage wird 1:1 an den SQL Server übergeben. Das ist die beste Variante. Der SQL Server führt die Abfrage aus und liefert die ermittelten Datensätze an Access.
• Die Abfrage wird in einzelnen Teilen an den SQL Server übergeben. Dieser Mittelweg kann gut, aber auch schlecht sein. Access teilt die Abfrage in mehrere kleine Abfragen auf und übergibt diese an den SQL Server. Die ermittelten Ergebnismengen werden wieder zurück zu Access übertragen und dort mit den Datensätzen der anderen Abfrageteile verknüpft. Dabei können die übermittelten Ergebnismengen durchaus mehr Daten als eigentlich notwendig enthalten.
• Die Abfrage wird nicht an den SQL Server übergeben. Das ist die schlechteste Variante. Access fordert vom SQL Server alle Daten der in der Abfrage befindlichen Tabellen an und verknüpft diese lokal.

Wie Sie sehen, sind das nicht gerade die schönsten Aussichten, was den Netzwerk-Traffic zwischen Access und SQL Server betrifft.

Um den Netzwerk-Traffic nicht gänzlich in die Hände der Jet-Engine zu legen, müssen Sie die Access-Abfragen anpassen.

Für diese Anpassungen ist es sinnvoll zu wissen, wann der Abfrageoptimierer die Abfrage in welcher Form an den SQL Server übergibt.

Dies ist grundsätzlich von der Abfrage selbst abhängig. Es gibt einige Gründe für eher suboptimale Entscheidungen des Abfrageoptimierers:

• Abfragen mit Ausdrücken oder Funktionen: Ausdrücke oder Access- beziehungsweise VBA-Funktionen können innerhalb einer Access-Abfrage in der Spaltenauflistung wie auch in der WHERE-Bedingung genutzt werden.
• Abfragen mit Access-Funktionen werden nur dann an den SQL Server übergeben, wenn die Funktion vom SQL Server unterstützt wird. Dies ist aber nur bei wenigen Access-Funktionen wie DISTINCT oder bei einigen Aggregationen wie SUM der Fall.
• Bei den SQL Server nicht bekannten Access-Funktionen wie DMAX, FORMAT, PIVOT TRANSFORM beziehungsweise bei VBA-Funktionen oder bei Ausdrücken versucht der Abfrageoptimierer das Beste herauszuholen. Im günstigsten Fall wird die Access-Abfrage in mehrere Abfragen gesplittet. Folgendes Beispiel soll dies verdeutlichen. Die Abfrage

SELECT dbo_Orders.OrderID,
dbo_Orders.Freight
FROM dbo_Orders
WHERE dbo_Orders.Freight) > DMax("UnitPrice","dbo_Order Details")

wird in zwei einzelne Anweisungen gesplittet:

SELECT MAX("UnitPrice") FROM "dbo"."Order Details"

und

SELECT "Freight" "dbo"."Orders"."OrderID"
FROM "dbo"."Orders"

Access übernimmt das Verknüpfen der beiden übertragenen Ergebnismengen.

• Abfragen mit Verweis auf ein Formularfeld: ähnlich wie bei Funktionen oder Ausdrücken sind dem SQL Server auch Formularfelder unbekannt. Insofern werden auch hier die Daten der in der Abfrage verwendeten SQL Server-Tabellen an den Client übertragen. Im besten Fall wird die Abfrage gesplittet, um nur Teilergebnisse vom SQL Server zu ermitteln und diese zur Komplettierung der Abfrage an Access zu übertragen.
• Komplexe Tabellenverknüpfungen: Sofern die Tabellenverknüpfungen zu komplex für den Abfrageoptimierer sind, werden entweder Teilergebnisse auf dem SQL Server ermittelt oder die kompletten Tabelleninhalte der in der Abfrage verwendeten SQL Server-Tabellen an Access übergeben.
• Abfragen mit TOP n und TOP n PERCENT: Die TOP-Anweisung wird von der Jet-Engine bei der Übergabe der Abfrage ignoriert und Access erhält alle Datensätze der in der Abfrage enthaltenen Tabellen. Erst lokal wird die Ausgabe auf die in der TOP-Klausel angegebenen Mengen begrenzt.
• Abfragen mit berechneten Spalten: Die Jet-Engine übermittelt die Berechnungsformeln nicht an den SQL Server. Insofern liefert der SQL Server lediglich die Spalten, mit denen der Wert berechnet wird. Die Berechnung selbst wird von der Jet-Engine übernommen.

Es gibt noch weitere Gründe für den Abfrageoptimierer, die Abfrage nicht 1:1 oder nur teilweise an den SQL Server zu übergeben, wie beispielsweise

• Abfragen mit einem GROUP BY um ein weiteres GROUP BY beziehungsweise DISTINCT,
• Abfragen mit einem JOIN über einen oder mehrere GROUP BY beziehungsweise DISTINCT oder
• Operationen bei UNION– oder Unterabfragen, die heterogene Daten vermischen und noch einige mehr.

Grundsätzlich handelt der Abfrageoptimierer also nach der altbekannten Bauernregel „Was der Bauer nicht kennt, isst er nicht“. Ergo werden nur Abfragen an den SQL Server übergeben, bei denen der Abfrageoptimierer der Meinung ist, dass der SQL Server – oder auch die OBDC-Schnittstelle – diese interpretieren kann.

In welcher Art und Weise die anderen Abfragen am SQL Server ankommen, liegt in der Hand des Abfrageoptimierers. Und den sollten Sie im Auge behalten. Denn wenn die Abfrage nicht auf dem SQL Server ausgeführt wird, sondern für die Ausführung die kompletten Daten oder Teile davon an den Client übertragen werden, degradiert Access Ihre mühsam erstellte Client/Server-Applikation zu einem File-Server-System.

Bleibt die Frage, wie Sie den Abfrageoptimierer beobachten können. Dabei kann Ihnen nun wieder der SQL Server helfen – oder besser gesagt ein Tool des SQL Servers: der Profiler.

Der Profiler

Jede Anweisung, die der SQL Server ausführt, kann mit dem Profiler aufgezeichnet werden. Sie sehen also nicht, wie Access die Abfragen optimiert, aber Sie sehen, welche Anweisungen der SQL Server ausführt.

Sofern Sie die Aufzeichnung auf Ihre Access-Applikation eingrenzen, können Sie also verfolgen, welche Anweisungen SQL Server von Access erhält und ausführt, das heißt, Sie sehen, ob Access nun die komplette Abfrage oder nur Teile oder – im schlimmsten Fall – einfache SELECT-Anweisungen zur Ermittlung der kompletten Tabelleninhalte an den SQL Server übergibt.

Um die Abfragen aufzuzeichnen, starten Sie zunächst den Profiler, den Sie in der Programmgruppe des SQL Servers finden. Dort richten Sie eine Ablaufverfolgung ein, indem Sie im Menü Datei den Befehl Neue Ablaufverfolgung wählen und sich anschließend auf dem SQL Server anmelden, auf den Sie per Access zugreifen möchten.

Im Dialog Ablaufverfolgungseigenschaften geben Sie der Ablaufverfolgung im Feld Ablaufverfolgungsname eine Bezeichnung und wählen danach die Ablaufverfolgungsvorlage Leer aus dem Listenfeld Vorlage verwenden (siehe Bild 1). Anschließend wechseln Sie zur Registerkarte Ereignisauswahl.

Typ der Access-Abfrage

Objekt im SQL Server

(Sicht oder Benutezrdefinierte Funktion auch möglich)