Office: (Office 2013) Abfrage 2 Tabellen aus 2 SQL Datenbanken sehr langsam

Hallo Josef,

vielen Dank! Ich verstehe, aber die müssten ja in jedem Fall kürzer als die Zeit für den Abruf der Join Abfrage sein oder? Wie auch immer, hier die Werte:

SELECT * FROM TABELLE1

473; 0,13 Sek

SELECT Top 1 * FROM TABELLE1

1; 0,12 Sek

SELECT Top 1 IndiziertesDatenfeld FROM TABELLE1 Order by IndiziertesDatenfeld

1;0,12 Sek






SELECT * FROM TABELLE2

477; 0,11 Sek

SELECT Top 1 * FROM TABELLE2

1; 0,13 Sek

SELECT Top 1 IndiziertesDatenfeld FROM TABELLE2 Order by IndiziertesDatenfeld

1; 0,13 Sek





SELECT * FROM TABELLE1 WHERE ReNr LIKE '1*'

335; 0,12 Sek




Als Indizes sind nur die PrimaryKeys sichtbar, alle anderen Indizes sind in Access als "nein" gekennzeichnet. hm... Aber das scheint in allen Tabellen so zu sein, ist das speziell hier wegen der 2 verschiedenen DBs bei WHERE Abfragen ein besonderes Problem?

Ansonsten gehen ja alle Tabellen und auch Abfragen super schnell abzurufen. Es tritt nur bei diesem Mix aus Tabellen der 2 DBs auf, was du da mit dem Kreuzprodukt beschreibst könnte ich mir hier gut vorstellen.

Wie kann ich den Ablaufplan in diesem Sinne richtig stellen?

 

Wie schnell läuft die SQL-Anweisung
Code:

/edit: gerade sehen ... das läuft schnell genug. *wink.gif*

Wenn das schnell läuft, testest du als nächstes die Auswertung der anderern Tabelle über das Feld, das im Join-Ausdruck verwendet wird.


Ist die von dir verwendete SQL-Anweisung mit join schon irgendwo in diesem Thread zu sehen?

Die Indizes (es könnten auch Mehrfelderindizes sinnvoll sein) siehst du, wenn du im Tabellenentwurf das Index-Fenster über das Menüband öffnest.

mfg
Josef

 

Josef, ich glaube du hast es wirklich erraten! Die beiden Tabellen haben zwar beide einen Primary Key aber weder Frontend noch Backend (da separiert) kennen den Zusammenhang (F-Key), daher erstellt er bestimmt so ein Kreuzprodukt.

Wie kann ich das verhindern?

ich habe bereits in Access eine "Beziehung" der beiden Tabellen angelegt, hilft leider nix...

 

Fremdschlüssel sind für Join-Ausdrucke nicht geschwindigkeitsrelevant. Dafür zählt nur der Index.
Zum Testen: vielleicht hilft es, wenn du die Tabellen neu verknüpfst, um die aktuell einstellten Indizes im Access-Frontend zu sehen.

Ich kenne deine SQL-Anweisung nicht.

 

Ja, unsere Antworten haben sich grad überschnitten, hier die SQL Anweisung, aber die ist halt unspektakulär *biggrin.gif*

ungefähr so sieht sie aus

SELECT X.fIDkunde, X.IDrechnung, X.ReNr, X.ReDatum, X.ReStornoDatum, X.LeistungVon, X.LeistungBis, X.Vorname, X.Nachname, X.ReSumme, X.ReNotes, Y.IDkunde, Y.NachnameRE
FROM VIEWDB1 AS X LEFT JOIN VIEWDB2 AS Y ON X.fIDkunde=Y.IDkunde;


neu verknüpfen bringt nichts, Indizes werden in der Access Entwurf Ansicht alle auf nein gestellt außer der PrimaryKey und ich kann das auch nicht umstellen, da Änderungen der verknüpften Tabellen nicht gespeichert werden können.



Falls relevant und falls ich es noch nicht erwähnt habe (argh! denke aber habs am Anfang erwähnt) die verknüpften Tabellen sind wiederum VIEWS im Backend, aber die werden von Access ja wie Tabellen behandelt. Kann aber natürlich erklären warum z.B. Indizes nicht übernommen werden. Aber wie du auch schon sagst, sollten die Indizes nicht so entscheidend sein bei den paar Datensätzen oder? Oder hängt daran dass er ein Kreuzprodukt erstellt?

 

tatsächlich: die indizes von direkten Tabellen werden übernommen, aber von einer VIEW nicht, das ist aber wohl nur in dieser Konstellation ein echtes Problem wobei ich vermute dass es auch sonst Performanceprobleme bringt. Aber mir wurde immer empfohlen mit VIEWs zu arbeiten, da sie viel schneller sind als Access Abfragen, das ist prinzipiell richtig oder?

 

Jetzt können wir die von ebs vorgeschlagene Variante mit dem "vorher filtern" ausprobieren.

1. Deine Anweisung mir nur wenigen Ausgabefeldern
Code:

etwas umgebaut:
Code:

Bei beiden Varianten würde ich den gleichen Ablaufplan erwarten, bei Access weiß man das allerdings nie so genau. *wink.gif*

Und jetzt noch ein etwas "anderer" Test:
Speicher bitte den SQL-Ausdruck
Code:

In einer Pass-Through-Abfrage und teste damit diesen SQL-Ausdruck:
Code:

Anm.: hilfreich kann auch sein, wenn man sich die SQL-Anweisungen ansieht, die an den Server gehen.
Das ist mir an dieser Stelle allerdings zu viel zum Erklären - da müsstest du dich zuerst selbst schlau machen. *wink.gif*

Grundsätzlich schon - aber eher nur dann, wenn nur eine View verwendet wird und der Jet-Abfrage-"Optimierer" nicht überlegen muss, wie er die Abfrage ausführt, sondern alles an den Server weiterreicht.

 

Hi Josef,

ich habe den WHERE Filter nun nur auf die VIEW1 angewendet, das ganze eingeklammert und erst danach gejoined. Das Ergebnis ist immernoch 14-15 Sek.

Es ist ja sehr auffällig, dass er bei VIEWs die Indizes nicht übernimmt, daher habe ich nun einmal probiert, keine VIEWs zu verwenden sondern direkt alles in der Access-Abfrage zusammenzubauen. Siehe da, die WHERE LIKE Bedingung läuft unter 1 Sek! Zwar nicht super schnell aber immerhin 0,5 Sek. Access Abfragen sind ja immer erheblich langsamer als serverseitige VIEWs.

Access hat also wohl enorme Probleme damit, dass hier VIEWs und nicht Tabellen von verschiedenen Servern verarbeiten werden müssen, bei denen man die Indizes abgesehen vom Primärschlüssel nicht nutzbar machen kann. Soweit ich das einschätze gibt es auch keinen Weg das zu umgehen, außer mit federated tables, die laut all inkl Support hier nicht möglich sind, oder einer Fusion der DBs.

Ich könnte aber für diese Endlosformulare nun im Frontend die Tabellen zusammenbauen und so erstmal arbeiten. Anscheinend sollte man serverseitige VIEWs nicht mit Access Abfragen mixen, zumindest nicht wenn man sie filtern will.

Vielen Dank, ohne euch wäre ich nie darauf gekommen. :-)))

Interesseant ist noch, dass ich bei diesem Test nur die eine VIEW aufgelöst habe, die andere nicht. D.h. er hat nur dann so enorme Probleme, wenn die VIEWs ohne Indizes auf beiden Seiten bestehen. Macht das Sinn!?

Noch weitere Ideen? Ansonsten ist mein Problem erstmal gelöst, damit kann ich arbeiten. Tausend Dank *Smilie

 

Eine Sicht im DMBS ist meiner Meinung nach nur dann schneller als der Tabellenzugriff über ODBC, wenn in der Sicht mehrere Tabellen miteinander verbunden sind. Ansonsten kann Jet/ACE ganz gut mit ODBC-verknüpften Tabellen umgehen.
Der Vorteil der Sicht ist, dass das DBMS den Ausführungsplan gestaltet und der ist meist besser als der von Access bei Access-Abfragen mit mehreren per join verbundenen ODBC-Tabellen.



Einen Test hätte ich noch:
Kannst du der verknüpften Sicht einen Primärschlüssel geben?
Code:

Grund:
Wenn Access/ACE/Jet über ODBC dem Server SQL-Anweisungen weitergibt, werden üblicherweise die Primärschlüsselwerte zurückgegeben und dann erst die restlichen Daten abgeholt. Ob das hilft, weiß ich nicht, aber ausprobieren kannst du es trotzdem. *wink.gif*

Ansonsten: Gibt es bei MySQL indizierte Sichten?

mfg
Josef

 

Ich vermute dass die Sicht in MySQL mit den Indizes arbeiten kann, aber Access bekommt sie nicht übergeben. In meinen Sichten sind immer mehrere Tabellen verbunden, die berechnet MySQL dermaßen schnell, dass sich das auf jeden Fall lohnt, bis auf diese tödliche Kombination hier.


in PHPmyadmin kann ich einer Sicht keinen separaten P-Key zuordnen, die VIEW erkennt diese automatisch.


Die verknüpfte Sicht wird immer beim Verknüpfen mit einem Primärschlüssel versehen, also Access kennt den Primärschlüssel der VIEW. Aber für alle anderen Felder kennt Access dann nicht die Indizes. Womöglich nicht mal die Relationen :S


Ich habe auch die Erfahrung dass der Zugriff auf Tabellen insgesamt gut funktioniert.


Danke!

 

Access braucht die Indizes nicht - außer eventuell die Information über deren Existenz für die Erstellung das Ausführungsplans. Das Problem wird sein, dass Access/Jet/ACE beim Join mit den 2 Sichten einen schlechten Ausführungsplan erstellt.
Falls du dir das einmal selbst ansehen willst: ShowplanCapturer .. damit könntest du die 2 Varianten - Abfrage mit den die Sichten und die Abfrage mit den Tabellen - vergleichen.
Über ODBC-Tracing kannst du sehen, wie mit dem DBMS kommuniziert wird.

Hast du die Variante mit der PassThrough-Abfrage ausprobiert?

Bei so wenig Datensätzen könntest du auch überlegen, die Daten der ersten Sicht in einer temporären Access-Tabelle zwischenzuspeichern.

mfg
Josef

 

Danke Josef,

ich denke du hast völlig recht, der Ablaufplan wird in dieser Konstellation irgendwie enorm ungünstig sein.

Ich greife das wieder auf sobald die Performance nicht mehr ausreichend ist. Ich habe zunächst mit dem hier gelernten eine gute vorläufige Lösung hinbekommen, insb. dadurch dass ich die Filter mit Kombinationsfeldern gebaut habe. Da ich jetzt auch in den Urlaub gehe markiere ich das erstmal als gelöst, danke euch allen und v.a. dir Josef, für die intensive Hilfestellung. :-) LG!