Language: Deutsch English















Last Update: 2017 - 07 - 09





Microsoft Access - Abfragen auf verknüpfte SQL-Server-Tabellen

Dieser Text ist die überarbeitete und erweiterete Version meines Artikels <a04s01.bl.1@knight.pstiefel.de> in den Newsgroups de.comp.datenbanken.ms-access und microsoft.public.de.access.clientserver vom 23.12.2001.

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-Datenbank zu verknüpfen. Da diese Thematik in den Access-Newsgroups in letzter Zeit häufiger angesprochen wurde, habe ich den Text etwas erweitert und hier auf der Website bereitgestellt.

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.

Abfragen auf eine einzelne Tabelle

Eine Abfrage "SELECT * FROM tabelle WHERE PrimaryKey = kriterium" wird praktisch 1:1 an das Server DBMS durchgereicht. Genauer: es wird ein Prepared-Statement (praktisch eine Art Stored Procedure) mit dem Kriterium als Variable erstellt und dann dieses Prepared-Statement mit den jeweiligen Kriterien aufgerufen.

Eine Abfrage "SELECT * FROM tabelle WHERE feld = kriterium" wird übersetzt in "SELECT PrimaryKey FROM tabelle WHERE feld = kriterium" und dann wird, wie oben beschrieben, ein Prepared-Statement mit einer Where-Condition auf dem PrimaryKey der Tabelle angelegt und aufgerufen.

Erst bei einer Abfrage "SELECT * FROM tabelle WHERE AccessFunction(feld) = kriterium" wird der komplette Datenbestand der Tabelle an Access zurückgeliefert und die Abfrage im wesentlichen von Access bearbeitet um den PrimaryKey der gesuchten Datensätze zu ermitteln. Die tatsächlich gesuchten Datensätze werden wieder, wie oben, über ein Prepared-Statement anhand der PKs abgerufen.

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 (handgeschriebenen) 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 gejoined. Das ist AFAIK erforderlich um die Abfrage aktualisierbar zu halten, denn Aktualisierungen werden als UPDATE-Statements (mit dem PrimaryKey und dem Feld aus der JOIN-Verknüpfung als Kriterium) nur auf die betroffene Tabelle umgesetzt.

Zur Verdeutlichung folgendes Beispiel:

SELECT *
 FROM tabelle1 AS t1
  INNER JOIN tabelle2 AS t2
   ON t1.feld1 = t2.feld1
 WHERE t1.feld1 < 50;

wird übersetzt in

SELECT t1.PrimaryKey, t2.PrimaryKey
 FROM tabelle1 AS t1
  INNER JOIN tabelle2 AS t2
   ON t1.feld1 = t2.feld1
 WHERE t1.feld1 < 50;

Anschließend werden dann aus beiden Tabellen die gesuchten Daten mit

SELECT * FROM tabelle1 WHERE PrimaryKey = kriterium

und

SELECT * FROM tabelle2 WHERE PrimaryKey = kriterium

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 damit sehr gut und kaum von direkten Abfragen auf den Server zu unterscheiden.

JOINs über mehr als zwei Tabellen

Um jetzt nicht in übermäßigen Optimismus zugunsten der Linked Tables zu verfallen, sollte man aber bedenken, dass der Performance- unterschied zu ungunsten der Linked Tables immer größer wird, je komplexer die Abfragen werden. Und zwar erfolgt der Performance- Abfall der linked Tables nicht linear, sondern exponential.

Schon bei einer Abfrage über drei Tabellen in Access-Join-Syntax (verschachtelte Joins, was anderes versteht Access ja nicht) und einer WHERE-Condition auf nur einer Tabelle ist die Katastrophe perfekt. Access schickt als erste Abfrage die innere Verschachtelung ohne Kriterium an den Server. Die Ergebnismenge dieser Teilabfrage wäre bei meinem Beispieldatenbeständen ca. 50 MIO Zeilen(!) groß. Die ganze Abfrage verendet im Timeout. Erfahrungsgemäß würde es deutlich länger als 45 Minuten dauern, bis Ergebnisse zurückkommen. - Solange möchte ich jetzt aber nicht warten.

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 *
 FROM (tabelle2
        RIGHT JOIN tabelle1
         ON tabelle2.feld1 = tabelle1.feld1)
  LEFT JOIN tabelle3
   ON tabelle2.feld2 = tabelle3.feld2
 WHERE (((tabelle1.feldXY) = 'Kriterium'));

diese Abfrage wird in mehrere Teilabfragen übersetzt. Die erste Teilabfrage sieht so aus:

SELECT tabelle2.PrimaryKey,
       tabelle2.feld1,
       tabelle2.feld2,
       tabelle3.PrimaryKey
 FROM tabelle2
  LEFT OUTER JOIN tabelle3
   ON (tabelle2.feld2 = tabelle3.feld2)

Schon das Laufzeitverhalten dieser ersten Teilabfrage ist, bei größeren Datenbeständen, verheerend, weil eine riesige Ergebnismenge gebildet wird, von der tatsächlich nur ein winziger Bruchteil benötigt wird.

Zum Vergleich: Die gleiche Abfrage, mit einfacher JOIN-Syntax als Pass-Through-Abfrage an der Server geschickt, liefert nach ca. 15 Sekunden(!) das korrekte Ergebnis von 46 Datensätzen.

Das SQL-Statement der Pass-Through-Abfrage lautet wie folgt:

SELECT *
 FROM tabelle1 AS t1
  LEFT OUTER JOIN tabelle2 AS t2
   ON t2.feld1 = t1.feld1
  LEFT OUTER JOIN tabelle3 AS t3
   ON t2.feld2 = t3.feld2
 WHERE t1.feldXY ='kriterium';

Um hier kein Mißverständnis aufkommen zu lassen. Das hier beschriebene Verhalten von Abfragen über 3 oder mehr Tabellen ist ein worst case Szenario, dass in dem beschrieben Ausmaßen nicht grundsätzlich eintreten muss. Aber je komplexer eine Anwendung mit Linked Tables in Access ist, desto größer ist die Wahrscheinlichkeit, dass gravierende Performanceprobleme auftreten, die auf diese Problematik zurückzuführen sind.

Fazit

Eine Client-Server-Applikation mit Linked Tables in Access zu erstellen ist nicht grundsätzlich schlecht. 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, wenn man eine performante Anwendung erstellen möchte. Sobald man den Aufwand für diese diese Optimierung mit einkalkuliert, ist es IMO deutlich einfacher gleich ein Access-ADP zu erstellen, in dem zwar einges nicht so einfach umzusetzen ist, wie in einer MDB-Datenbank, aber dafür auch nicht die Schwächen der JET-Engine bei der Zusammenarbeit mit Server-DBMS mühsam umgangen werden müssen.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Abonniere meinen Newsletter

*

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.



© 1999 - 2017 by Philipp Stiefel