Office: (Office 2007) Beschleunigung einer Abfrage

Hallo,

ich habe eine Abfrage mit 10 Feldern - darunter 2, in denen mehrere Tabellenfelder zusammengefasst werden - und 3 Feldern mit je einer Bedingung. Zwei der Bedingungen sind einfach, z. B. "€/kg". Die dritte Bedingung bezieht sich auf die Auswahl einer Combobox. Die Abfrage basiert auf 16 Tabellen. Soweit ist das noch nichts Besonderes. Die Ausführung der Abfrage dauert allerdings über 3 min. Wenn ich die Bedingung mit der Combobox durch eine Zahl ersetze (für die ID, die übergeben wird), ändert sich nichts. Wenn ich die Bedingung allerdings entferne, dauert es nur noch einen Bruchteil einer Sekunde. Wie kann eine einzige Bedingung eine Abfrage so stark verlangsamen? Das Ergebnis wird dabei etwa von 230 auf 30 DS reduziert. Und was kann ich tun, um die Berechnung zu beschleunigen?

Viele Grüße
Steffen

:)

 

Hallo!

.. bis du die ersten Datensätze siehst, oder bis du zum letzten Datensatz kommst?
Anm.: Access zeigt in einer Abfrage oder Formular bereits Datensätze an, während der Rest noch gefiltert wird.

Code:

... wird von Jet glaube ich zu "not =" umgeformt und das lässt in Jet keine Index-Nutzung zu.

Allerdings sollte bei einer Ausgangsdatenmenge von 230 Datensätzen ein Tablescan kein Problem sein.
Oder sind die 230 DS bereits eine vorgefilterte Datenmenge?
Wenn z. B. die Tabelle mit dem Feld, das auf "€/kg" gefiltert wird ein paar Millionen Datensätze hat und dieser Filter zu einem Table scan über alle DS führt, kann das dauern.

Zum Ausprobieren:
Ersetze "€/kg" durch (< "€/kg" AND > "€/kg").

Vermutlich spielt die Abarbeitungsreihenfolge der Filter inkl. Join-Bedingungen eine Rolle.
Den aktuellen Ausführungsplan (Jet Showplan) könntest du dir z. B. mittels ShowplanCapturer von Sascha Trowitzsch ansehen.


Da 16 Tabellen in der Abfrage miteinander verknüpft sind: sind das Access-Tabellen oder eventuell ODBC-verknüpfte Tabellen aus einem aktivem Backend?


Anm.: dass in den 16 Tabellen passende Indizes enthalten sind, setze ich einfach einmal voraus. *wink.gif*

mfg
Josef

 

Hallo Josef,

ich habe den Fehler offenbar gerade gefunden (leider etwas zu spät, sorry). Eine Tabelle war doppelt enthalten. Seltsamerweise waren beide auf die gleiche Art verknüpft. Durch zwischenzeitliche Änderungen hatte ich bei der Erstellung wohl vermutet, dass die Verknüpfungen deshalb fehlten (ist aber schon etwas länger her). Dadurch sind dann wohl überflüssige Schleifen entstanden. Komischerweise hatte die Abfrage aber bis vor kurzem noch deutlich schneller funktioniert.

Ich bekomme ein Ergebnis (Abfrage, Formular) immer erst angezeigt, wenn die Abfrage vollständig berechnet wurde.

... wird von Jet glaube ich zu "not =" umgeformt und das lässt keine Index-Nutzung zu.

Allerdings sollte bei einer Ausgangsdatenmenge von 230 Datensätzen ein Tablescan kein Problem sein.
Oder sind die 230 DS bereits eine vorgefilterte Datenmenge?

Zum Ausprobieren:
Ersetze "€/kg" durch (< "€/kg" AND > "€/kg").

Vermutlich spielt die Abarbeitungsreihenfolge der Filter inkl. Join-Bedingungen eine Rolle.
Den aktuellen Ausführungsplan (Jet Showplan) könntest du dir z. B. mittels ShowplanCapturer von Sascha Trowitzsch ansehen.


Da 16 Tabellen in der Abfrage miteinander verknüpft sind: sind das Access-Tabellen oder eventuell ODBC-verknüpfte Tabelle aus einem aktivem Backend?


Anm.: dass in den 16 Tabellen passende Indizes enthalten sind, setze ich einfach einmal voraus. *wink.gif*

Das sollte sich damit erledigt haben. Danke aber trotzdem für die Ausführungen. Das kann in anderen Fällen nützlich sein.

Grüße
Steffen

 

Hallo,

das war wohl doch nicht die Ursache. Aus irgend einem Grund dauert die Abfrage jetzt doch wieder länger. Ich werde noch mal alles durchgehen.

Grüße
Steffen

 

Hallo Josef,

... wird von Jet glaube ich zu "not =" umgeformt und das lässt in Jet keine Index-Nutzung zu.

Allerdings sollte bei einer Ausgangsdatenmenge von 230 Datensätzen ein Tablescan kein Problem sein.
Oder sind die 230 DS bereits eine vorgefilterte Datenmenge?
Wenn z. B. die Tabelle mit dem Feld, das auf "€/kg" gefiltert wird ein paar Millionen Datensätze hat und dieser Filter zu einem Table scan über alle DS führt, kann das dauern.

Zum Ausprobieren:
Ersetze "€/kg" durch (< "€/kg" AND > "€/kg").

< "€/kg" AND > "€/kg" funktioniert nicht. Die Abfrage zeigt dann keine Ergebnisse mehr. Offenbar funktionieren nicht mit Text. ist anscheinend unabhängig vom Datentyp!?
Ich habe als Bedingung jetzt Nicht "€/kg" eingefügt.

Das kann ich hier nicht ausführen und werde es mir daher später ansehen.

Das sind normale Access-Tabellen. Auch die Abfrage wurde in der Entwurfsansicht und nicht durch SQL-Eingabe erstellt.

Würde die Abfrage sonst überhaupt funktionieren bzw. ein Ergebnis liefern?

Grüße
Steffen

 

Hallo!

Da muss OR statt AND rein.
Ein Wert kann nicht größer als "x" und gleichzeitig kleiner als "x" sein. *wink.gif*

Bezüglich Indizes:
Eine Abfrage kann man auch auf Tabellen ohne Indizes machen. Ein Index kann aber das Filtern beschleunigen.

mfg
Josef

 

Die Vergleichsoperatoren '' funktionieren durchaus mit Text.
Es werden die ASCII Codes der einzelnen Zeichen verglichen.

 

Stimmt auch wieder. *Smilie Leider geht die Abfrage damit aber nicht schneller.

Die doppelte Tabelle scheint immerhin ein Fehler gewesen zu sein. Die Abfrage dauert jetzt noch ca. 1 min. Komischerweise ging es kurz nachdem ich die Tabelle entfernt hatte innerhalb weniger Sekunden.
Wenn ich einen der Filter entferne, verkürzt sich die Rechenzeit enorm auf etwa 5 sec.

Grüße
Steffen

 

Wenn du die SQL-Anweisung (in lesbar formatierter Form) zeigst, kann vielleicht jemand eine mögliche "Bremse" erkennen.

mfg
Josef

 

Hallo,

hast du mal ausprobiert, dir die 230 DS komplett in einer (gespeicherten) Abfrage zurückgeben zu
lassen und erst in einer zweiten Abfrage diese DS zu filtern.
Wenn das etwas bringt, wäre die GesamtSQL-Anweisung zu optimieren.
Oder du hast einen der Fälle, in denen Jet suboptimiert (Joins, Subquerys).

 

1) Indizes haben und nutzen: Grundlagen - SQL ist leicht (8) - Index
2) Die Abfrage so formulieren, dass der Jet-Optimierer besser optimieren kann. Bspw. ist ein JOIN aufwändiger als eine einfache Filterung (bei Berücksichtigung von 1) ). Daher könnte man eine gefilterte Tabelle (= Abfrage) in der neuen Abfrage joinen statt erst joinen und dann filtern.

Die werden auch alle in dieser Abfrage benötigt?
Schmale und flache Recordsets (schnellstmögliche Beschränkung auf die nur notwendigen Daten) dienen der Performance.

 

Hallo Josef,

hier der SQL-Code:

Code:

Muss ich mit dem Showplan-Tool noch was machen? Wenn ich es starte und dann die Abfrage ausführe, passiert nichts.

Alles Andere dann morgen.

Steffen

 

Eine lesbar formatierte Form sieht anders aus. *rolleyes.gif*

Beim Überfliegen:
Da deine Kriterien nur die Tabelle tblArbeitsleistungen betreffen, solltest du
diese vor dem Joinen vorfiltern
... Inner Join (SELECT x,y FROM tblArbeitsleistungen) ...

 

Hallo!

Möglicherweise ist der Join-Ausdruck durch die Klammern etwas ungünstig für den Jet-Abfrageoptimierer.

Versuche einmal die Abfrage neu aufzubauen, indem du mit dem Einfügen der tblArbeitsleistungen-Tabelle startest und von dort ausgehend alle anderen Tabellen verbindest. .. Damit als Ergebnis die Tabelle tblArbeitsleistungen "außen" im Join-Block steht.

Weiters könntest du ausprobieren, ob es hilft, wenn du die anderen Tabellen mit left join verknüpftst - natürlich nur dann, wenn es das Ergebnis nicht beeinflusst.
BTW: Tabellen!Feld ist für SQL unüblich. Normalerweise schreibt man Tabelle.Feld.

Bezüglich Ausführungsplan: Damit der neu erstellt wird, musst du entweder die Datenbank komprimieren oder die Abfrage ändern.


@Marsu und ebs: Das Vorfiltern einer Tabelle für einen Join bringt bei Jet etwas? Ist der Abfrageoptimierer wirklich so schlecht, dass er das nicht von selbst kann? *wink.gif*

Ich würde bei folgendem Beispiel immer den gleichen Ausführungsplan erwarten (vermutlich arbeite ich schon zu lange mit T-SQL *wink.gif*):
Code:

@Steffen: im obigen Beispiel könnte ein 2-Felder-Index in der Tabelle T2 über xxx und fiT1 helfen.

Zum Ausprobieren wäre dann auch, ob so etwas hilft:

Damit sollte Jet mit dem Filter der Tabelle 2 beginnen und erst an dieses Ergebnis die T1 verbinden.

mfg
Josef

 

Hallo Marsu,

habe ich jetzt ausprobiert. Die Abfrage ohne die Filter dauert weniger als 1 sec. Die Filter-Abfrage dauert genauso lange wie die ursprüngliche (ca. 1 min).
Heißt das, die Abfrage ist nicht wirklich über SQL optimierbar?

----

Es war gestern schon spät. Zudem weiß ich nicht, wie SQL-Code normalerweise gegliedert wird. Deshalb habe ich den Code aus Access rüberkopiert. Er ist wie gesagt nicht direkt von mir geschrieben, sondern durch Access mit den Angaben aus dem Abfrage-Editor. Daher auch die Tabelle!Feld-Anweisungen.

Hier erst mal der SQL-Code noch mal gegliedert:

Code:

Grüße
Steffen