Redgate Hub (Dansk)
T-s .l vinduesfunktioner gør det lettere at skrive mange forespørgsler, og de giver ofte bedre ydeevne også over ældre teknikker. For eksempel er det så meget bedre at bruge LAG-funktionen end at gøre en selvforbindelse. For at få bedre ydeevne generelt skal du dog forstå begrebet indramning, og hvordan vinduesfunktioner er afhængige af sortering for at give resultaterne.BEMÆRK: Se min nye artikel for at lære, hvordan forbedringer af optimi !er i 2019 påvirker ydeevnen!,
Over-klausulen og sortering
Der er to muligheder i over-klausulen, der kan forårsage sortering: PARTITION efter og rækkefølge efter. PARTITION BY understøttes af alle vinduesfunktioner, men det er valgfrit. Ordren er påkrævet for de fleste funktioner. Afhængigt af hvad du forsøger at opnå, vil dataene blive sorteret baseret på over-klausulen, og det kan være præstationsflaskehalsen i din forespørgsel.
rækkefølgen efter valg i over-klausulen er påkrævet, så databasemotoren kan lineere rækkerne, så at sige, for at anvende funktionen i den rigtige rækkefølge., Sig for eksempel, at du vil have ro ._number-funktionen anvendt i rækkefølge af SalesOrderID. Resultaterne vil se anderledes ud, end hvis du vil have funktionen anvendt i rækkefølge af TotalDue i faldende rækkefølge., Her er et eksempel:
|
1
2
3
4
5
6
7
8
9
10
11
|
BRUG AdventureWorks2017; –eller en hvilken version du har
GO
VÆLG SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(bekendtgørelse AF SalesOrderID) SOM RowNum
FRA Salg.,SalesOrderHeader;
VÆLG SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(bekendtgørelse AF TotalDue DESC) SOM RowNum
FRA Salg.SalesOrderHeader;
|
Siden den første forespørgsel er ved hjælp af klynge-tasten FOR VED valg, ingen sortering nødvendig.
den anden forespørgsel har en dyr sorteringsoperation.,
ordren i over-klausulen er ikke forbundet med den rækkefølge, der tilføjes til den samlede forespørgsel, hvilket kunne være helt anderledes., 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
FOR AF SalesOrderID;
|
grupperet indeks vigtigste er SalesOrderID, men de rækker, skal først sorteres efter TotalDue i faldende rækkefølge, og derefter tilbage til SalesOrderID. Se på udførelsesplanen:
partition BY-klausulen, understøttet men valgfri, for alle t-s .l-vinduesfunktioner forårsager også sortering. Det ligner, men ikke nøjagtigt, gruppen efter klausul for samlede forespørgsler., 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;
|
execution plan viser bare en slags operation, er en kombination af Kunde-og SalesOrderID.
den eneste måde at overvinde præstationsvirkningen af sortering er at oprette et indeks specifikt for over-klausulen. I sin bog Microsoft s .l Server 2012 højtydende T-s .l ved hjælp af vinduesfunktioner anbefaler it .ik Ben-gan POC-indekset. POC står for (P)ARTITION BY, (O)RDER BY, og (C)overing., Han anbefaler at tilføje eventuelle kolonner, der bruges til filtrering før partitionen efter og rækkefølge efter kolonner i nøglen. Tilføj derefter yderligere kolonner, der er nødvendige for at oprette et dækkende indeks som inkluderede kolonner. Ligesom alt andet skal du teste for at se, hvordan et sådant indeks påvirker din forespørgsel og den samlede arbejdsbyrde. Selvfølgelig kan du ikke tilføje et indeks for hver forespørgsel, du skriver, men hvis udførelsen af en bestemt forespørgsel, der bruger en vinduesfunktion, er vigtig, kan du prøve dette råd.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(Kundeid, SalesOrderID)
OMFATTE (TotalDue);
|
Når du køre forespørgslen, den slags operation der er nu gået fra at gennemførelsen af planen:
Framing
efter min mening, udformning er den mest vanskeligt begreb at forstå, når man lærer om T-SQL-vinduet funktioner. Hvis du vil vide mere om syntaksen se Introduktion til T-s .l vindue funktioner., Framing er, der kræves til følgende:
- Vindue aggregater med den RÆKKEFØLGE, der anvendes til løbende totaler eller glidende gennemsnit, for eksempel
- FIRST_VALUE
- LAST_VALUE
Heldigvis, udformning er ikke påkrævet det meste af tiden, men desværre, det er nemt at springe rammen og bruge standard. Standard ramme er altid interval mellem ubundet foregående og aktuelle række. Mens du får de rigtige resultater, så længe ordren efter valgmulighed består af en unik kolonne eller et sæt kolonner, vil du se et præstationshit.,id=”4b64f77d3b”>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SÆT STATISTIKKER IO PÅ;
GO
VÆLG Kunde,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION, SOM Kunde, FOR VED SalesOrderID)
SOM RunningTotal
FRA Salg.,SalesOrderHeader;
VÆLG Kunde,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION, SOM Kunde, FOR VED SalesOrderID
RÆKKER MELLEM UNBOUNDED FOREGÅENDE OG AKTUELLE RÆKKE)
SOM RunningTotal
FRA Salg.SalesOrderHeader;
|
resultaterne er De samme, men resultaterne er meget forskellige. Desværre fortæller udførelsesplanen ikke sandheden i dette tilfælde., Det rapporterer, at hver forespørgsel tog 50% af de ressourcer, der er:
Hvis du gennemgår den statistik, IO-værdier, du vil se forskellen:
Brug den korrekte billede er endnu mere vigtigt, hvis din ORDRE VED indstilling er ikke enestående, eller hvis du bruger LAST_VALUE. I dette eksempel er ordren efter kolonne Ordredato, men nogle kunder har placeret mere end en ordre på en given dato. Når du ikke angiver rammen eller bruger rækkevidde, behandler funktionen matchende datoer som en del af det samme vindue.,”>
grunden til uoverensstemmelsen er, at området ser data logisk, mens RÆKKER ser det positionelt. Der er to løsninger på dette problem. Den ene er at sikre, at ordren efter valg er unik. Den anden og mere vigtige mulighed er altid at angive rammen, hvor den understøttes.det andet sted, hvor indramning forårsager logiske problemer, er med LAST_VALUE., LAST_VALUE returnerer et udtryk fra den sidste række af rammen. Da standardrammen (interval mellem ubundet foregående og nuværende række) kun går op til den aktuelle række, er den sidste række i rammen den række, hvor beregningen udføres.,
– Vinduet Aggregater
En af de smarteste træk i T-SQL-vinduet funktioner er muligheden for at tilføje et samlet udtryk for en ikke-samlede forespørgsel. Desværre kan dette ofte fungere dårligt. For at se problemet skal du se på statistikken IO-resultaterne, hvor du vil se et stort antal logiske læsninger., Mit råd, når du har brug for at returnere værdier ved forskellige granulariteter inden for den samme forespørgsel for et stort antal rækker, er at bruge en af de ældre teknikker, såsom et almindeligt tabeludtryk (CTE), temp-tabel eller endda en variabel. Hvis det er muligt at forhåndskonfigurere, før du bruger vinduesaggregatet, er det en anden mulighed.,ows forskellen mellem det samlede vindue og en anden teknik:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
VÆLG SalesOrderID,
TotalDue,
SUM(TotalDue) OVER (), SOM OverallTotal
FRA Salg.,SalesOrderHeader
, HVOR ÅR(OrderDate) =2013;
ERKLÆRE @OverallTotal PENGE;
VÆLG @OverallTotal = SUM(TotalDue)
FRA Salg.SalesOrderHeader
, HVOR ÅR(OrderDate) = 2013;
VÆLG SalesOrderID,
TotalDue,
@OverallTotal SOM OverallTotal
FRA Salg.,SalesOrderHeader SOM SOH
, HVOR ÅR(OrderDate) = 2013;
|
Den første forespørgsel scanner kun bordet én gang, men det har 28,823 logisk læser i et arbejdsbord. Den anden metode scanner tabellen to gange, men den behøver ikke arbejdsbordet.
Det næste eksempel bruger en windows samlede anvendes til en samlet udtryk:
Når du bruger vinduet funktioner i en samlet forespørgsel, udtrykket skal følge samme regler som den skal du VÆLGE og BESTILLE AF klausuler., I dette tilfælde anvendes vinduesfunktionen til SUM(TotalDue). Det ligner et indlejret aggregat, men det er virkelig en vinduesfunktion, der anvendes til et samlet udtryk.
Da data er blevet samlet, før vinduet funktionen blev anvendt, ydeevne er god:
Der er en interessant ting at vide om brug af vinduet aggregater. Hvis du bruger flere udtryk, der bruger matchende over definitioner af klausuler, vil du ikke se en yderligere forringelse i ydeevnen.
mit råd er at bruge denne funktionalitet med forsigtighed., Det er ganske praktisk, men skalerer ikke så godt.
Præstationssammenligninger
de hidtil præsenterede eksempler har brugt det lille Salg.SalesOrderHeader tabel fra Adventureworksorks og revideret udførelse planer og logiske læser. I det virkelige liv vil dine kunder ikke bekymre sig om eksekveringsplanen eller de logiske læsninger; de vil bekymre sig om, hvor hurtigt forespørgslerne kører. For bedre at se forskellen i køretider brugte jeg Adam Machanic ‘ s Thinking Big (Adventure) script med et T .ist.scriptet opretter en tabel kaldet bigTransactionHistory, der indeholder over 30 millioner rækker., Efter at have kørt Adams script lavede jeg to kopier af hans bord med henholdsvis 15 og 7,5 millioner rækker. Jeg tændte også Discard-resultaterne efter eksekveringsegenskaben i Forespørgselseditoren, så udfyldning af gitteret ikke påvirkede køretiderne. Jeg kørte hver test tre gange og ryddet buffer cache før hver kørsel.,
I can’t say enough about how important it is to use the frame when it’s supported., For at se forskellen, jeg kørte en test til at beregne kører totaler ved hjælp af fire metoder:
- Markøren løsning
- Korreleret sub-query
- Vindue funktion med standard ramme
- Vindue funktion med RÆKKER
jeg har kørt test på de tre nye tabeller. Her er resultaterne i et diagram format:
Når du kører med RÆKKER ramme, de 7,5 millioner række tabellen tog mindre end et sekund til at køre på det system, jeg bruger, når du udfører testen. 30 millioner rækkebordet tog omkring et minut at køre., 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 dette tilfælde brugte jeg bare 30 millioner rækkebordet, men udførte en, to eller tre beregninger ved hjælp af den samme granularitet og derfor samme over klausul. Jeg sammenlignede vinduet samlede ydeevne til en CTE og til en korreleret underforespørgsel.
vinduesaggregatet udførte det værste, cirka 1, 75 minutter i hvert tilfælde. CTE udført bedst, når øge antallet af beregninger, da tabellen blev bare rørt forn gang for alle tre., Den korrelerede underforespørgsel udføres værre, når øge antallet af beregninger, da hver beregning skulle køre separat, og det rørte bordet i alt fire gange.,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
FRA bigTransactionHistory SOM O
DELTAG Calcs PÅ O. ProductID = Calcs.ProductID;
|
Konklusion
T-SQL-vinduet funktioner er blevet fremmet som er fantastisk til ydeevne. Efter min mening gør de skriveforespørgsler lettere, men du er nødt til at forstå dem godt for at få god præstation. Indeksering kan gøre en forskel, men du kan ikke oprette et indeks for hver forespørgsel, du skriver., Indramning er muligvis ikke let at forstå, men det er så vigtigt, hvis du har brug for at skalere op til store borde.