Zeitverhalten einer join Abfrage

[idefix2]

Ich habe zwei Tabellen, Tabelle A (Primärschlüssel A_Nr) mit 200000 Datensätzen, und eine zweite Tabelle B mit vielleicht 10000 Datensätzen (Primärschlüssel B_Nr), die über einen Fremdschlüssel A.B_Nr mit der Tabelle A verknüpft ist.

Wenn ich folgende Abfrage mache, scheint er zuerst eine Riesen Join-Tabelle für alle Datensätze von A zu machen und dann erst über die where-Klausel den einen, den ich will, herauszugreifen (vermute ich auf Grund des Zeitverhaltens):

Code:

select A.*, B.* from A left join B on A.B_Nr=B.B_Nr where A.A_Nr=1785

wie müsste man die Abfrage formulieren, damit er nur für den einen Datensatz mit A.A_Nr=1785 die gejointe Tabelle aufbaut? Das Ganze ist in einem älteren Projekt noch auf Basis von Access 2002, da scheint der SQL-Optimierer von Access noch nicht besonders effizient zu sein.

Ich muss das mittels Query machen und kann nicht locate oder dgl. verwenden, weil die ganze Programmstruktur danach mit dem Ergebnis einer Query weiterarbeitet.

[mkinzler]

Mit etwas (viel) Glück funktioniert

SQL-Code:

select 
  A.*, B.* 
from 
  ( select * from A where A.A_Nr=1785) A
    left join B on A.B_Nr=B.B_Nr;

[idefix2]

Danke, ich probiers einmal so aus.

[Dejan Vu]

Auf welchen Feldern ist ein Index?

[jobo]

Und noch die Frage:
Sind die Tabellen nativ in Access oder sind es verlinkte Tabellen, die faktisch auf irgendeinem DB Server liegen?

Falls nativ Access kann man die Variante von mkinzler noch in 2 Separate Access Abfragen splitten, wenn es keinen Erfolg bringt. Innen die Einschränkung auf ein Satz, außen den Join. Irgendwie wird man Access das schon beibringen können.
Falls nicht nativ, sondern linked tables, kenne ich es auch so, dass Access einfach Blödsinn macht (das war aber Access 2000 oder 2003?), weil es offenbar keine brauchbaren Modellinformationen / Statistik findet oder finden will. Dann lieber gleich auch linked queries (pass through)einsetzen.

[idefix2]

Index natürlich auch auf allen Primärschlüsselfeldern.
Die oben vorgeschlagene Abfrage funktioniert leider nicht, irgend etwas an der Syntax passt Access nicht.
Die Tabellen liegen nativ in Access vor.

und leider kann ich die Abfrage nicht splitten. Das Ganze findet innerhalb eines Programmframeworks statt, dem ich ein SQL Statement übergebe, und das eine Tabelle (in dem Fall mit nur einer Zeile) zurückliefert.

Die Access-Hilfe zu dem Thema ist eine Frechheit:

Syntax
SELECT Feldliste
FROM Tabellenausdruck [IN ExterneDatenbank]

Tabellenausdruck: Ein Ausdruck, der eine oder mehrere Tabellen kennzeichnet, aus denen Daten abgerufen werden. Der Ausdruck kann ein einzelner Tabellenname sein, ein Name einer gespeicherten Abfrage oder ein zusammengesetzter Name, der sich aus einer Verknüpfung (INNER JOIN LEFT JOIN, oder RIGHT JOIN) ergibt.

Wird daraus irgend jemand klug? Wie soll so ein "zusammengesetzter Name" konkret ausschauen? beispiele dazu gibt es in dieser "Hilfe" nicht.

Ein gepeicherte Abfrage könnte ich zur Not verwenden - aber nur sehr ungern, weil wie bekomme ich eine gespeicherte Abfrage automatisiert auf Kundendatenbanken? Das geht vermutlich nicht über ein vom Programm generiertes (SQL)-Statement - und mir ist auch nicht klar, ob und wie ich einer gespeicherten Abfrage Parameter übergeben kann.

[jobo]

Dann versuch die Abfrage zu zerteilen wie beschrieben.

[idefix2]

Das ist leider auch nicht möglich - ich muss einen SQL-String mit einem Select-befehl an das Framework übergeben, das mir dann die Antwort zurückliefert.
Ich hoffe, ich habe jetzt ein Workaround gefunden, muss aber noch schauen, ob das so funktioniert:
Eine Hifstabelle, in der nur eine Zeile mit dem Schlüssel des Datensatzes steht, den ich haben will. Dann mache ich ein left join der Hilfstabelle mit der Tabelle A, und danach erst das left join mit der Tabelle B.
Trotzdem denke ich, dass das irgendwie ohne solche Kunstgriffe gehen müsste.:evil:

edit: So funktioniert es und ist im Vergleich zu vorher sehr viel schneller. Interessieren würde mich trotzdem, ob das nicht irgendwie besser zu lösen ist als mit einer extra einspaltigen und einzeiligen Tabelle. Aber Access 2002 ist natürlich nicht ganz die neueste Software...

[mkinzler]

Ein richtiges DBMS nehmen ;)

[p80286]

Zitat:

Zitat von mkinzler (Beitrag 1299956)

Ein richtiges DBMS nehmen ;)

:thumb:

Nachdem ich mit 70k Datensätzen und Access als Frontend einen fantastischen Schiffbruch erlitten habe kann ich ,freundlich ausgedrückt nur von Access abraten. Meine ehrliche Meinung ist leider mit den Forenregeln nicht kompatibel.

Gruß
K-H

[idefix2]

Ich kann das leider nur bestätigen. Aber hier handelt es sich um ein uraltes Projekt (in Delphi 5 geschrieben) mit über 1.000.000 Zeilen, zum Teil mit eingebundenen Fremdkomponenten, die es längst nicht mehr zu kaufen gibt. Freiwillig stelle ich das nicht um.

[jobo]

Also sicherheitshalber:
Fremdschlüssel B_NR = Primary Key = indiziert?

Ich hab gestern irgendwo gelesen, dass Access offenbar wirklich nicht in der Lage ist, bei stacked queries eine "vorgegebene" Reihenfolge einzuhalten bzw. sich eine brauchbare auszudenken.
Dennoch, hast Du mal etwas mit anderen Verfahren gespielt, z.B. "TOP 1" Aggregat? Ist natürlich unsinnig, könnte dem "Query Optimizer" aber vlt auf die Sprünge helfen. Mglw. gibt's noch andere tolle Krücken, die man auspobieren können.

[idefix2]

Ja, natürlich indiziert, sonst würde ja der Kunstgriff mit der extra Tabelle auch nichts bringen. Das Problem ist, dass die Access Syntax anscheinend kein Join mit dem Ergebnis eines Select vorsieht.
Und bei einer anderen Query, in die mehr Tabellen involviert sind, versagt jetzt der Trick auch, da bekomme ich die Fehlermeldung, dass die On-Klausel auf zu viele verschiedene Tabellen zugreift (es sind mit mit meiner Extra Tabelle genau 5 Tabellen :evil: ), und beim direkten Probieren der Query in Excel von der Excel Hilfe die Empfehlung, WHERE statt ON zu verwenden :!:

[jobo]

Also "natürlich" mit Index ist okay, manchmal glaubt man ja auch nur, es sei ein Index drauf. ;)
Subselects (geklammert) sollten eigentlich gehen, natürlich mit Table Alias!
"Where" ist ja auch nicht schlimm, nur wage ich zu bezweifeln, dass es dazu unter Access eine Notation für "outer" gibt.

[idefix2]

Das Problem ist ja, dass die Abfrage mit where extrem langsam wird, weil er zuerst die Riesentabelle aufbaut und danach selektiert.
Mit der ON Klausel wird der eine Datensatz gleich beim Join herausselektiert, weil die linke Tabelle nur aus einem Datensatz besteht, und nur dieses kleine Ergebnis wird mit einer Reihe von anderen Tabellen gejoint. Bei einem brauchbaren Query-Optimierer, wie ihn jede halbwegs moderne SQL Datenabnk hat, wäre das ganze Theater nicht nötig.
Ich gehe davon aus, dass eine neuere Access-Version das besser machen würde, aber die Umstellung des ganzen Delphi-5 Projekts auf eine neue Access-Version ist mir zu viel Aufwand.

[mkinzler]

Wenn ich Umsteigen würde, würde ich gleich ganz von Access weg wechseln

[Dejan Vu]

Wenn man die Sofware rein zufällig etwas strukturiert hätte, könnte man mit moderatem Aufwand umsteigen. Ich vermute, es wird mit ADO gearbeitet, dann bietet sich der MS SQL-Server (Express) an.

[RoadR69]

Also ich habe mir mal eine Beispieltabelle in meinem Access angelegt mit jeweils der doppelten Anzahl von Datensätzen ...
Ausführungszeit für die Orginal-Abfrage: sofort da, nicht messbar ...

Auch die 2. Abfrage läuft, welche natürlich etwas besser formuliert ist und mit Einfügen von "AS" (Access will das halt so...) läuft die auch.

select
A.*, B.*
from
( select * from A where A.A_Nr=1785) as A
left join B on A.B_Nr=B.B_Nr;

[Dejan Vu]

:thumb: Probieren ist immer besser als mutmaßen.

Dann liegt es wohl an etwas Anderem.

[jobo]

Zitat:

Zitat von RoadR69 (Beitrag 1300132)

Also ich habe mir mal eine Beispieltabelle in meinem Access angelegt mit jeweils der doppelten Anzahl von Datensätzen ...
Ausführungszeit für die Orginal-Abfrage: sofort da, nicht messbar ...

Auch die 2. Abfrage läuft, welche natürlich etwas besser formuliert ist und mit Einfügen von "AS" (Access will das halt so...) läuft die auch.

Klingt plausibel. Mich hat es gewundert, dass sowas simples nicht geht. Access ist keine Wundersoftware, aber sowas könnten sie können.
Da wäre aber eine wichtige Sache, welche Version ist das?
Dann ist da auch noch dieses ominöse Framwork in das "die Abfrage reingeht". Vielleicht ist das der Übeltäter.

[Jasocul]

Ich habe hier leider kein Access. Aber kann man das vielleicht so "austricksen":

Code:

select A.*, B.* 
from A 
left join B 
on A.B_Nr=B.B_Nr 
and A.A_Nr=1785

[user0815]

Vielleicht würde es auch helfen die Datenbank einfach zu öffnen & dann zu: "komprimieren und reparieren".
Ist dies nicht möglich dann evtl. per Code: site:delphipraxis.net access datenbank komprimieren

[idefix2]

Zitat:

Zitat von user0815 (Beitrag 1300146)

Vielleicht würde es auch helfen die Datenbank einfach zu öffnen & dann zu: "komprimieren und reparieren".

Leider nein, die Datenbanken werden regelmässig komprimiert und repariert (allein der Aufwand wäre eigentlich schon ein Grund, das Datenbanksystem zu wechseln :-D ). Für neue Projekte verwenden wir ohnedies je nach den Umständen Firebird oder SQLite.

Und auch der Ansatz von Jasocul geht nicht, Access meldet da (beim Ausprobieren der Abfrage direkt in der Access-Entwicklungsumgebung):
Die ON-Anweisung in Ihrer JOIN-Operation ist nicht vollständig oder enthält zu viele Tabellen. Sie sollten ggf. den ON-Ausdruck in einem WHERE-Abschnitt angeben.

Zitat:

Zitat von jobo (Beitrag 1300135)

Zitat:

Zitat von RoadR69 (Beitrag 1300132)

Also ich habe mir mal eine Beispieltabelle in meinem Access angelegt mit jeweils der doppelten Anzahl von Datensätzen ...
Ausführungszeit für die Orginal-Abfrage: sofort da, nicht messbar ...

Auch die 2. Abfrage läuft, welche natürlich etwas besser formuliert ist und mit Einfügen von "AS" (Access will das halt so...) läuft die auch.

Klingt plausibel. Mich hat es gewundert, dass sowas simples nicht geht. Access ist keine Wundersoftware, aber sowas könnten sie können.
Da wäre aber eine wichtige Sache, welche Version ist das?
Dann ist da auch noch dieses ominöse Framwork in das "die Abfrage reingeht". Vielleicht ist das der Übeltäter.

Es hängen an der Abfrage noch ein paar andere Joins mit grossen Tabellen dran, das dürfte es herunterbremsen.
Und das Framework ist nicht schuld, ich habe es im Debugger verfolgt, die Zeit vergeht beim Open der Query. Ich vermute auch, dass neuere Access Versionen das besser handhaben werden, aber die Anwendung ist mit Delphi5 für Access 2002 entwickelt und ich kann mich erinnern, dass auch der Umstieg von Access 97 auf Access 2002 damals mit einigen Troubles verbunden war, das will ich jetzt nicht riskieren (auch wenn es vielleicht gar kein grosser Aufwand wäre, aber das weiss man im voraus nicht)

Jetzt pfeif ich drauf, mit der Hilfstabelle ist das Zeitproblem für fast alle Fälle gelöst, bei einer speziellen Query geht das zwar auch nicht, aber die wird ohnehin kaum verwendet, die braucht eben auch weiterhin länger.

Nachdem ich schon unter ziemlichen Zeitdruck stehe, spiele ich mich jetzt da nicht weiter. Danke jedenfalls für alle Inputs.

edit: ich sehe gerade

Zitat:

Einfügen von "AS" (Access will das halt so...)

Das probier ich noch aus :)

edit2: So Funktioniert es! :thumb:

[p80286]

Manchmal hat es geholfen, die vorhandene Abfrage zu löschen, und ganz neu aufzubauen/einzugeben. Das ist zwar kurz vor Voodoo aber es hat ja auch niemand behauptet daß die Access-Programmierer nicht in der Karibik sitzen.
U.u. ist Pass-through auch hilfreich.

Gruß
K-H