PDA

Vollständige Version anzeigen : SQL ist leicht (12) - Anwendungslogik, Technik, Performance - ein Beispiel


ebs17
28.02.2019, 22:52
Greifen wir uns ein Beispiel aus dem normalen Leben: Abfrage soll nur den letzten Bericht des Kunden zeigen (https://www.ms-office-forum.net/forum/showthread.php?t=358523)
SELECT Besuchsbericht.Datum, Kunden.Kunde, Besuchsbericht.Resultat
FROM Kunden INNER JOIN Besuchsbericht ON Kunden.KNR = Besuchsbericht.KNR
ORDER BY Besuchsbericht.Datum DESC, Kunden.Kunde
Was muss ich in der Bedingung des Datenfelds DATUM eingeben, damit von jedem Kunden nur der letzte, aktuellste Bericht gezeigt wird?

Zuallererst sei die Frage unmittelbar beantwortet:
SELECT
B.Datum,
K.Kunde,
B.Resultat
FROM
Kunden AS K
INNER JOIN Besuchsbericht AS B
ON K.KNR = B.KNR
WHERE
B.Datum IN
(
SELECT TOP 1
X.Datum
FROM
Besuchsbericht AS X
WHERE
X.KNR = B.KNR
ORDER BY
X.Datum DESC
)
ORDER BY
B.Datum DESC,
K.Kunde

Diese Gestaltung hat aber einige Nachteile, die sich letzten Endes in längeren Laufzeiten widerspiegeln werden und sollte daher nicht erste Wahl sein.
Probleme:
(1) Der INNER JOIN wird sehr wahrscheinlich als erstes ausgeführt. Verknüpfen macht Arbeit. Wenn man das excellike per SVerweis ausführen würde, würde einem das sehr schnell ziemlich klar. Erst nach getaner Arbeit wird gefiltert.
(2) Die Filterung erfolgt mittels korrelierter Unterabfrage. Das heißt, die Unterabfrage wird mehr als einmal ausgeführt, deutlich mehr, schlimmstenfalls jedes Mal pro Datensatz der oberen Ebene. Das ist Aufwand, alleine schon das Sortieren. Aufwand kostet.
(3) Hat man nun einen zu setzenden Index auf das Feld Datum "vergessen", gibt das einen zusätzlichen Schub in Richtung Laufzeitverlängerung. Die Indizes auf jeweils KNr darf man wohl voraussetzen, weil das die Beziehung mit gesetzter referentieller Integrität schon alleine voraussetzt bzw. regelt.

Nachdem das durchleuchtet wurde, versuchen wir doch mal, clever, also schnell zu arbeiten.

Den Nachteil (1) vermeiden wir, indem wir erst die Tabelle Besuchsbericht auf die nötigen Datensätze reduzieren:
SELECT
KNR,
MAX(Datum) AS MaxDatum
FROM
Besuchsbericht
GROUP BY
KNr
Leider können wir das Feld Resultat oder auch weitere nicht unmittelbar mitführen, das Gruppieren/Aggregieren lässt das nicht zu. Dafür geht diese einmalig auszuführende Abfrage rasant schnell.
Die weiteren Felder der Tabelle holen wir uns, indem wir die Tabelle neu hinzu verknüpfen:
SELECT
A.KNr,
A.Datum,
A.Resultat
FROM
Besuchsbericht AS A
INNER JOIN
(
SELECT
KNR,
MAX(Datum) AS MaxDatum
FROM
Besuchsbericht
GROUP BY
KNr
) AS U
ON A.Knr = U.KNr
AND
A.Datum = U.MaxDatum
(die vorangegangene Abfrage wurde blau markiert)

Kontrollfrage: Was haben wir jetzt erreicht?
Nun, wir haben die reduzierte "Tabelle" Besuchsbericht, die nur die Datensätze mit dem letzten Besuchbericht, identifizierbar am maximalen Datum je KNr, enthält.
Damit wird nach außen nur diese flache Tabelle zu verknüpfen sein.

Parallel haben wir Nachteil (2) umgangen. (Unter)Abfragen in FROM-Teil werden nur einmal ausgeführt. Das Wiederholungsfiasko wie bei korrelierten Unterabfragen findet nicht statt.

Der Index auf Datum ist immer noch sinnvoll und in Sachen Performance hilfreich, aber ein Fehlen wird sich nicht ganz so dramatisch auswirken wie im ersten Vorschlag.

Somit verbleibt nun nur noch, die Verknüpfung nach außen auszuführen:
SELECT
B.Datum,
K.Kunde,
B.Resultat
FROM
Kunden AS K
INNER JOIN
(
SELECT
A.KNr,
A.Datum,
A.Resultat
FROM
Besuchsbericht AS A
INNER JOIN
(
SELECT
KNR,
MAX(Datum) AS MaxDatum
FROM
Besuchsbericht
GROUP BY
KNr
) AS U
ON A.Knr = U.KNr
AND
A.Datum = U.MaxDatum) AS B
ON K.KNR = B.KNR
ORDER BY
B.Datum DESC,
K.Kunde
(die vorangegangene Abfrage wurde blau markiert)

Die Messung der Performance und der Unterschiede durch die Varianten sei an dieser Stelle den interessierten Lesern überlassen. Zahlen dazu dürfen aber gerne an dieser Stelle dokumentiert werden.