Office: (Office 2007) Beschleunigung einer Abfrage

Was der Abfrageoptimierer wirklich kann, weiß ich nicht so recht. In Jet sind Ausführungspläne nur für einfachere Abfragen sichtbar. Wenn es etwas komplexer und tiefer wird, gibt es dazu keinen vorgezeigten Ablaufplan (oder ich habe ihn mir noch nicht erschlossen), so dass ich mich dann eher auf Erfahrung+Intuition verlasse und den Rest dann ganz einfach probieren würde.

Wichtig ist natürlich das Wissen: Ein Abfrageoptimierer ermittelt nicht zwingend den optimalsten Ablauf (nach dem Optimieren soll ja auch noch ein wenig Zeit für das eigentliche Ausführen der Abfrage sein und beides sollte in einem gesunden Verhältnis zueinander stehen), sondern einen "nicht ganz schlechten" Ablauf.

Eine Erwartung eines gleichen Ablaufplanes setzt dann vermutlich auch eine Erwartung eines durchgeplanten Standes von Indexierung und Modellierung voraus. Ich würde da eher noch ein Fragezeichen machen.

Kurzer Abweg:
Code:

Beide Abfragen werden (lt. Professor Felix Naumann, Hasso Plattner Institut, Universität Potsdam) identisch ausgeführt: Erst werden über ein CROSS JOIN alle Datensätze miteinander kombiniert, im zweiten Schritt werden die Datensätze ermittelt, die in den Kriterien die gewünschten Übereinstimmungen haben. Man kann sich nun leicht vorstellen, dass, wenn man eine Tabelle vorfiltert, das sich ergebende Recordset aus dem CROSS JOIN deutlich kleiner ist und somit auch schneller erstellt und im nächsten Schritt auf gleiche ID's verglichen wird.

Da eine solche Maßnahme in den meisten Fällen sinnvoll sein wird, kann man dem Optimierer helfen, dass er diesen Schritt unbedingt und unabhängig von einem Optimierer ausführt: Vor Filtern und JOIN wird der FROM-Teil und damit die zugrundeliegende Tabelle ausgewertet.

Zurück zum eigentlichen Thema:
In der Abfrage gibt es einen erheblichen Gruppierungsabschnitt. Dabei werden auch berechnete Felder gruppiert (spätestens dort gibt es auch keine Indexunterstützung), und es wird über viele Felder gruppiert (=> Aufwand!). Es ist aber keine Aggregation (Summieren, Minimum, Maximum, ...) erkennbar.
Also wird aus Spaß gruppiert (umsonst, aber nicht kostenlos).
Oder aber durch die Verknüpfungen (JOIN's) werden Datensätze vervielfältigt und müssen dann wieder auf Non-Duplikate zurückgeführt werden.
Da wie oben besprochen Vervielfältigen viel Arbeit sein kann und Gruppieren von viel auch, könnte man u.U. das Abfragedesign deutlich anders stricken nach dem Prinzip: Erst Rechnen, dann Verknüpfen (statt des üblichen Alles-Verknüpfen, dann Irgendwie-Rechnen).

Der verwendete HAVING-Teil ist natürlich in Wirklichkeit eine WHERE-Aufgabe (WHERE filtert vor Gruppieren, HAVING nach Gruppieren). Auch hier würde ich mich nicht auf den Abfrageoptimierer wegen einer Korrektur verlassen, sondern selber die Richtung vorgeben.

Das alles sind aber eher allgemeine Tipps für eine allgemeine Abfrageausführung. Warum ein zusätzliches Kriterium einen eklatanten Performanceeinbruch bereitet - da bin ich derzeit ratlos.

Zum Nachlesen habe ich die obige Abfrage noch einmal wiederholt.
Code:

Nachtrag zum inzwischen neu erschienenen Beitrag:

Wenn die ursprüngliche Abfrage trotz ihrer offensichtlichen Schwächen sehr schnell ist und ein zusätzlicher eingebauter Filter Probleme bereitet, könnte man doch erst die ursprünliche Abfrage ausführen und in einer zusätzlichen aufgesetzten Abfrage die Filterung vornehmen. Für eine Filterung von 230 auf 30 DS braucht man nun gar nichts mehr:

Code:

 

Hallo Eberhard,

welchen SQL-String meinst du? Müsste der FROM-Teil nicht nur lauten:

Code:

?

Steffen

 

Wenn Du die vorhandene Abfrage mitverarbeiten willst, musst Du sie natürlich mitführen. Das A ist hier nur ein ALIAS für das Ganze, siehe auch Grundlagen - SQL ist leicht (2) - Alias
Gleichlautend könntest Du Deine bisherige Abfrage als gespeicherte Abfrage ablegen und aus dieser Abfrage als Tabelle eine neue Abfrage mit dem zusätzlichen Filter erzeugen. Dann ist entsprechend der Name der gespeicherten Abfrage der Alias für die dahinterliegende SQL-Anweisung.


Nebenbei: Aliase für Tabellen würden auch schon die vorhandene SQL-Anweisung spürbar verkürzen und die Lesbarkeit deutlich verbessern.

 

Die Filter-Abfrage sieht jetzt so aus:

Code:

Diese (einfache) Abfrage dauert aber immer noch gut 1 min.

Steffen

 

Oben schon einmal angesprochen: Wie steht es um die Indizierung der drei verwendeten Felder?
Übrigens müssen die verwendeten Felder auch in der vorherigen Abfrage im SELECT-Teil alle auftreten. Das kann ich beim schnellen Anschauen nicht erkennen.

 

Hallo!

Es macht auch keinen Unterschied, ob du die Join-Anweisungen in eine Abfrage auslagerst und dann in einer anderen Abfrage diese filterst.
Das ist vom Ausführungsplan identisch.

Meiner Meinung nach sind die Join-Gruppen nicht ideal angeordnet.

Du musst im Prinzip darauf achten, was die Datenmenge am schnellsten reduzieren kann.
Ich nehme an, dass der Filter "auftrID=Formulare!frmAuftragsbearbeitungAufträgeAuswerten!cboAuftrag" den Datenmenge gut reduziert.
=> die TAbelle tblAufträge gehört so im Join angeordnet, dass man jet dabei hilft mit dieser Auswertung zu starten. (Passende Indizes sind dabei natürlich sehr wichtig, denn ohne Index kann der Abfrageoptimierer nicht viel optimieren.)


Mögliche Reihenfolge zum Ausprobieren:
Code:

Wichtig: das ist vermutlich kein funktionierender Jet-SQL-Code, da die Join-Klammern richtig gesetzt sein müssen.

Der von Eberhard angesprochen Punkt bezüglich Gruppierung: ich nehme an, du willst keine doppelten Datensätze haben, die sich aufgrund der vielen Tabellen entstehen.
=> Kannst du eventuell die Abfrage in 2 Teile gliedern?
1. Teil: Auswertung des Ergebnisses (berechnete Felder für Zeiten usw.)
2. Teil: Ergänzung des 1. Teils um die "Beschreibungsfelder"
Vielleicht ergibt sich dadurch für den 1. TEil eine Abfrage, die mit weniger Tabellen auskommt.

Beim Lesen der Join-Anweisungen hatte ich das Gefühlt, dass da eventuell mehr Datensätze erzeugt als benötigt werden. (Ich kenne allerdings den Inhalt und Zusammenhang der Tabellen nicht.)

@ebs:

Das passiert meiner Meinung nach dann, wenn der Abfrageoptimierer nicht optimiert sondern verschlechtert, weil er auf der falschen Seite zum Filtern beginnt und daher viele zu viele Datensätze holt um diese später wieder einzuschränken.


mfg
Josef

 

Ich dachte, das galt vor allem für die umfangreiche Abfrage. Dann muss ich mich wohl doch mal in das pdf einlesen...

Das habe ich schon in die vorherige Abfrage eingefügt:

Code:

Anm.: Die Felder hatte ich vorher schon in der Entwurfsansicht, allerdings unsichtbar. Daher tauchten sie im SQL-Code nicht auf. Die rot markierten Feldnamen habe ich neu vergeben.

Steffen

 

\@Toffel13:

Ich auch nicht. Vielleicht lässt sich das ändern (Demo)?
Vielleicht gibt es da einige weitere "Fouls".

@josef: Deine Vorschläge werte ich jetzt aber auch "nur" als Unterstützung bis Vorgabe für den Optimierer - was ich gut finde.

 

Nein. Das Wissen um und der Einsatz von Indizes ist kein einmaliger Akt für irgendeine Aufgabe, sondern idealerweise Normalzustand für einen, der eine Datenbank anfasst und mehr als purer User ist.
Neben der Theorie: Es kann auch etwas bringen.
Performance total mies: Man möchte jedem eine so einschneidende Erfahrung wünschen, um Lernwillen und Gewohnheitsbildung zu befruchten.

 

Hallo,

mit einer Demo-DB wird es schwierig. Mit den Primärschlüsseln, Datentypen usw. sollte aber alles ok sein. Oder wo wären noch Fehler zu vermuten? Andere Abfragen mit teils zahlreichen Tabellen funktionieren auch recht schnell.

Steffen

 

Das ist ja nun ein Argument. Weiter so.

Mindestinformation zu Tabellenzusammenhängen (Beziehungsbild): Null
Antwort auf Frage nach Gruppierung: Null
Andere Abarbeitung durch anderes Abfragedesign: sowieso Null

Meine "Ratlosigkeit" basiert auch darauf, dass ich der gesamten geschilderten Situation misstraue. Da kann ich nur unterstellen, dass Wesentliches bisher verschwiegen wird.

 

\@Eberhard:

Dass der Jet-Optimierer nicht zu den schlauesten gehört wissen wir.
Wenn man ihm aber ein wenig bei der Join-Reihenfolge hilft, kann er vielleicht den Rest selbst erledigen. *wink.gif*

Ob passende Indizes vorhanden sind und wie die Abfrage abgearbeitet wird, würde man im Showplan sehen. Den kann aber nur Steffen liefern.
Und solange da nichts kommt, denke ich mich nicht näher in die Problemstellung hinein.

Das Thema an sich wäre durchaus interessant, da man testen könnte, wie man dem Jet-Abfrageoptimierer helfen kann.


mfg
Josef

 

Ein Beziehungsbild habe ich jetzt hochgeladen (s. u.).

Das hatte ich mehr unbewusst eingesetzt. Habe jetzt festgestellt, dass doppelte Einträge entstehen, wenn ich keine Gruppierung verwende. Die Abfrage verläuft aber auch dann nicht wesentlich schneller.

Ich war ein wenig überfordert mit den Vorschlägen und wusste nicht so richtig, wo ich anfangen soll. Ich hatte gerade angefangen, mich in die pdf über die Indices einzuarbeiten, verstehe aber dort erst mal nur Bahnhof.
Dann versuche ich es jetzt mal mit der Abfragestruktur.

Steffen

 

Mit Datenmodellen, wo man in den Beziehungen mehrfach im Kreisverkehr fahren kann, habe ich keine Erfahrungen und somit keine Vorstellungen.
Ich würde aber etliche Zirkelbezüge mit unerwarteten Auswirkungen befürchten, insbesondere wenn man so viele (alle?) Tabellen in die Abfrage einbezieht.

 

Das lässt sich m. E. nicht vermeiden, z. B. hängt die Arbeitsleistung vom Einzelteil und dem Material ab. Oder sollte man das anders lösen? Ehrlich gesagt würde ich die DB deswegen aber nicht umstellen wollen.

Es sind nicht alle Tabellen in der Abfrage enthalten. Die DB umfasst insgesamt 19 Tabellen. Da ich Werte aus den verschiedensten Tabellen benötige, muss ich recht viele Tabellen in die Abfrage einbeziehen, um sinnvolle Ergebnisse zu erhalten.