DFirst/DLast und der Mythos der sortierten Ergebnismenge

von Philipp Stiefel, ursprünglich veröffentlicht 02. Juni 2018

zuletzt aktualisiert 07. Juli 2018

Foto von Alex Holyoake auf Unsplash

Es gibt zwei eng verwandte Fragen, die mir häufig in verschiedenen Datenbank-Foren begegnen.

„Warum sind die Ergebnisse meiner Abfrage nicht so sortiert, wie ich das erwartet habe?“

… und …

„Warum liefert die DFirst (oder DLast) Funktion nicht den Wert, den ich erwartet hätte?“

Die erste Frage ist elementar, um das Kernproblem zu verstehen, daher konzentrieren wir uns zuerst darauf.

Die Reihenfolge von Abfrageergebnissen

Warum sind deine Abfrageergebnisse nicht in der Reihenfolge sortiert, die du erwartet hast?

Löse dich von deiner Vorstellung der sequenziellen Datensätze. Relationale Datenbanken arbeiten mit Datenmengen. Sie speichern und verarbeiten Datensätze nicht in einer bestimmten Reihenfolge. - Natürlich tun sie das, aber zu deiner eigenen Sicherheit, geh bitte davon aus, dass sie das nicht tun. - Die Datenbankengine wird die Datensätze in irgendeiner Reihenfolge liefern, die ihr geeignet erscheint, um Ergebnisse so effizient wie möglich zu produzieren.

Du solltest niemals irgendeine bestimme Datensatzreihenfolge erwarten.

Wenn du dein Ergebnis in einer bestimmten Art und Weise sortiert benötigst, dann musst du die Datenbankengine explizit anweisen, die Daten in dieser Reihenfolge zu liefern.

Das kannst du einfach mit einer ORDER BY Klausel in deinem SQL Statement erreichen. Die Syntax ist sehr einfach. Du fügst ORDER BY, gefolgt von einer kommagetrennten Liste der Spalten nach denen du deine Ergebnisse sortiert haben möchtest, an. Standardmäßig wird jedes Feld aufsteigend (ASC) sortiert. Du kannst das DESC Schlüsselwort ergänzen, um nach einem Feld absteigend zu sortieren.

Und dies sollte eigentlich schon das Ende der Diskussion sein. Punkt!

Allerdings klinkt sich an dieser Stelle recht oft irgendjemand anders mit einer geistreichen Anmerkung ein.

„Die Datensätze sind physikalisch in der Reihenfolge ihres Primärschlüssels gespeichert und die Ergebnisse werden automatisch in dieser Reihenfolge ausgegeben. Wenn du deine Datensätze nach ihrem streng ansteigenden Autowert-Primärschlüssel sortiert haben willst, dann kannst du dich bereits darauf verlassen und brauchst das ORDER BY nicht hinzufügen.“

Dies ist auf mehreren Ebenen falsch!

In vielen Datenbanksystemen, wie z.B. Microsoft SQL-Server oder Oracle, sind die Datensätze nur nach dem Primärschlüssel sortiert gespeichert, wenn beim Erstellen der Tabelle nur die Standardeinstellen verwendet werden. Du kannst aber Einstellungen und Optionen für die Tabelle aktivieren, die dafür sorgen, dass die physische Reihenfolge der Datensätze eine andere ist.

Bei Microsoft Access sind die Datensätze in einer Tabelle tatsächlich in der Reihenfolge ihres Primärschlüssels gespeichert. - Dies aber erst nachdem die Datenbank durch Komprimieren und Reparieren reorganisiert wurde. Vorher wurden neue Datensätze einfach nur an das Ende des für die jeweilige Tabelle vorgesehenen Speicherbereichs angefügt. - Für einen streng ansteigenden Autowert-Primärschlüssel macht das keinen Unterschied, aber das kann bei anderen Arten von Primärschlüsseln durchaus anders sein.

Aber selbst mit einem Autowert-Primärschlüssel, kannst du dich nicht darauf verlassen, dass die Ergebnisse einer Abfrage in der Reihenfolge des Primärschlüssels ausgegeben werden.

In der Vergangenheit war meine Antwort auf die obige Anmerkung etwa wie folgt.

Du solltest und kannst dich nicht auf (undokumentierte) Interna der Datenbank für die richtige Darstellung der Ergebnisse verlassen. Sie könnte intern evtl. doch anders arbeiten als du denkst, oder dieses Verhalten wird in einer zukünftigen Version ohne Ankündigung einfach geändert. Wenn du eine bestimmte Reihenfolge deiner Datensätze in der Ergebnismenge benötigst, musst du diese explizit über die ORDER BY Klausel definieren.

Ich wusste zwar einfach aus Erfahrung, dass dies so richtig ist, aber ich hatte keinen reproduzierbaren Beispielcode, um die Unrichtigkeit der Anmerkung zu beweisen. Jetzt, dank dem Blog-Post The Importance of ORDER BY, von Matthew McGiffen, konnte ich ein Szenario erstellen das beweist, dass du dich nicht darauf verlassen kannst, dass die Datensätze immer in der Reihenfolge des Primärschlüssels ausgegeben werden.

Nimm an wir haben die Tabelle tblPerson mit den Spalten Id (Primärschlüssel), Lastname, Firstname und DateOfBirth.

Hier sind die Beispieldaten in dieser Tabelle.

Wenn wir jetzt Daten aus dieser Tabelle abfragen, ohne ein explizites ORDER BY anzugeben, werden diese in der Reihenfolge der ID, des Primärschlüssels, ausgegeben. Das stimmt auch dann noch, wenn wir die Daten filtern. Zum Beispiel, um nur die Daten von Personen zu sehen, die an oder nach dem 01.01.2000 geboren wurden.

Beachte, es befindet sich kein ORDER BY in der Abfrage.

Bis jetzt scheint dies eher die Annahme zu belegen, dass die Daten nach dem Primärschlüssel sortiert sind. Wenn du allerdings diese Abfrage häufig ausführst und eine große Anzahl an Datensätzen in der Tabelle gespeichert ist, dann ist es eine sehr vernünftige Idee, einen Index auf dem DateOfBirth Feld anzulegen. Das kannst du schnell erledigen, indem du die Indiziert-Eigenschaft des Feldes auf „Ja (Duplikate möglich)“ einstellst.

Das ist eine kleine, unscheinbare Änderung, nicht wahr? Sie sollte die Ausführungsgeschwindigkeit der Abfrage verbessern, aber die Ergebnisse der Abfrage nicht verändern. - Oder doch?

Lass uns die Abfrage nochmal ausführen und die Ergebnisse anschauen.

Was ist hier passiert? Die Ergebnisse sind nicht mehr nach dem Primärschlüssel sortiert. Sie sind jetzt nach dem Feld DateOfBirth sortiert. Warum ist das so?

Die Datenbankengine hat richtig erkannt, dass sie jetzt den neuen Index auf DateOfBirth verwenden kann, um die Abfrage effizienter auszuführen. Sie liest die Werte für DoB aus dem sortierten Index, um die Daten zu filtern, danach holt sie die zu den Indexeinträgen gehörigen, vollständigen Datensätze hinzu. Die Ergebnismenge ist jetzt nach DateOfBirth sortiert, weil es zusätzlichen Aufwand bedeutet hätte, die Daten wieder nach dem Primärschlüssel zu sortieren. Grundsätzlich wird eine Datenbankengine niemals Ressourcen auf irgendeine Operation verschwenden, die nicht zwingend erforderlich ist, um das gewünschte Ergebnis der jeweiligen Abfrage zu erzielen.

Also, selbst wenn du Datensätze in einer einzelnen Microsoft Access Tabelle, in Reihenfolge ihres Primärschlüssels abfragen willst, solltest du immer ein explizites ORDER BY in deiner Abfrage angeben.

DFirst/DLast und was mit ihnen verkehrt läuft

Die Funktionsnamen First und Last (ErsterWert, LetzterWert) Aggregatfunktionen und ihrer VBA Gegenstücke DFirst und DLast (DomErsterWert, DomLetzterWert) Domänenfunktionen, suggerieren, dass du damit den ersten oder letzten Datensatz aus einer Datensatzmenge ermitteln kannst. Bis Access 2003 stand etwa das folgende in der offiziellen Dokumentation.

„Die Microsoft Access DomErsterWert-Funktion gibt den ersten Wert aus einer Access Tabelle (oder Domäne) zurück.“
(Ich konnte leider nicht den wortwörtlichen Text der alten Hilfedateien übernehmen. Diese sind heutzutage schwer zu finden.)

Die meisten Leute interpretieren dies (fälschlicherweise) so, dass dies der chronologisch erste erfasste Datensatz ist. Allerdings sollte es klar werden, dass bei der obigen Aussage die Angabe fehlt, über welche Sortierung der ermittelte Datensatz der erste oder letzte ist.

Die aktuelle Dokumentation ist in dieser Hinsicht klarer. Die Access Developer Reference for the First and Last Functions merkt an (Übersetzung von mir):

“Sie geben einfach den Wert des angegebenen Feldes in dem ersten oder letzten Datensatz der durch die Abfrage zurückgegeben Ergebnismenge zurück. Da Datensätze üblicherweise in keiner bestimmten Reihenfolge zurückgeliefert werden (es sei denn die Abfrage enthält eine ORDER BY Klausel), werden die von diesen Funktionen zurückgelieferten Datensätze zufällig sein.“

Korrekt, aber beachte bitte, dass diese Funktionen Aggregatfunktionen sind. Jegliche ORDER BY Klausel wird üblicherweise auf die endgültige Ergebnismenge angewendet, aber nicht auf die untergeordnete Abfrage aus der der erste/letzte Wert ermittelt wird. Also solltest du immer davon ausgehen, dass die Ergebnisse zufällig sind, solange du nicht die Eingangswerte mittels einer explizit sortierten Unterabfrage ermittelst.

Da es gar nicht möglich ist, ein ORDER BY auf eine Domänenaggregatfunktion anzuwenden, wird die Dokumentation zu der DFirst- und DLast-Funktion noch deutlicher.

„Sie können die Funktionen DFirst und DLast verwenden, um einen zufällig ausgewählten Datensatz aus einem bestimmten Feld einer Tabelle oder Abfrage zurückzugeben wird, wenn Sie lediglich irgendeinen Wert aus diesem Feld benötigen“ (sic!)

Diese Aussage solltest du aber keinesfalls in der Form missverstehen, dass der Rückgabewert von DFirst/DLast von einem Zufallsgenerator bestimmt wird. Ganz im Gegenteil; es ist der erste Wert, der für die ACE-Engine gerade am einfachsten zu ermitteln ist. Dies ist in der Regel immer derselbe Wert, bis sich die Umstände ändern, d.h. weitere Datensätze eingegeben werden, die Datenbank komprimiert wird oder ein neuer Index angelegt wird. Du darfst dich keinesfalls darauf verlassen, dass DFirst/DLast einen bestimmten Wert zurückgibt, auch wenn du in zahlreichen Versuchen immer das erwartete Ergebnis bekommen hast.

Bessere Alternativen zu DFirst/DLast: Min/Max bzw. DMin/DMax

Soweit das Problem mit DFirst/DLast. - Was aber ist ein besserer Weg, um den ersten oder letzten Wert in einem bestimmten Kontext zu ermitteln?

Die Aggregatfunktionen Min und Max ermitteln zuverlässig und immer reproduzierbar den kleinsten oder größten Wert aus einer Spalte. Wenn du also nur den ersten (oder letzten) Wert aus einer bestimmten Spalte ermitteln willst, ist es trivial diese Werte über DMin oder DLast zu ermitteln.

Schwieriger wird es, wenn du nicht direkt den Wert aus der Spalte deren Reihenfolge über Min/Max eindeutig definiert ist, sondern den Wert einer anderen Spalte des Datensatzes, der nach der Sortierung an erster oder letzter Position wäre. Du möchtest, zum Beispiel, den Auftragswert der letzten Bestellung eines Kunden ermitteln. Die Sortierung wäre hier das Bestelldatum, aber der gewünschte Wert ist nicht das Datum selbst, sondern der Gesamtbetrag dieser Bestellung.

In diesen Fällen hilft ein zweistufiger Ansatz. Dafür gibt es zwei Optionen.

1. Erste Option; du ermittelst erst mit der DMin/DMax-Funktion den Wert aus dem sortierbaren Feld (das Datum in dem obigen Beispiel) und verwendest diesen als zusätzliches Kriterium in deiner Abfrage um das eigentliche Ergebnis zu ermitteln.
2. Zweite Option; du erstellst erst eine Abfrage, die den Primärschlüssel und das Maximum des sortierbaren Feldes (z.B. Kundennummer und das Maximum des Bestelldatums) enthält. In einer zweiten Abfrage verbindest du über diese Felder dann die erste Abfrage mit einer zweiten Abfrage auf die Kunden und Bestellungen, die alle Felder enthält, aber eben nur für den jeweils letzten (neusten) Bestelldatensatz.

Dies habe ich ausführlicher und mit Beispielen in meinem Text zur Abfrage von aggregierten 1-zu-N-Daten erläutert.

Schlussfolgerungen

• Du kannst absolut niemals sicher wissen, wie die Ergebnisse einer Abfrage sortiert sind, es sie denn du gibst explizit eine Sortierung mit einer ORDER BY Klausel vor.
• Die Funktionen DFirst und DLast sollten beide besser DRandom heißen. Es ist unmöglich genau zu wissen, was sie zurückgeben werden.
• Auch wenn es zusätzlichen Aufwand erfordert, ist es meist besser, die Min- und Max-Aggregatfunktionen zu verwenden, um ein verlässliches Ergebnis zu ermitteln.

Share this article: