Redgate Hub
T-SQL vensterfuncties maken het schrijven van veel query ‘ s eenvoudiger, en ze leveren vaak ook betere prestaties ten opzichte van oudere technieken. Bijvoorbeeld, het gebruik van de LAG-functie is zo veel beter dan het doen van een self-join. Om over het algemeen betere prestaties te krijgen, moet u echter het concept van framing begrijpen en hoe vensterfuncties afhankelijk zijn van sorteren om de resultaten te leveren.
opmerking: Zie mijn nieuwe artikel om te leren hoe verbeteringen aan de optimizer in 2019 de prestaties beïnvloeden!,
de over-clausule en sortering
Er zijn twee opties in de over-clausule die sortering kunnen veroorzaken: partitie op en volgorde op. Partitie door wordt ondersteund door alle window functies, maar het is optioneel. De volgorde is vereist voor de meeste functies. Afhankelijk van wat u probeert te bereiken, worden de gegevens gesorteerd op basis van de over-clausule, en dat kan de prestatie-bottleneck van uw query zijn.
De volgorde per optie in de over-clausule is vereist, zodat de database-engine de rijen zogezegd kan opstellen om de functie in de juiste volgorde toe te passen., Stel bijvoorbeeld dat u de functie ROW_NUMBER wilt toepassen in volgorde van SalesOrderID. De resultaten zien er anders uit dan als u de functie wilt toepassen in volgorde van TotalDue in aflopende volgorde., Hier is het voorbeeld:
|
1
2
3
4
5
6
7
8
9
10
11
|
GEBRUIK AdventureWorks2017; –of welke versie u hebt
GO
SELECTEER SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY SalesOrderID) ALS RowNum
VAN de Omzet.,SalesOrderHeader;
SELECT SalesOrderID,
Totaaldue,
ROW_NUMBER() OVER(ORDER BY Totaaldue DESC) AS RowNum
vanaf Sales.SalesOrderHeader;
|
aangezien de eerste query de clustersleutel gebruikt als de volgorde per optie, is Sorteren niet nodig.
de tweede zoekopdracht heeft een dure sorteerbewerking.,
De volgorde door in de over-clausule is niet verbonden met de volgorde door clausule toegevoegd aan de Algemene query die heel anders zou kunnen zijn., 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
volgorde volgens SalesOrderID;
|
de geclusterde indexsleutel is SalesOrderID, maar de rijen moeten eerst worden gesorteerd op Totaaldue in aflopend bestellen en dan terug naar salesorderid. Neem een kijkje op het uitvoeringsplan:
de partitie per clausule, ondersteund maar optioneel, voor alle T-SQL vensterfuncties veroorzaakt ook sortering. Het is vergelijkbaar met, maar niet precies zoals, de groep door clausule voor geaggregeerde queries., 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;
|
het uitvoeringsplan toont slechts één sorteeroperatie, een combinatie van CustomerID en SalesOrderID.
de enige manier om de prestatie-impact van Sorteren te overwinnen is door een index aan te maken specifiek voor de over-clausule. In zijn boek Microsoft SQL Server 2012 High-Performance T-SQL met behulp van Window functies, Itzik Ben-Gan beveelt de POC-index. POC staat voor (P)ARTITION BY, (O)RDER BY, en (c) overing., Hij raadt aan om kolommen toe te voegen die gebruikt worden voor het filteren vóór de partitie door en volgorde door kolommen in de sleutel. Voeg vervolgens extra kolommen toe die nodig zijn om een dekkingsindex te maken als opgenomen kolommen. Net als iets anders, je nodig hebt om te testen om te zien hoe een dergelijke index van invloed op uw vraag en de totale werklast. Natuurlijk kunt u geen index toevoegen voor elke query die u schrijft, maar als de prestaties van een bepaalde query die gebruik maakt van een window-functie belangrijk is, kunt u dit advies uitproberen.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID, SalesOrderID)
INCLUDE (Totaaldue);
|
wanneer u de query opnieuw uitvoert, is de sorteerbewerking nu verdwenen uit het uitvoeringsplan:
framing
naar mijn mening is framing het moeilijkste concept om te begrijpen bij het leren over T-SQL Window functies. Voor meer informatie over de syntaxis zie inleiding tot T-SQL window functies., Framing is vereist voor het volgende:
- Vensteraggregaten met de volgorde van, gebruikt voor lopende totalen of voortschrijdende gemiddelden, bijvoorbeeld
- FIRST_VALUE
- LAST_VALUE
gelukkig is framing meestal niet vereist, maar helaas is het gemakkelijk om het frame over te slaan en de standaard te gebruiken. Het standaard frame is altijd bereik tussen onbegrensde voorafgaande en huidige rij. Terwijl u de juiste resultaten te krijgen, zolang de volgorde door optie bestaat uit een unieke kolom of reeks kolommen, ziet u een prestatie hit.,id=”4b64f77d3b”>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
STATISTIEKEN IO INSTELLEN OP;
GO
SELECTEER klant-id,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION BY CustomerID OM DOOR SalesOrderID)
ALS Lopendtotaal
VAN de Omzet.,SalesOrderHeader;
Select CustomerID,
SalesOrderID,
Totaaldue,
Som(Totaaldue) OVER(partitie door CustomerID volgorde door SalesOrderID
rijen tussen onbegrensde voorgaande en huidige rij)
als RunningTotal
van verkoop.SalesOrderHeader;
|
de resultaten zijn hetzelfde, maar de prestaties zijn zeer verschillend. Helaas vertelt het uitvoeringsplan je niet de waarheid in deze zaak., Het rapporteert dat elke query 50% van de bronnen in beslag nam:
Als u de statistieken IO waarden bekijkt, zult u het verschil zien:
het gebruik van het juiste frame is nog belangrijker als uw bestelling per optie niet uniek is of als u LAST_VALUE gebruikt. In dit voorbeeld is de volgorde per kolom Orderdatum, maar sommige klanten hebben meer dan één bestelling op een bepaalde datum geplaatst. Wanneer u het frame niet opgeeft of het bereik gebruikt, behandelt de functie overeenkomende datums als onderdeel van hetzelfde venster.,”>
de reden voor de discrepantie is dat bereik de gegevens logisch ziet terwijl rijen het positioneel zien. Er zijn twee oplossingen voor dit probleem. Een daarvan is om ervoor te zorgen dat de volgorde door optie is uniek. De andere en belangrijkste optie is om altijd het frame op te geven waar het wordt ondersteund.
de andere plaats waar framing logische problemen veroorzaakt is LAST_VALUE., LAST_VALUE geeft een expressie van de laatste rij van het frame. Aangezien het standaard frame (bereik tussen onbegrensde voorgaande en huidige rij) alleen naar de huidige rij gaat, is de laatste rij van het frame de rij waar de berekening wordt uitgevoerd.,
Vensteraggregaten
een van de handigste kenmerken van T-SQL Window functies is de mogelijkheid om een geaggregeerde expressie toe te voegen aan een niet-geaggregeerde query. Helaas kan dit vaak slecht presteren. Om het probleem te zien, je nodig hebt om te kijken naar de statistieken IO resultaten waar u een groot aantal logische leest te zien., Mijn advies als je waarden moet retourneren op verschillende granulariteiten binnen dezelfde query voor een groot aantal rijen is om een van de oudere technieken te gebruiken, zoals een common table expression( CTE), temp table, of zelfs een variabele. Als het mogelijk is om pre-aggregate voor het gebruik van het venster aggregate, dat is een andere optie.,ows is het verschil tussen het venster geaggregeerde en in een andere techniek:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECTEER SalesOrderID,
TotalDue,
SUM(TotalDue) OVER() ALS OverallTotal
VAN de Omzet.,SalesOrderHeader
waar YEAR (OrderDate) = 2013;
DECLARATE @OverallTotal MONEY;
SELECT @OverallTotal = SUM(Totaaldue)
van verkopen.SalesOrderHeader
waar YEAR ( OrderDate) = 2013;
SELECT SalesOrderID,
Totaaldue,
@OverallTotal AS OverallTotal
van Sales.,SalesOrderHeader als SOH
waar YEAR(OrderDate) = 2013;
|
de eerste query scant de tabel slechts één keer, maar heeft 28.823 logische reads in a werktafel. De tweede methode scant de tabel twee keer, maar het heeft de werktafel niet nodig.
het volgende voorbeeld gebruikt een windows-aggregaat toegepast op een geaggregeerde expressie:
bij het gebruik van vensterfuncties in een geaggregeerde query, moet de expressie dezelfde regels volgen als de Select en ORDER BY-clausules., In dit geval wordt de window-functie toegepast op Som (Totaaldue). Het ziet eruit als een geneste aggregaat, maar het is eigenlijk een window functie toegepast op een aggregaat expressie.
aangezien de gegevens zijn geaggregeerd voordat de window-functie werd toegepast, zijn de prestaties goed:
Er is nog een interessant ding om te weten over het gebruik van window aggregaten. Als u meerdere uitdrukkingen gebruikt die matching over-clausule definities gebruiken, ziet u geen extra verslechtering van de prestaties.
mijn advies is om deze functionaliteit voorzichtig te gebruiken., Het is heel handig, maar niet zo goed schaal.
Prestatievergelijkingen
De tot nu toe gepresenteerde voorbeelden hebben gebruik gemaakt van de kleine verkopen.SalesOrderHeader tafel van AdventureWorks en beoordeeld de uitvoering plannen en logische leest. In het echte leven, uw klanten zullen niet de zorg over de uitvoering plan of de logische leest; zij zullen de zorg over hoe snel de queries lopen. Om het verschil in looptijd beter te zien, gebruikte ik Adam Machanic ‘ s Thinking Big (Adventure) script met een twist.
het script maakt een tabel genaamd bigTransactionHistory met meer dan 30 miljoen rijen., Na het uitvoeren van Adam ‘ s script, heb ik nog twee kopieën van zijn tafel gemaakt, met respectievelijk 15 en 7,5 miljoen rijen. Ik heb ook de teruggooi resultaten na uitvoering eigenschap in de Query-Editor ingeschakeld, zodat het vullen van het raster geen invloed heeft op de looptijd. Ik heb elke test drie keer uitgevoerd en de buffer cache gewist voor elke run.,
I can’t say enough about how important it is to use the frame when it’s supported., Om het verschil te zien, heb ik een test uitgevoerd om lopende totalen te berekenen met behulp van vier methoden:
- Cursor solution
- gecorreleerde subquery
- vensterfunctie met standaard frame
- vensterfunctie met rijen
Ik heb de test uitgevoerd op de drie nieuwe tabellen. Hier zijn de resultaten in een diagramformaat:
bij het draaien met het rijen frame duurde de 7,5 miljoen rijtabel minder dan een seconde om te draaien op het systeem dat ik gebruikte bij het uitvoeren van de test. De 30 miljoen rij tafel duurde ongeveer een minuut te lopen., 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 dit geval heb ik alleen de 30 miljoen rijtabel gebruikt, maar één, twee of drie berekeningen uitgevoerd met dezelfde korreligheid en dus dezelfde over-clausule. Ik vergeleek het raam aggregaat prestaties met een CTE en een gecorreleerde subquery.
het vensteraggregaat presteerde het slechtst, ongeveer 1,75 minuten in elk geval. De CTE presteerde het beste bij het verhogen van het aantal berekeningen, omdat de tabel slechts één keer werd aangeraakt voor alle drie., De gecorreleerde subquery presteerde slechter bij het verhogen van het aantal berekeningen omdat elke berekening afzonderlijk moest worden uitgevoerd, en het raakte de tabel in totaal vier keer.,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
FROM bigTransactionHistory AS o
JOIN Calcs ON O. ProductID = Calcs.ProductID;
|
conclusie
T-SQL vensterfuncties zijn gepromoot als geweldig voor prestaties. Naar mijn mening maken ze het schrijven van vragen makkelijker, maar je moet ze goed begrijpen om goede prestaties te krijgen. Indexeren kan een verschil maken, maar je kunt geen index maken voor elke query die je schrijft., Framing is misschien niet gemakkelijk te begrijpen, maar het is zo belangrijk als je moet opschalen naar grote tafels.