Redgate Hub (Svenska)
T-SQL fönsterfunktioner gör att skriva många frågor enklare, och de ger ofta bättre prestanda samt över äldre tekniker. Till exempel är det så mycket bättre att använda LAG-funktionen än att göra en självkoppling. För att få bättre prestanda totalt sett måste du förstå begreppet inramning och hur fönsterfunktioner är beroende av sortering för att ge resultaten.
OBS! Se min nya artikel för att lära dig hur förbättringar av optimeraren i 2019 påverkar prestanda!,
Over-klausulen och sorteringen
det finns två alternativ i over-klausulen som kan orsaka sortering: PARTITION efter och ordning efter. PARTITION BY stöds av alla fönsterfunktioner, men det är valfritt. Ordern efter krävs för de flesta funktionerna. Beroende på vad du försöker åstadkomma, data kommer att sorteras baserat på över klausulen, och det kan vara prestanda flaskhals av din fråga.
ordningen efter alternativet i över-klausulen krävs så att databasmotorn kan rada upp raderna, så att säga, för att kunna tillämpa funktionen i rätt ordning., Säg till exempel att du vill att row_number-funktionen ska tillämpas i Order of SalesOrderID. Resultaten kommer att se annorlunda ut än om du vill att funktionen ska tillämpas i storleksordningen TotalDue i fallande ordning., Här är exemplet:
|
1
2
3
4
5
6
7
8
9
10
11
|
använd adventureworks2017; –eller vilken version du har
gå
välj salesorderid,
totaldue,
row_number() över(order by salesorderid) som rownum
från försäljning.,SalesOrderHeader;
välj SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) som RowNum
från försäljning.SalesOrderHeader;
|
eftersom den första frågan använder klusternyckeln som alternativ, behövs ingen sortering.
den andra frågan har en dyr sorteringsoperation.,
ordern i over-klausulen är inte ansluten till ordern efter-klausulen som lagts till den övergripande frågan, vilket kan vara helt annorlunda., 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;
|
den klustrade indexnyckeln är SalesOrderID, men raderna måste först sorteras efter TotalDue i fallande ordning och sedan tillbaka till SalesOrderID. Ta en titt på exekveringsplanen:
partitionen efter klausul, som stöds men valfritt, för alla T-SQL-fönsterfunktioner orsakar också sortering. Det liknar, men inte precis, gruppen efter klausul för aggregerade frågor., 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;
|
exekveringsplanen visar bara en sorteringsoperation, en kombination av CustomerID och SalesOrderID.
det enda sättet att övervinna sorteringens prestanda är att skapa ett index specifikt för OVER-klausulen. I sin bok Microsoft SQL Server 2012 högpresterande T-SQL Använder Fönstret Funktioner, Itzik Ben-Gan rekommenderar POC index. POC står för (S)ARTITION AV, (O)GRÄNS AV, och (c)overing., Han rekommenderar att du lägger till några kolumner som används för filtrering före partitionen efter och ordning efter kolumner i nyckeln. Lägg sedan till några ytterligare kolumner som behövs för att skapa ett täckningsindex som inkluderade kolumner. Precis som allt annat måste du testa för att se hur ett sådant index påverkar din fråga och övergripande arbetsbelastning. Naturligtvis kan du inte lägga till ett index för varje fråga som du skriver, men om prestanda för en viss fråga som använder en fönsterfunktion är viktigt, kan du prova detta råd.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID, SalesOrderID)
inkludera (TotalDue);
|
När du omdirigerar frågan är sorteringsoperationen nu borta från exekveringsplanen:
inramning
enligt min mening är inramning det svåraste konceptet att förstå när man lär sig om T-SQL-fönsterfunktioner. Om du vill veta mer om syntaxen se Introduktion till T-SQL fönsterfunktioner., Inramning krävs för följande:
- fönster aggregat med ORDER by, används för att köra summor eller glidande medelvärden, till exempel
- FIRST_VALUE
- LAST_VALUE
lyckligtvis är inramning inte krävs för det mesta, men tyvärr är det lätt att hoppa över ramen och använda standard. Standardramen ligger alltid mellan obundet föregående och aktuell rad. Medan du kommer att få rätt resultat så länge ordningen efter alternativet består av en unik kolumn eller uppsättning kolumner, kommer du att se en prestanda träff.,id=”4b64f77d3b”>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
Ställ in statistik Io på;
gå
välj CustomerID,
salesorderid,
totaldue,
sum(totaldue) över(partition av CustomerID order by salesorderid)
som runningtotal
från försäljning.,SalesOrderHeader;
välj CustomerID,
SalesOrderID,
TotalDue,
SUM(TotalDue) över(PARTITION av CustomerID ORDER BY SalesOrderID
rader mellan OBOUNDED föregående och aktuell rad)
som RunningTotal
från försäljning.SalesOrderHeader;
|
resultaten är desamma, men resultatet är väldigt annorlunda. Tyvärr säger avrättningsplanen inte sanningen i det här fallet., Det rapporterar att varje fråga tog 50% av resurserna:
om du granskar statistikens Io-värden ser du skillnaden:
att använda rätt ram är ännu viktigare om din beställning efter alternativ inte är unik eller om du använder LAST_VALUE. I det här exemplet är order BY column OrderDate, men vissa kunder har placerat mer än en order på ett visst datum. När du inte anger ramen, eller använder intervall, behandlar funktionen matchande datum som en del av samma fönster.,”>
anledningen till avvikelsen är att intervallet ser data logiskt medan rader ser det positivt. Det finns två lösningar på detta problem. En är att se till att ordningen efter alternativ är unik. Det andra och viktigare alternativet är att alltid ange ramen där den stöds.
den andra platsen som inramning orsakar logiska problem är med LAST_VALUE., LAST_VALUE returnerar ett uttryck från den sista raden i ramen. Eftersom standardramen (intervall mellan obunden föregående och aktuell rad) bara går upp till den aktuella raden, är den sista raden i ramen den rad där beräkningen utförs.,
Windowaggregat
en av de mest praktiska funktionerna i T-SQL-fönsterfunktionerna är möjligheten att lägga till ett aggregerat uttryck till ett icke-strukturerat uttryck.aggregerad fråga. Tyvärr kan detta ofta fungera dåligt. För att se problemet måste du titta på statistik Io-resultaten där du kommer att se ett stort antal logiska läsningar., Mitt råd när du behöver returnera värden på olika granulariteter inom samma Fråga för ett stort antal rader är att använda en av de äldre teknikerna, till exempel ett vanligt tabelluttryck (CTE), temp-tabell eller till och med en variabel. Om det är möjligt att i förväg aggregera innan du använder fönstret aggregatet, det är ett annat alternativ.,div id=”6fb350a029″>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
välj salesorderid,
totaldue,
sum(totaldue) över() som överalltotal
från försäljning.,SalesOrderHeader
var år(OrderDate) =2013;
förklara @OverallTotal pengar;
välj @OverallTotal = SUM(TotalDue)
från försäljningen.SalesOrderHeader
WHERE YEAR(OrderDate) = 2013;
välj SalesOrderID,
TotalDue,
@OverallTotal som OverallTotal
från försäljningen.,SalesOrderHeader AS SOH
WHERE YEAR(OrderDate) = 2013;
|
den första frågan skannar bara tabellen en gång, men den har 28,823 logiska läser i en arbetstabell. Den andra metoden skannar tabellen två gånger, men det behöver inte arbetsbordet.
nästa exempel använder ett Windows-aggregat som används för ett aggregerat uttryck:
När fönsterfunktioner används i en aggregerad fråga måste uttrycket följa samma regler som klausulerna SELECT och ORDER BY., I det här fallet tillämpas fönsterfunktionen på SUM (TotalDue). Det ser ut som ett kapslat aggregat, men det är verkligen en fönsterfunktion som appliceras på ett aggregerat uttryck.
eftersom data har aggregerats innan fönsterfunktionen applicerades är prestandan bra:
det finns en mer intressant sak att veta om att använda fönsteraggregat. Om du använder flera uttryck som använder matchande över klausuldefinitioner kommer du inte att se någon ytterligare försämring av prestanda.
mitt råd är att använda denna funktion med försiktighet., Det är ganska praktiskt men skalar inte så bra.
Prestandajämförelser
de exempel som hittills presenterats har använt den lilla försäljningen.SalesOrderHeader-tabellen från AdventureWorks och granskade exekveringsplanerna och logiska läsningar. I verkliga livet, dina kunder kommer inte att bry sig om avrättningsplanen eller den logiska läser; de kommer att bry sig om hur snabbt frågorna kör. För att bättre se skillnaden i körtider använde jag Adam Machanics tänkande stora (äventyr) skript med en vridning.
skriptet skapar en tabell som heter bigTransactionHistory som innehåller över 30 miljoner rader., Efter att ha kört Adams manus skapade jag ytterligare två kopior av hans bord, med 15 respektive 7,5 miljoner rader. Jag slog också på Borttagningsresultaten efter exekveringsegenskapen i Frågeredigeraren så att det inte påverkade körtiderna att fylla i rutnätet. Jag körde varje test tre gånger och rensade buffertcachen före varje körning.,
I can’t say enough about how important it is to use the frame when it’s supported., För att se skillnaden körde jag ett test för att beräkna löpande totaler med fyra metoder:
- Markörlösning
- korrelerad subfråga
- fönsterfunktion med standardram
- fönsterfunktion med rader
jag körde testet på de tre nya tabellerna. Här är resultaten i ett diagramformat:
När du kör med RADRAMEN tog tabellen 7,5 miljoner rader mindre än en sekund att köra på det system jag använde när du utförde testet. 30 miljoner Row bordet tog ungefär en minut att köra., 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., I det här fallet använde jag bara 30 miljoner radtabellen, men utförde en, två eller tre beräkningar med samma granularitet och därför samma överklausul. Jag jämförde fönstret aggregerade prestanda till en CTE och till en korrelerad subquery.
fönsterenheten utförde det värsta, ungefär 1,75 minuter i varje enskilt fall. CTE utförde det bästa när man ökade antalet beräkningar eftersom tabellen bara berördes en gång för alla tre., Den korrelerade subquery utförs sämre när man ökar antalet beräkningar eftersom varje beräkning var tvungen att köra separat, och det rörde vid tabellen totalt fyra gånger.,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
från bigTransactionHistory som O
gå med i Calcs på O. ProductID = Calcs.
|
slutsats
T-SQL fönsterfunktioner har främjats som bra för prestanda. Enligt min mening gör de skrivfrågor enklare, men du måste förstå dem bra för att få bra prestanda. Indexering kan göra skillnad, men du kan inte skapa ett index för varje fråga du skriver., Inramning kan inte vara lätt att förstå, men det är så viktigt om du behöver skala upp till stora bord.