Redgate Hub (Deutsch)
T-SQL-Fensterfunktionen erleichtern das Schreiben vieler Abfragen und bieten häufig auch eine bessere Leistung als ältere Techniken. Zum Beispiel ist die Verwendung der LAG-Funktion so viel besser als ein Self-Join. Um insgesamt eine bessere Leistung zu erzielen, müssen Sie jedoch das Konzept des Framing verstehen und wissen, wie Fensterfunktionen auf das Sortieren angewiesen sind, um die Ergebnisse bereitzustellen.
HINWEIS: In meinem neuen Artikel erfahren Sie, wie sich Verbesserungen des Optimierers im Jahr 2019 auf die Leistung auswirken!,
Die OVER-Klausel und Sortierung
In der OVER-Klausel gibt es zwei Optionen, die eine Sortierung verursachen können: PARTITION NACH und ORDER BY. PARTITION BY wird von allen Fensterfunktionen unterstützt, ist aber optional. Die ORDER BY ist für die meisten Funktionen erforderlich. Abhängig davon, was Sie erreichen möchten, werden die Daten basierend auf der OVER-Klausel sortiert, und dies könnte der Leistungsengpass Ihrer Abfrage sein.
Die ORDER BY-Option in der OVER-Klausel ist erforderlich, damit die Datenbank-Engine die Zeilen sozusagen anordnen kann, um die Funktion in der richtigen Reihenfolge anzuwenden., Angenommen, Sie möchten, dass die Funktion ROW_NUMBER in der Reihenfolge SalesOrderID angewendet wird. Die Ergebnisse sehen anders aus, als wenn Sie die Funktion in der Reihenfolge TotalDue in absteigender Reihenfolge anwenden möchten., Hier ist das Beispiel:
|
1
2
3
4
5
6
7
8
9
10
11
|
VERWENDEN AdventureWorks2017; – oder welche Version Sie haben
GEHEN SIE
WÄHLEN SIE SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNum
FROM Sales.,SalesOrderHeader;
SELECT SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) AS RowNum
VOM Umsatz.SalesOrderHeader;
|
Da die erste Abfrage den Clusterschlüssel als ORDER BY-Option verwendet, ist keine Sortierung erforderlich.
Die zweite Abfrage eine teure Art der Bedienung.,
Die ORDER BY in der OVER-Klausel ist nicht mit der ORDER BY-Klausel verbunden, die der Gesamtabfrage hinzugefügt wurde, was sehr unterschiedlich sein kann., Here is an example showing what happens if the two are different:
|
1
2
3
4
5
|
SELECT SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) AS RowNum
FROM Sales.,SalesOrderHeader
ORDER BY SalesOrderID;
|
Der gruppierte Indexschlüssel ist SalesOrderID, aber die Zeilen müssen zuerst nach TotalDue in absteigender Reihenfolge sortiert und dann zurück zu SalesOrderID. Schauen Sie sich den Ausführungsplan an:
Die unterstützte, aber optionale PARTITION BY-Klausel bewirkt für alle T-SQL-Fensterfunktionen auch eine Sortierung. Es ist ähnlich, aber nicht genau wie die GROUP BY Klausel für aggregierte Abfragen., This example starts the row numbers over for each customer.
|
1
2
3
4
5
6
|
SELECT CustomerID,
SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS RowNum
FROM Sales.,SalesOrderHeader;
|
Der Ausführungsplan zeigt nur eine Art Betrieb, eine Kombination von CustomerID und SalesOrderID.
Die einzige Möglichkeit, die Auswirkungen der Sortierung auf die Leistung zu überwinden, besteht darin, einen Index speziell für die OVER-Klausel zu erstellen. In seinem Buch Microsoft SQL Server 2012 Hochleistungs-T-SQL mit Fensterfunktionen empfiehlt Itzik Ben-Gan den POC-Index. POC steht für (P)ARTITION VON, (O)RDER, und (c)overing., Er empfiehlt, alle Spalten hinzuzufügen, die zum Filtern vor der PARTITION nach und NACH Spalten im Schlüssel verwendet werden. Fügen Sie dann zusätzliche Spalten hinzu, die zum Erstellen eines Abdeckungsindex als enthaltene Spalten erforderlich sind. Genau wie alles andere müssen Sie testen, wie sich ein solcher Index auf Ihre Abfrage und die gesamte Arbeitsbelastung auswirkt. Natürlich können Sie nicht für jede Abfrage, die Sie schreiben, einen Index hinzufügen, aber wenn die Leistung einer bestimmten Abfrage, die eine Fensterfunktion verwendet, wichtig ist, können Sie diesen Rat ausprobieren.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID, SalesOrderID)
INCLUDE (TotalDue);
|
Wenn Sie die Abfrage erneut ausführen, ist die Sortieroperation jetzt aus dem Ausführungsplan verschwunden:
Framing
Meiner Meinung nach ist Framing das am schwierigsten zu verstehende Konzept, wenn man etwas über T-SQL-Fensterfunktionen lernt. Weitere Informationen zur Syntax finden Sie unter Einführung in T-SQL-Fensterfunktionen., Framing ist für Folgendes erforderlich:
- Fensteraggregate mit der REIHENFOLGE nach, die zum Ausführen von Summen oder gleitenden Durchschnitten verwendet werden, z. B.
- FIRST_VALUE
- LAST_VALUE
Glücklicherweise ist Framing die meiste Zeit nicht erforderlich, aber leider ist es einfach, den Frame zu überspringen und den Standardwert zu verwenden. Der Standardrahmen ist immer DER BEREICH ZWISCHEN UNBEGRENZTER VORHERIGER UND AKTUELLER ZEILE. Sie erhalten zwar die richtigen Ergebnisse, solange die Option ORDER BY aus einer eindeutigen Spalte oder einem Satz von Spalten besteht, aber Sie sehen einen Leistungseinbruch.,id=“4b64f77d3b“>
|
1
2
3
4
5
6
7
8
9
/div>
10
11
12
13
14
15
16
|
SET STATISTICS IO ON;
GO
SELECT CustomerID,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS runningTotal
FROM Sales.,SalesOrderHeader;
SELECT CustomerID
SalesOrderID
TotalDue,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID
ZEILEN ZWISCHEN UNBOUNDED VORHERGEHENDEN UND AKTUELLEN ZEILE)
ALS RunningTotal
VOM Umsatz.SalesOrderHeader;
|
die Ergebnisse sind Die gleichen, aber die Leistung ist sehr unterschiedlich. Leider sagt Ihnen der Ausführungsplan in diesem Fall nicht die Wahrheit., Es wird berichtet, dass jede Abfrage 50% der Ressourcen beansprucht hat:
Wenn Sie die Statistiken und Werte überprüfen, sehen Sie den Unterschied:
Die Verwendung des richtigen Rahmens ist noch wichtiger, wenn Ihre ORDER BY-Option nicht eindeutig ist oder wenn Sie LAST_VALUE verwenden. In diesem Beispiel lautet die Spalte ORDER BY OrderDate, einige Kunden haben jedoch mehr als eine Bestellung zu einem bestimmten Datum aufgegeben. Wenn Sie den Rahmen nicht angeben oder den BEREICH verwenden, behandelt die Funktion übereinstimmende Daten als Teil desselben Fensters.,“>
Der Grund für die Diskrepanz ist, dass RANGE die Daten logisch sieht, während ROWS sie positionell sieht. Es gibt zwei Lösungen für dieses problem. Eine besteht darin, sicherzustellen, dass die BESTELLUNG NACH Option eindeutig ist. Die andere und wichtigere Option besteht darin, immer den Frame anzugeben, in dem er unterstützt wird.
Der andere Ort, an dem das Framing logische Probleme verursacht, ist LAST_VALUE., LAST_VALUE gibt einen Ausdruck aus der letzten Zeile des Frames zurück. Da der Standardrahmen (BEREICH ZWISCHEN UNBEGRENZTER VORHERGEHENDER UND AKTUELLER ZEILE) nur bis zur aktuellen Zeile reicht, ist die letzte Zeile des Rahmens die Zeile, in der die Berechnung durchgeführt wird.,
Fensteraggregate
Eines der handlichsten Funktionen von T-SQL-Fensterfunktionen ist die Möglichkeit, einen Aggregatausdruck zu einer nicht aggregierten Abfrage hinzuzufügen. Leider kann dies oft schlecht funktionieren. Um das Problem zu sehen, müssen Sie sich die Statistiken und Ergebnisse ansehen, in denen eine große Anzahl logischer Lesevorgänge angezeigt wird., Mein Rat, wenn Sie Werte mit unterschiedlichen Granularitäten innerhalb derselben Abfrage für eine große Anzahl von Zeilen zurückgeben müssen, ist die Verwendung einer der älteren Techniken, z. B. eines Common Table Expression (CTE), einer temporären Tabelle oder sogar einer Variablen. Wenn es möglich ist, vor der Verwendung des Fensteraggregats vorzuaggregieren, ist dies eine weitere Option.,ows ist der Unterschied zwischen dem Fenster-Aggregat und eine andere Technik:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECT SalesOrderID,
TotalDue,
SUM(TotalDue) ÜBER() ALS OverallTotal
VOM Umsatz.,SalesOrderHeader
WHERE YEAR(OrderDate) =2013;
DECLARE @OverallTotal MONEY;
SELECT @OverallTotal = SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2013;
SELECT SalesOrderID,
TotalDue,
@OverallTotal AS OverallTotal
FROM Sales.,SalesOrderHeader AS SOH
WHERE YEAR(OrderDate) = 2013;
|
Die erste Abfrage nur die Tabelle scannt einmal, aber es hat 28,823 logische Lesevorgänge in einer Werkbank. Die zweite Methode scannt die Tabelle zweimal, benötigt jedoch nicht die Arbeitstabelle.
Im nächsten Beispiel wird ein Windows-Aggregat verwendet, das auf einen Aggregatausdruck angewendet wird:
Bei Verwendung von Fensterfunktionen in einer Aggregatabfrage muss der Ausdruck denselben Regeln wie die SELECT-und ORDER BY-Klauseln folgen., In diesem Fall wird die Fensterfunktion auf SUM(TotalDue) angewendet. Es sieht aus wie ein verschachteltes Aggregat, aber es ist wirklich eine Fensterfunktion, die auf einen Aggregatausdruck angewendet wird.
Da die Daten aggregiert wurden, bevor die Fensterfunktion angewendet wurde, ist die Leistung gut:
Es gibt eine weitere interessante Sache über die Verwendung von Fensteraggregaten zu wissen. Wenn Sie mehrere Ausdrücke verwenden, die übereinstimmende Klauseldefinitionen verwenden, wird keine zusätzliche Leistungsverschlechterung angezeigt.
Mein Rat ist, diese Funktionalität mit Vorsicht zu verwenden., Es ist ziemlich praktisch, skaliert aber nicht so gut.
Leistungsvergleiche
Die bisher vorgestellten Beispiele haben den kleinen Umsatz genutzt.SalesOrderHeader Tabelle von AdventureWorks und überprüft die Ausführungspläne und logische Lesevorgänge. Im wirklichen Leben kümmern sich Ihre Kunden nicht um den Ausführungsplan oder die logischen Lesevorgänge, sondern darum, wie schnell die Abfragen ausgeführt werden. Um den Unterschied in den Laufzeiten besser zu erkennen, habe ich Adam Machanics Thinking Big (Adventure) – Skript mit einem Twist verwendet.
Das Skript erstellt eine Tabelle namens bigTransactionHistory mit über 30 Millionen Zeilen., Nachdem ich Adams Skript ausgeführt hatte,erstellte ich zwei weitere Kopien seiner Tabelle mit 15 bzw. Ich habe auch die Eigenschaft Ergebnisse nach Ausführung im Abfrageeditor verwerfen aktiviert, damit das Auffüllen des Rasters die Laufzeiten nicht beeinflusst. Ich habe jeden Test dreimal ausgeführt und den Puffercache vor jedem Lauf gelöscht.,
I can’t say enough about how important it is to use the frame when it’s supported., Um den Unterschied zu erkennen, habe ich einen Test durchgeführt, um laufende Summen mit vier Methoden zu berechnen:
- Cursorlösung
- Korrelierte Unterabfrage
- Fensterfunktion mit Standardrahmen
- Fensterfunktion mit ZEILEN
Ich habe den Test für die drei neuen Tabellen ausgeführt. Hier sind die Ergebnisse in einem Diagrammformat:
Beim Ausführen mit dem Zeilenrahmen dauerte die Ausführung der 7,5-Millionen-Zeilentabelle weniger als eine Sekunde auf dem System, das ich bei der Durchführung des Tests verwendet habe. Die Ausführung der 30-Millionen-Zeilentabelle dauerte etwa eine Minute., id=“4b64f77d3b“>
|
1
2
3
4
5
|
SELECT ProductID, SUM(ActualCost) OVER(PARTITION BY ProductID
ORDER BY TransactionDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM bigTransactionHistory;
|
I also performed a test to see how window aggregates performed compared to traditional techniques., In diesem Fall habe ich nur die 30-Millionen-Zeilentabelle verwendet, aber eine, zwei oder drei Berechnungen mit derselben Granularität und daher derselben OVER-Klausel durchgeführt. Ich habe die Gesamtleistung des Fensters mit einer CTE und einer korrelierten Unterabfrage verglichen.
Das Fensteraggregat hat die schlechteste Leistung erbracht, jeweils etwa 1,75 Minuten. Die CTE hat beim Erhöhen der Anzahl der Berechnungen die beste Leistung erbracht, da die Tabelle nur einmal für alle drei berührt wurde., Die korrelierte Unterabfrage wurde beim Erhöhen der Anzahl der Berechnungen schlechter ausgeführt, da jede Berechnung separat ausgeführt werden musste und die Tabelle insgesamt viermal berührt wurde.,v>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
WITH Calcs AS (
SELECT ProductID,
AVG(ActualCost) AS AvgCost,
MIN(ActualCost) AS MinCost,
MAX(ActualCost) AS MaxCost
FROM bigTransactionHistory
GROUP BY ProductID)
SELECT O.,ProductID,
ActualCost,
AvgCost,
MinCost,
MaxCost
AUS bigTransactionHistory O
JOIN Calcs AUF O. ProductID = Calcs.ProductID;
|
T-SQL-Fensterfunktionen wurden als leistungsstark befördert. Meiner Meinung nach erleichtern sie das Schreiben von Abfragen, aber Sie müssen sie gut verstehen, um eine gute Leistung zu erzielen. Indizierung kann einen Unterschied machen, aber Sie können nicht für jede Abfrage, die Sie schreiben, einen Index erstellen., Framing ist vielleicht nicht leicht zu verstehen, aber es ist so wichtig, wenn Sie auf große Tische skalieren müssen.