Grundlagen

 

Nur benötigte Felder ausgeben

 

Felder, die im Abfrageergebnis nicht unbedingt gebraucht werden, sollte man weglassen. Viele spezialisierte Abfragen sind schneller als wenige Universalabfragen. Zudem sind sie ergonomischer, da man einen Benutzer, der eine Telephonnummer eines Kunden benötigt, nicht mit anderen Daten, die er im Moment gar nicht braucht, verwirren soll.

Das heißt natürlich nicht, daß man dem generischen Paradigma untreu werden und auf variable Parameter zugunsten von literalen Kriterien verzichten soll. Erfreulicherweise hinsichtlich Wartbarkeit und Arbeitserleichterung liegt hierin kein schädliches Potential.

 

Speziell bedeutet das:

 

• Kein *
• Bedingungen ausblenden, wenn es keine Oder-Ausdrücke sind

 

Der Inhalt eines einfachen Bedingungsfeldes ist sowieso in allen DS gleich, also informationslos. Die Kategorie kann ihren Ausdruck im Namen der Abfrage finden oder als Eintrag eines Kombifeldes, über das die Kriterien gesteuert werden.

 

Nur benötigte Datensätze ausgeben

 

Was für Felder gesagt wurde, gilt in noch höherem Maße für die Beschränkung der ausgegebenen Datensätze. Daher

 

• Resultset mit Where einschränken

 

Das insbesondere, wenn berechnete Felder vorkommen. In je weniger Zeilen eine satzabhängige Berechnung durchgeführt wird, desto besser.
Wie man WHERE-Bedingungen ökonomisch aufbaut, wird weiter unten beschrieben.

 

Abfragen speichern

 

Eine gespeicherte Abfrage ist deutlich schneller als SQL-Code, der in VBA-Code dynamisch erzeugt wird; in meinen Tests um bis zu 400%

Man sollte daher annehmen, daß eine gespeicherte Abfrage auch als RecordSource eines gebundenen Formulars vorzuziehen ist. Das ist im Prinzip auch richtig. Daß ein in der Datensatzherkunft eingetragener SQL-String dennoch unkritisch ist, liegt schlicht daran, daß Access hierfür heimlich unsichtbare Systemabfragen erstellt, die noch nicht einmal über Ansicht Ausgeblendete Objekte bzw. Systemobjekte sichtbar werden. Das gleiche gilt für Listen- und Kombifelder.

CurrentDb.QueryDefs oder ein Blick in die Systemtabelle MSysObjects enthüllt aber solche Abfragen, die an ihren Präfixen leicht zu erkennbar sind:

 

~sq_fFormularName bzw.
~sq_cFormularName~sq_cListeName

 

Bei Einsatz älterer Access-Versionen überzeuge man sich, ob solche Systemabfragen bereits angelegt werden. Wenn nicht, verzichtet man auf SQL-Strings als Datenherkunft und speichert sie selbst als Abfragen.

 

Nur Indexfelder für mengenorientierte Operationen

 

Felder, die in einem

 

• JOIN

oder einer der Klauseln

 

• ORDER BY
• WHERE
• GROUP BY

 

Verwendung finden, müssen indiziert sein. Wenn solche Operationen auf nichtindizierten Feldern ausgeführt werden, muß Jet prinzipbedingt zu Sortier- und Join-Algorithmen greifen, deren Laufzeit mit steigender Datenmenge überproportional ansteigt.
Darüber hinaus sind solche Aktionen zu vermeiden auf berechneten Feldern, da diese keine Indexnutzung zulassen.

 

Sortierung

 

Sortierungen sollte man auf das notwendige Mindestmaß beschränken. Wenn eine Ergebnismenge für interne Weiterverarbeitung per Code nicht sortiert sein muß, sollte man darauf ganz verzichten und nicht aus Gewohnheit einfach eine Sortierung festlegen.

Die beste Performance haben Sortierungen auf den wie oben gezeigt herbeigezauberten Clustered Index ohne ORDER BY (500%).

Das ist natürlich nur bei relativ konstanten Daten anwendbar, da neue Datensätze nicht sofort einsortiert werden.

Sortierungen sollen auch ansonsten nur auf Indexfeldern vorgenommen werden, die die betreffende Reihenfolge der Sortierfelder auch unterstützen, wie unter Indexplanung beschrieben wurde.

Viel mehr Gedanken muß sich übers Sortieren nicht machen, da man kaum weitere Gestaltungsmöglichkeiten hat.

 

Selektion

 

WHERE-Klauseln gestalten

 

Unproblematisch sind die folgenden Vergleiche:

 

• =
• <
• <=
• >
• >=
• Like Text*

 

Alle diese Operatoren erlauben die Ausnutzung eines bestehenden Index. Zum Like-Operator ist noch anzumerken, daß der Index bis zum Jokerzeichen, aber nicht darüber hinaus genutzt werden kann. Je weiter hinten dieses auftaucht, desto günstiger ist es also.

 

Problematisch sind:

 

• <>
• Like *Text

 

<> wird als Not = ausgeführt (siehe nächster Punkt), und bei Like *… wird in Fortsetzung des oben zu Like Gesagten ein Index überhaupt nicht mehr genutzt.

Wenn solche Filterungen nach hintenliegenden Feldinhaltsteilen systematisch auftauchen, ist die DB falsch normalisiert, und man sollte sich überlegen, wie man das Feld so aufteilen kann, daß Daten als ganze oder führende Feldinhalte auffindbar sind.

 

 

NOT vermeiden

 

Ein Not (Nicht) in einem Kriterium verhindert zuverlässig die Indexnutzung. Hierzu gehört auch die Verwendung von <>.

Solche Ausdrücke sollte man, wenn irgend möglich, mittels entsprechender logischer Gesetze umformen. Man findet weiter unten Beispiele hierzu, nebst den erzielbaren Laufzeitgewinnen.
Es gibt von dieser Regel aber eine wichtige Ausnahme: NOT in Verbindung mit dem EXISTS-Operator bei Unterabfragen ist unschädlich.

OR und AND verwenden

 

Häufig wird man Kriterien mit den logischen Operatoren Or und And kombinieren. Jet verwendet eine als Rushmore bezeichnete Methode, logische Ausdrücke auszuwerten, die besonders effizient ist.
Damit Jet diese Technologie anwenden kann, müssen die Ausdrücke bestimmte Kriterien erfüllen. Man findet unter dem Stichwort Rushmore in der Online-Hilfe einen Artikel dazu, dessen Inhalt hier kurz zusammengefaßt wird.

 

Ein einfacher Ausdruck hat die Form:

[Feld] Vergleichsoperator Ausdruck

Ein einfacher optimierbarer Ausdruck hat die Form

[IndiziertesFeld] Vergleichsoperator Ausdruck

Also zum Beispiel [Nachname] = 'Müller', wenn Nachname indiziert ist.
Als Operatoren sind zulässig: <, >, =, <=, >=, Between…And, Like, In

 

In der Access-OH wird auch <> in dieser Liste aufgeführt. Das ist falsch. Mit <> wird nicht nur keine Rushmore-Optimierung durchgeführt, sondern der Index gar nicht mehr benutzt.

Ein komplexer Ausdruck ist aus einfachen Ausdrücken mit AND und OR zusammengesetzt. Es ergeben sich für einen zweiwertigen komplexen Ausdruck folgende Kombinationsmöglichkeiten:

 

• Optimierbarer Ausdruck AND/OR Optimierbarer Ausdruck
• Optimierbarer Ausdruck AND/OR Nichtoptimierbarer Ausdruck
• Nichtoptimierbarer Ausdruck AND/OR Nichtoptimierbarer Ausdruck

 

Natürlich kann man durch Verknüpfung komplexer Ausdrücke weitere Ausdrücke noch höherer Komplexität aufbauen.

Für das Optimierbarkeitsverhalten eines komplexen Ausdrucks gibt es drei Stufen:

 

• Vollständig optimierbar
• Teilweise optimierbar
• Nicht optimierbar

Wie sich diese ergeben, kann der folgenden Tabelle entnommen werden.

 

 

Mit Hilfe logischer Gesetze lassen sich Ausdrücke gegebenenfalls so umformen, daß sie optimierbar werden, zum Beispiel

NOT (x<5 OR y >3)  x>=5 AND y<=3

 

Der erste Ausdruck ist trotz angenommenem Index auf x und y nicht optimierbar, der zweite Ausdruck ist sogar vollständig optimierbar.
Für solche Umformungen gibt es Heerscharen logischer Theoreme, die praktisch wichtigsten, die jeder Programmierer im Schlaf beherrschen sollte, sind

 

• Distributivgesetz der Konjunktion und Disjunktion
  A OR (B AND C) = (A OR B) AND (A OR C)
  A AND (B OR C) = (A AND B) OR (A AND C)
• De Morgansche Gesetze
  NOT (A OR B) = NOT A AND NOT B
  NOT (A AND B) = NOT A OR NOT B
• Gesetz von der Doppelnegation
  NOT NOT A = A

 

Kriterienreihenfolge

 

Man hört gelegentlich den durchaus plausiblen Tip, man solle die Reihenfolge einschränkender Kriterien so festlegen, daß die größten Einschränkungen zuerst kommen. Das ist im Prinzip richtig, aber dennoch überflüssig.
Zum einen hängt die einschränkende Wirkung von Kriterien vom Datenbestand ab, ist also dem Entwickler im Zweifel nicht bekannt.
Da zum anderen das DB-System interne Statistiken führt, kann es die optimale Kriterienreihenfolge sehr wohl beurteilen und legt diese unabhängig von der Reihenfolge in der Abfrage auch zugrunde.
Man muß sich hierüber also keinen Kopf machen und kann das Denken in diesem Falle Jet überlassen.

 

OR oder IN

 

Auch die allgemeine Empfehlung, IN statt OR zu verwenden, ist obsolet, da Jet auch hier unabhängig vom SQL-Code selbständig die bessere Variante mit IN einsetzt.

 

Vermeidung von OR durch UNION

 

Gelegentlich kann man über diesen Tip stolpern, allerdings im Zusammenhang mit anderen DB-Systemen. Es ist keine gute Idee, dieses Vorgehen für Jet zu adaptieren. Im Gegensatz zu den Systemen, die diesen Tip rechtfertigen, ist Jet – Rushmore sei Dank – durchaus in der Lage, OR-Ausdrücke mit Index zu nutzen und zu optimieren.
Eine Abfrage mit OR ist deutlich schneller als eine ergebnisäquivalente Union-Abfrage.

 

BETWEEN … AND oder AND

BETWEEN 1 AND 5 ist (minimal) besser als >=1 AND <=5

WHERE auf berechneten Feldern vermeiden

Bereits einfachste Rechnungen verhindern Indexnutzung. Man kann sich diesen Effekt zum Testen zunutze machen und durch Zahlfeld + 0 oder Textfeld & '' ausprobieren, wie sich ein indiziertes Feld ohne Index verhalten würde.
Als Abhilfe kann man nur versuchen, die Bedingung auf die indizierten(!) Felder, die in die Berechnung eingehen, umzuformen.
Das geht grundsätzlich bei eindeutig umkehrbaren Funktionen.
Gegebenenfalls muß man auch das Tabellendesign entsprechend anpassen.
Angenommen, eine Tabelle enthält Maße von Quadern, deren Länge, Breite, Höhe und Volumen verwaltet werden soll. Zusätzlich ist die Anforderung bekannt, daß das Volumen in WHERE-Klauseln verwendet werden soll, aber nicht Länge, Breite und Höhe.
Statt in der Tabelle (l, b, h) zu speichern und V in einer Abfrage zu berechnen, würde man in der Tabelle zum Beispiel (V, l, b) speichern und dann h in einer Abfrage berechnen.
Bei nicht eindeutig umkehrbaren Funktionen kann es helfen, sie in Monotonie-Intervalle zu zerlegen und daraus eigene Bedingungen zu formen.
Angenommen, es werden die Datensätze gesucht, bei denen das Quadrat eines Feldes x größer als 4 ist. Zunächst ist die Funktion x² nicht eindeutig umkehrbar, aber sie läßt sich in die Monotonieintervalle  oo bis 0 und 0 bis oo zerlegen.
Also statt (x * x) > 4 besser x < -2 Or x > 2, womit wir uns, von verhinderter Indexnutzung ausgehend, so erheblich verbessern, daß sogar eine Rushmore-Optimierung möglich wird.

 

WHERE mit Formularbezug

 

Der Bezug des Kriterienwertes aus einem Steuerelement in einem Formular ist unproblematisch und in der Regel auch sowieso aus ergonomischen Gründen unumgänglich.

 

Beispiele zur Optimierung

 

Schlecht
Gut
Alle Beispiele basieren auf Verwendung indizierter Felder.

Prüfung auf NULL
IsNull([Feld])
[Feld] Is Null
Geschwindigkeitsvorteil ca. 1500%

Prüfung auf Nicht NULL
NOT Is Null
Bei Text
>= '' (Achtung! Kein Leerzeichen zwischen '')
Bei Zahlen
<0 OR >=0
Geschwindigkeitsvorteil ca.  700%

Prüfung auf ungleich Zahl
x<>5 (NOT x=5)
x<5 OR x>5
Geschwindigkeitsvorteil ca.  600%

Prüfung auf Jahr
Year([Datum]) = 2004
#1/1/2004# <= [Datum] AND [Datum] <= #12/31/2004#
Oder
[Datum] BETWEEN #1/1/2004# AND #12/31/2004#
Geschwindigkeitsvorteil ca. 2700%

Prüfung auf 0, Positiv, Negativ bei berechneten Feldern
Felder a, b, x: a * b
x=0
      x>0
      x<0
a=0 OR b=0
      (a>0 AND b>0) OR (a<0 AND b<0)
      (a>0 AND b<0) OR (a<0 AND b>0)
Geschwindigkeitsvorteil ca. 700%

Prüfung auf nicht in Menge

NOT IN (3, 4, 5)
NOT IN (3, 4, 5)  NOT (x=3 OR x=4 OR x=5)
(Auflösen von IN)
NOT (x=3 OR x=4 OR x=5)  NOT x=3 AND NOT x=4 AND NOT x=5
(De Morgan)
NOT x=3 AND NOT x=4 AND NOT x=5  x<>3 AND x<>4 AND x<>5
(Auflösen von Nicht gleich in Ungleich)
x<>3 AND x<>4 AND x<>5  (x<3 OR x>3) AND (x<4 OR x>4) AND (x<5 OR x>5)
(Auflösen von Ungleich in Größer oder Kleiner)
(x<3 OR x>3) AND (x<4 OR x>4) AND (x<5 OR x>5)
Gewinn ca. 500%
Ein weiteres Beispiel zum Ersetzen von NOT IN, bei dem die Kriterienmenge ein Resultset ist, im Kapitel Unterabfragen.

Prüfung auf nicht in Bereich
NOT BETWEEN 3 AND 12

NOT BETWEEN 3 AND 12
→NOT (x>=3 AND x<= 12)
→NOT x>=3 OR NOT x<=12
→x<3 OR x>12

x<3 OR x>12
Gewinn ca. 500%

Wertprüfung auf Eingangsvariable verschieben
Felder x, y mit y: x * 0,12 + 24
→x= (y - 24) / 12 * 100
y > 100
x > 633
Gewinn ca. 3700%

Redundante Bedingungen ausklammern
(A=3 AND B = 2 AND C = 5) OR (A=3 AND B = 2 AND C = 12)
A=3 AND B = 2 AND (C=5 OR C=12)
Die erste Zeile entsteht recht gerne, wenn Bedingungen mit dem graphischen Abfragedesigner erstellt werden.

Gewinn ca. 20%, A, B, C alle indiziert.

Wenn Nichtindexfelder und Indexfelder gemischt vorliegen, kann die Umstellung dazu führen, daß statt gar keiner Indexnutzung im besten Fall sogar auf Rushmore umgestellt werden kann. Der Vorteil kann also durchaus größer werden.

 

Prüfung auf Textanfang
Left(Feld,1) = 'M'
Feld Like 'M*'
Feld >= 'M' And Feld < 'N'
Gewinn ca. 18900% Version 3 gegen Version 1; 800% Version 3 gegen Version 2

 

 

Kalkulation

 

Das Thema hat mittlerweile weitaus weniger Bedeutung, als in älteren Access-Versionen. Der Jet-Expression-Service ist im Laufe der Zeit spürbar verbessert worden. Dennoch sollen einige Tips zur Optimierung von Rechenausdrücken gegeben werden, wiewohl sie in modernen Versionen von geringerem Interesse sind.

 

Berechnete Felder

Text vs. Zahl

Gängige Datentypen in absteigender Reihenfolge

• Ganzzahl
• Fließkommazahl/Datum
• Text

Gängige Operatoren in absteigender Reihenfolge

• + - * \ Mod mit (Ganz-)Zahlen
• / und &,sowie + mit Text, Iif (neue Versionen)
• Nz, Iif (alte Versionen), vom Expression Service verfügbar gemachte Funktionen, wie Left, Mid, Right, Year, Month
• Selbstgeschriebene VBA-Funktionen

 

Ausdrücke umformen

Nachfolgend einige Beispiele, die vor allem in älteren Versionen nützlich sein können. Man prüfe im Einzelfall, was unter den gegebenen Voraussetzungen schneller ist.

Durch eine Anzahl teilen, die auch 0 sein könnte. Dann soll als Rückgabe der NULL-Wert erfolgen:
Wenn(x=0; Null; y/x)
y/(x Or Null)
Diesen Ausdruck nicht verwenden, wenn x auch Werte -1<x<0 Und 0<x<1 annehmen kann.

Negative Zahlen sollen auf 0 gesetzt werden:
Wenn(x<0;0;x)
(x>0) * -x

Negative Zahlen sollen auf  NULL gesetzt werden:
Wenn(x<0;Null;x)
((x>0) oder Null) * -x

Der Wert 1 soll eine 7, der Wert 2 eine 12 ergeben (z. B. Rabattkategorien):
Wenn (x=1;7;Wenn(x=2;12;0))
besser Linearkombination mit Wahrheitswerten:
-(x=1)*7-(x=2)*12
oder, wenn sichergestellt ist, daß x ausschließlich 1 oder 2 sein kann, Polynom:
5*x+2.
Falls der Definitionsbereich  für x auf {0, 1, 2} liegt, dann
-x*(x-8)

Man kann n beliebige Bedingungen immer als n-dimensionale Linearkombination ausdrücken und n Bedingungen vom Typ "=" und abgeschlossener Definitionsmenge als Polynom schlimmstenfalls (n-1)ten Grades.

Leerzeichen automatisch ausfüllen:

Wenn(IstNull(Titel);"";Titel & " ") & Vorname & " " & Nachname
(Titel + " ") & Vorname & " " & Nachname)

In der Verkettung mit "&" verhält sich der Nullwert als neutrales, in der Verkettung mit "+" als dominantes Element

Das Geschlecht ist mit 1 (männlich) und 2 (weiblich) codiert. Daraus sollen die Buchstaben "m" bzw. "w" erzeugt werden:

Wenn(x=1;'m';'w')
Chr(10*x+99)

Das Geschlecht ist mit m und w codiert. Daraus sollen die Kennzahlen 1 bzw. 2 erzeugt werden:

Wenn(x='m';1;2)
(Asc(x)-99)/10

NULL-Wert in Stringfeld in Leere Zeichenfolge konvertieren

Nz(Feld;'')
'' & Feld

Potenzen in Multiplikation auflösen

x^2
x * x

Exp und Log wenn möglich vermeiden

Exp(2 * Log(x))
x * x

Alternative: Berechnung im Formular
Rechenausdrücke in Formularen werden zwar nicht schneller berechnet als in Abfragen, aber wenn die Abfrage als Basis eines Endlosformulars dient, ist der Bildaufbau bei Formularberechnungen erheblich besser.

Zusammenfassung

Die benötigte Zeit für Berechnungen mit Feldwerten innerhalb eines Datensatzes ist bei aktuellen Access-Versionen in der Regel unkritisch. Man kann weitaus kapitalere Böcke schießen, zum Beispiel eine Bedingung in ein solches Feld setzen.

 

 

Domänenfunktionen

 

Statt der Verwendung von Domänenfunktion (DomWert, DomSumme, etc.) sollte man die bezogene Tabelle mittels eines Joins verknüpfen und gegebenenfalls passende SQL-Aggregat-Funktionen einsetzen oder eine Unterabfrage konstruieren. Domänenfunktionen sind aus verschiedenen Gründen normalerweise die langsamste aller Möglichkeiten ein Ergebnis zu erzielen.

 

 

Verbund

 

JOINS gestalten
Bei vielen DB-Systemen liegt hier einiges Optimierungspotential. Bei modernen Jet-Versionen wird jedoch erstaunlich viel vom System automatisch optimiert.

 

Der grundlegende Ratschlag, einen Join nicht klassisch über WHERE, sondern mit einem echten JOIN aufzubauen, ist für Jet-Abfragen irrelevant.

Klassischer Inner Join

 

 

Expliziter Inner Join

 

 

Beide Syntaxvarianten werden von Jet in der optimalen Form mit INNER JOIN ausgeführt!
Das soll kein Plädoyer für die alte Syntax (erstes Beispiel) sein, zeigt aber, wie weit die Optimierung durch Jet geht.

 

 

JOINS mit Bedingungen in einem Datenfeld

 

Prinzipiell bestünden die zwei Möglichkeiten, Zusatzbedingungen in der Join-Bedingung oder in einer WHERE-Klausel unterzubringen:

 

 

versus

 

 

oder ganz klassisch

 

 

Tatsächlich werden alle drei Varianten genau gleich ausgeführt:

 

 

Es sei noch einmal daran erinnert, daß das Feld A.Krit natürlich indiziert sein muß, um das Ergebnis „using rushmore“ oder wenigstens „using index“  zu erzielen.

Das optimale Vorgehen, erst die einschränkenden Bedingungen je Tabelle auszuwerten, und dann die übrigen Datensätze zu verknüpfen, wird von Jet in jedem Fall, unabhängig von der Formulierung der Bedingung, durchgeführt.

 

 

JOINS mit Bedingungen im Verknüpfungsfeld

 

Optimierungspotential gibt es hingegen beim Join mit Kriterien im Verknüpfungsfeld über eine 1:n Beziehung; wahrscheinlich der häufigste Fall in einer Datenbank.

 

 

oder

 

 

Beide Abfragen liefern das gleiche Ergebnis, die zweite, mit der Einschränkung auf der 1-Seite, tut das aber 100 bis 200 % schneller.
Die Ausführungspläne beider Abfragen unterscheiden sich auch durchaus.

 

 

Die Ex-post-facto-Rationalisierung fällt in Kenntnis der Ergebnisse leicht: auf der 1-Seite kommt ein bestimmter Wert natürlich seltener (einmal) vor, als auf der n-Seite, wodurch die Beschränkung effektiver ist.

 

 

Tabellenreihenfolge

Hier ergab sich ein etwas uneinheitliches Bild.
1) (A x B) x C

 

 

2) (A x C) x B

 

 

Beide Abfragen liefern dieselbe Ergebnismenge und sehen im graphischen Entwurf genau gleich aus.

Bei einigen Fällen wurden die Abfragevarianten von Access im SQL-Code auf die gleiche Syntax umgeschrieben, bei anderen blieb die unterschiedliche Klammersetzung erhalten, aber die Ausführungspläne waren identisch und dann gab es auch noch das Ergebnis, daß die Ausführungspläne bei beiden Abfragen verschieden waren und die Laufzeit ebenfalls.

Wann welches Verhalten auftritt, scheint in erster Linie damit zusammenzuhängen, ob zwischen den im Join verwendeten Feldern Beziehungen mit referentieller Integrität bestehen.

Eine Änderung der Klammerung, also der Reihenfolge der Joins, ist im graphischen Entwurf nicht möglich, sondern muß im SQL-Code erfolgen.

Als Faustregel für die Reihenfolge der Joins gilt, daß man den Join mit der größeren Selektivität, also den, der die kleinere Ergebnismenge liefert, nach innen klammert, und damit zuerst ausführen läßt.

Das sind – Vorsicht, Falle! – keineswegs unbedingt die Tabellen mit den wenigsten Datensätzen, sondern die Tabellenpaare mit den wenigsten Übereinstimmungen im Verknüpfungsfeld.

Das ohne empirischen Test einzuschätzen fällt nicht leicht, da die Selektivität von der Datenverteilung abhängt. Man prüfe also die beiden Abfragen, die sich aufgrund der mittleren Tabelle ergeben, auf die Anzahl der zurückgegebenen Datensätze.

 

Im Beispiel wären das A x B und A x C, da A mit beiden anderen Tabellen verknüpft wird.

Man beachte, daß Jet beim Vorkommen von Outer Joins unter Umständen nicht alle Reihenfolgen zuläßt.

Wenn alle Joins über Beziehungen mit referentieller Integrität laufen, scheint man sich darauf, daß Jet selbst die optimale Join-Reihenfolge findet, verlassen zu können. Ein Eingriff in den SQL-Code, um die Reihenfolge zu beeinflussen, ist dann obsolet.
Man sollte sicherheitshalber die Ausgabe des Showplan zu Rate ziehen, damit man weiß, was passiert, und nicht auf Vermutungen angewiesen ist.

 

Vergleichsoperatoren

Wenn möglich soll man Equi Joins verwenden, also solche mit = im Verknüpfungsausdruck.
Theta Joins, also Vergleiche wie

• A.id < B.id
• A.id <= B.id
• A.id = B.id *2

führen zu einem Cross Join ohne Indexnutzung.

 

INNER und OUTER Joins

Outer Joins sind etwas langsamer als Inner Joins. Da sie aber nicht durcheinander ersetzbar sind, wird man den Outer Join in Kauf nehmen, wenn eine Anfrage ihn erfordert. Immerhin ist der Outer Join zur Indexnutzung in der Lage; er rutscht also keinesfalls in die Liga des Cross Join ab.
Falls man jedoch im Einzelfall einen Outer Join benutzt hat, der aufgrund der Datenlage (Eingabepflicht) kein anderes Ergebnis als ein Inner Join liefern kann, sollte man ihn dadurch ersetzen.

 

JOIN oder Unterabfrage

Die Legende besagt, daß Unterabfragen oft sehr langsam seien und ein Join auf jeden Fall die bessere Wahl. In der Tat trifft es zu, daß es häufig Fälle gibt, in denen beim Vergleich Join vs. Subquery der Join erheblich besser abschneidet. Es gibt allerdings auch Konstellationen, in denen das Gegenteil richtig ist.
Weiter unten wird der Klärung der Frage, wann eine Subquery und wann ein Join Vorteile hat, in einem eigenen Kapitel nachgegangen.

 

Gruppierung

Indexfelder benutzen
Repetitiones non placent – Wiederholungen gefallen nicht, sagt der Römer. Er sagt aber auch: Qui nocent, docent – Leiden sind Lehren.
Also zum wiederholten Male: Gruppierungen sollte man nur auf indizierten Feldern vornehmen.

 

WHERE statt HAVING
Mit Where werden Kriterien vor der Gruppierung angewandt, mit Having danach. Rein logisch macht Having also nur Sinn, wenn es auf einem Feld benutzt wird, das erst im Laufe einer Gruppierung durch Aggregation entsteht.

Mit anderen DB-Systemen und auch in älteren Access-Versionen sollte man das beherzigen, in neueren denkt Jet mit. Eine unsinnige Having-Klausel wird bei der Ausführung einfach zu Where übersetzt.

Man kann das Verhalten mit dem ShowPlan überprüfen und seine Abfragen entsprechend gestalten.
Normalerweise kommt Having auf Nichtaggregatfeldern auch nicht absichtlich zustande, sondern durch eine Eigenheit des graphischen Abfragedesigners:

Ein Eintrag in der Kriterienzeile führt bei nichtgruppierten Abfragen zu einer Where-Klausel, bei gruppierten unsinnigerweise aber zu einer Having-Klausel. Die Where-Klausel ist hier hinter der „Funktion“ Bedingung versteckt.

 

Folgegruppen vermeiden

Angenommen, man nimmt eine Gruppierung nach Kundennummern vor, um Umsatzsummen aus Aufträgen oder ähnliches zu berechnen.

 

 

Graphisch:

 

 

Nun soll aber noch der Name des Kunden angezeigt werden:

 

 

Graphisch:

 

 

Das dürfte der meist eingeschlagene Weg sein. Da der Name aber anhand der Kundennummer schon eindeutig feststeht, ist eine Folgegruppierung danach nicht nur sinnlos, sondern sogar schädlich, da Gruppierungen einigen Rechenaufwand verursachen.

Besser ist

 

 

Graphisch:

 

 

First gibt einfach nur den zufällig ersten Wert aus. Da nach der Gruppierung zum Beispiel nach idKd = 3 alle folgenden Namenwerte ‚Donaubauer’ lauten, spielt es überhaupt keine Rolle, welcher davon genommen wird.

Der erste ist gerade recht, weil er einfach am Anfang steht und nicht gesucht werden muß.
Min gibt den kleinsten Wert aus, bei lauter identischen Werten spielt das also auch keine Rolle.

Man hat in dieser Konstellation also die Qual der Wahl. Das ist nicht zu verallgemeinern – normalerweise haben First und Min unterschiedliche Auswirkungen, aber wenn alle zur Auswahl stehenden Werte durch die Vorausgruppierung sowieso gleich sind, tun sie dasselbe.

Welches soll man also nehmen?

Wenn das Folgefeld, hier Name, nicht indiziert ist, ist normalerweise First schneller, wenn es indiziert ist, ist Min schneller.

Der Gewinn von First/Min gegenüber Group By beträgt bei wenigen Daten auf einem Folgefeld etwa 30%, bei großen Datenmengen und mehreren Folgefeldern kann da einiges zusammenkommen.

Ob First oder Min im Einzelfall schneller ist, sollte man am besten ausprobieren.

 

Group By im Join

 

Das Ergebnis von eben kann man noch einmal deutlich verbessern, wenn man die Gruppierung auf der richtigen Seite der Verknüpfung anlegt. Also nicht wie oben

 

 

sondern

 

 

Wenn man die Gruppierung statt auf  der 1-Seite auf der n-Seite vornimmt, bringt das ab 40% - 60% aufwärts.

 

Umgang mit Aggregatfunktionen

 

Count(*)
Abweichend von der Grundregel ganz zu Anfang „* vermeiden“, gilt bei der Aggregatfunktion Anzahl (Count), daß Count(*) besser als Count([Feld]) ist, da die Count(*)-Syntax speziell für Rushmore optimiert wurde.

„Echte“ Aggregate
… wie Summe, Durchschnitt sowie First/Last profitieren nicht vom Index. Für eine Summe beispielsweise ist ein Index belanglos, da in jedem Fall alle Werte addiert werden müssen, wobei deren Reihenfolge, Kommutativgesetz sei Dank, keine Rolle spielt. Felder, die also nur für solche Aggregate benötigt werden, muß man deswegen nicht indizieren.

„Unechte“ Aggregate
 … wie Min, Max, Count profitieren enorm. In einem indizierten Feld reduziert sich der zur Minimumsbestimmung nötige Algorithmus beispielsweise auf das schlichte Lesen des ersten Eintrags. Solche Felder sollen also indiziert werden.

Eindeutige Datensätze
Um aus einem Feld mit Wertwiederholungen eindeutige Datensätze zu bekommen, bieten sich zwei Vorgehensweisen an:

 

 

oder

 

 

Der Vorteil von DISTINCT gegenüber GROUP BY liegt hier bei 1400%

 

 

UNION-Abfragen

 

Durch E-R-Modell vermeiden
Rund drei Viertel aller Union-Abfragen entspringen erfahrungsgemäß einem falschen Datenmodell. Oft gesehene Anforderungen sind zum Beispiel:

Man möchte für eine Telephonliste Felder aus einer Kundentabelle und einer Lieferantentabelle untereinander bringen oder Adressen aus einer Tabelle Dozenten und einer Tabelle Studenten für ein Rundschreiben o. ä.

Ein richtiges Modell würde beispielsweise eine Tabelle Firmen führen, die gemeinsame Datenfelder von Kunden und Lieferanten enthält, und die kunden- und lieferantenspezifischen Daten in Spezialisierungstabellen mittels je einer 1-1/0-Beziehung anhängen.

Das, was im fehlerhaften Modell durch die Union-Abfrage erreicht werden soll, liegt im richtigen Modell schon als Tabelle vor.

Natürlich ist ein sauberes Modell noch etwas komplexer, da Kunden auch Personen sein können. Da das hier nicht unser Thema ist, sei auf mein Skript zur Datenbanknormalisierung verwiesen, das den korrekten Tabellenaufbau beschreibt.

Als Geschwindigkeitsvorteile durch diese Maßnahme konnten Gewinne von 1200% - 1500% gemessen werden.

 

UNION ALL
Bei den Union-Abfragen, die auch in einem ordentlichen Datenmodell auftauchen, sollte man statt Union Select besser Union All Select benutzen.

Dazu muß man wissen, daß in der einfachen Union ein Distinct schon eingebaut ist; mehrfache gleiche Datensätze werden also aus dem Ergebnis eliminiert. Wenn das Auftreten von Dubletten nicht stört oder wegen der Datenlage solche sowieso nicht auftauchen, kann man durch Union All diese Automatik außer Kraft setzen.

Der Geschwindigkeitsvorteil hierdurch bewegt sich in der Größenordnung 80%

 

Unterabfragen

 

Funktionsweise
Eigenständige Query als

• Vergleich in WHERE-Klausel
• Herkunft eines einzelnen Feldes, wenn eindeutig
• Datenherkunft im FROM (Derived Table, eigentlich keine UA im engeren Sinn)

 

 

Vor- und Nachteile
Die größte Hürde ist zweifellos, daß von graphischen Query-Designern Verwöhnte textorientiert SQL schreiben müssen. Hinzu kommen die für Unterabfragen spezifischen Syntaxbestandteile wie EXISTS und die Mengenprädikate.

Wenn man dieses rein organisatorische Problem dadurch gelöst hat, daß man endlich einmal den guten Vorsatz zum Jahreswechsel, die komplette SQL-Syntax auswendig zu lernen, eingelöst hat, steht einem sinnvollen Einsatz wenig im Wege.

Man unterscheidet:

• Korrelierte UA (mit Bezug auf Hauptabfrage) - Diese werden n-mal ausgeführt  Alle Performance-Regeln in der UA beachten!
• Unkorrelierte UA (ohne Bezug auf Hauptabfrage) - Diese werden nur einmal ausgeführt und stellen ihr Ergebnis allen DS der Hauptabfrage zur Verfügung

Manche Anfragen sind nur mit Unterabfragen lösbar.

 

 

Umformung in JOINS

 

Korrelierte Unterabfragen sind in der Regel auch als Join formulierbar, was oft auch die schnellere Alternative ist. Man sollte das jedoch nicht ungeprüft nachbeten, es gibt auch genug Fälle, in denen es anders herum ist. Man vergewissere sich im Einzelfall, welche Lösung am günstigsten ist.

Kriterium in Fremdtabelle
Eine der typischen Anwendungen für Subqueries ist das  Filtern eines Resultsets nach einem Kriterium, das aus einer anderen Tabelle bezogen wird.

 

Beispiel:

• Vorgang (idVrg, VrgBezeichnung, fiPrs)
• Personal (idPrs, PrsName,fiAbt)
• Abteilung (idAbt, AbtName)

mit Vorgang n:1 Personal n:1 Abteilung

Anfrage: Suche alle Vorgänge, die zur Abteilung 3 gehören. Die Information ist als Fremdschlüssel fiAbt in Personal enthalten, allgemein „Filtere Daten aus Tabelle A nach einem Kriterium in einer Tabelle B, die mit A verknüpft ist“

Mit Unterabfrage in der WHERE-Klausel und IN:

 

 

Mit Unterabfrage in der WHERE-Klausel und Mengenprädikat:

 

 

Mit Unterabfrage mit EXISTS:

 

 

Diese typischen Subqueries lassen sich als Join so formulieren:

 

 

Die Join-Variante ist um etwa 200% schneller als die Subqueries.

An dieser Stelle sei auch noch einmal an den alternativen Schlüsselaufbau erinnert, bei dem Detailtabellen alle Schlüsselinformationen aus allen übergeordneten Tabellen erben. Damit wäre die Anfrage ohne jeden Join und Subquery lösbar, da die Abteilungsnummer als Schlüsselteil sowieso Bestandteil der Vorgangstabelle wäre.

 

Größter/Kleinster
Ein weiteres typische Anwendung für eine Subquery: Suche den Datensatz, der im Kriterienfeld den größten (kleinsten) Wert hat.

Beispiel:

• Bestellung (idBst, BstDatum, fiKd, …)

Anfrage: Suche die neuesten Bestellungen

Subquery mit Mengenprädikat:

 

BILD Seite 53

 

Um diese Anfrage ohne Subquery auszudrücken, kann man sich des TOP-Prädikates bedienen:

 

BILD Seite 53

 

Man beachte, daß TOP 1 keineswegs sicher nur einen DS liefert. Wenn im Beispiel mehrere Bestellungen am letzten Bestelldatum vorgenommen wurden, werden bei der Feldauswahl im Beispiel auch alle diese DS ausgegeben.

Wenn TOP 1 auf einem nicht eindeutigen Feld benutzt werden sollte, um das Maximum als einen Rückgabewert zu ermitteln, wäre ein SELECT DISTINCT TOP 1 erforderlich.

Im vorliegenden Beispiel ist es allerdings gerade erwünscht, daß mehrere DS erscheinen, wenn mehrere den gleichen Höchstwert aufweisen. Die Subqueries verhalten sich genauso.
Subquery mit Aggregat

 

BILD Seite 54

 

Hier ist die Subquery-Lösung mit >= ALL deutlich am langsamsten, gefolgt von der TOP1-Lösung. Die Subquery mit Aggregatfunktion ist aber der ungeschlagene Sieger.

Diese letzte Abfrage ist um 20.600% besser als TOP1 und um 128.000% besser als die Abfrage mit Mengenprädikat.

Nun noch zwei generische Beispiele, in denen das Pendel ganz erheblich zugunsten der Unterabfrage ausschlägt:

1:n Master mit Detail („Kunden mit Bestellung“)
Inner Join mit Distinct

 

BILD Seite 54

 

Exists mit Subquery

 

BILD Seite 54

 

Die Subquery zeigte sich beim Testen um 2.300% bis zu 12.700% (!) schneller. Zudem ist sie im Gegensatz zu Select Distinct bearbeitbar.

1:n Master ohne Detail („Kunden ohne Bestellung“)
Outer Join mit Where Is Null

 

BILD Seite 54

 

Not (!) Exists mit Subquery

 

 

Vorteil der zweiten Lösung im Test 1.500% bis 17.000% (!!)
Das kann auch als Ersatz dienen, um eine Unterabfrage mit NOT IN zu vermeiden, wie

 

 

Man kann zwar durchaus allgemeine Aussagen treffen, in welchen Konstellationen Lösungen mit oder ohne Subquery vorzuziehen sind. Die betreffenden Regeln sind aber so komplex und mit Ausnahmen, zu deren Auftreten es natürlich auch wieder komplexe Regeln gibt, durchsetzt, daß es einerseits den Rahmen dieses Skriptes sprengt und andererseits schlichtes Testen der Alternativen schneller zum Ziel führt.

Daher nur ein paar unverbindliche Faustregeln, deren Zutreffen man im Einzelfall testen möge.

1) Vergleiche auf

 

 

sind eher ungünstig, da mit allen Ergebnissen verglichen werden muß.

Ebenso

 

 

2) Vergleiche auf

 

 

sind günstiger, da nach dem ersten Treffer abgebrochen werden kann. Vergleiche mit

 

 

verhalten sich ähnlich

3) Vergleiche auf

 

 

sind günstig, da nur auf einen Wert verglichen wird, der über einem Indexfeld mit den Aggregatfunktionen Min/Max obendrein schnell gefunden wird.

4) Vergleiche mittels

 

 

sind meist extrem schnell; letzterer sogar trotz NOT.

 

© 1989-2013 Erstellt mit dem szCMS Version 2.1