Redgate Hub (Română)
T-SQL window funcții face scris mai multe interogări mai ușor, și de multe ori oferă o performanță mai bună, precum și peste tehnici mai vechi. De exemplu, utilizarea funcției LAG este mult mai bună decât a face o auto-aderare. Cu toate acestea, pentru a obține o performanță mai bună în ansamblu, trebuie să înțelegeți conceptul de încadrare și modul în care funcțiile ferestrei se bazează pe sortare pentru a oferi rezultatele.notă: Consultați noul meu articol pentru a afla cum Îmbunătățirile aduse optimizatorului în 2019 afectează performanța!,
Clauza OVER și sortare
există două opțiuni în clauza OVER care pot cauza sortarea: PARTITION BY și ORDER BY. Partiția BY este acceptată de toate funcțiile ferestrei, dar este opțională. Comanda BY este necesară pentru majoritatea funcțiilor. În funcție de ceea ce încercați să realizați, datele vor fi sortate pe baza clauzei OVER și acesta ar putea fi blocajul de performanță al interogării dvs.
opțiunea Comandă după din clauza OVER este necesară pentru ca motorul bazei de date să poată alinia rândurile, ca să spunem așa, pentru a aplica funcția în ordinea corectă., De exemplu, spuneți că doriți ca funcția ROW_NUMBER să fie aplicată în ordinea SalesOrderID. Rezultatele vor arăta diferit decât dacă doriți funcția aplicată în ordinea TotalDue în ordine descrescătoare., Aici este un exemplu:
|
1
2
3
4
5
6
7
8
9
10
11
|
UTILIZAREA AdventureWorks2017; – sau oricare versiune aveți
GO
SELECTAȚI SalesOrderID,
TotalDue,
ROW_NUMĂR() OVER(ORDER BY SalesOrderID) CA RowNum
DIN Vânzări.,SalesOrderHeader;
SELECTAȚI SalesOrderID,
TotalDue,
ROW_NUMĂR() OVER(ORDER BY TotalDue DESC) CA RowNum
DIN Vânzări.SalesOrderHeader;
|
Din prima interogare este utilizarea de bord cheie ca ORDINE DE opțiune, nu de sortare este necesar.
a doua interogare are o operație de sortare scumpă.,
clauza ORDER BY in OVER Nu este conectată la clauza ORDER BY adăugată la interogarea generală, care ar putea fi destul de diferită., 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
COMANDA DE SalesOrderID;
|
cluster de index cheie este SalesOrderID, dar rândurile trebuie să fie sortată în funcție de TotalDue în ordine descrescătoare și apoi înapoi la SalesOrderID. Aruncați o privire la planul de execuție:
partiția după clauză, acceptată, dar opțională, pentru toate funcțiile ferestrei T-SQL determină și sortarea. Este similar cu, dar nu exact ca, grupul de clauza pentru interogări agregate., 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;
|
planul De execuție prezintă doar un singur fel operație, o combinație de CustomerID și SalesOrderID.
singura modalitate de a depăși impactul de performanță al sortării este de a crea un index special pentru clauza OVER. În cartea sa Microsoft SQL Server 2012 T-SQL de înaltă performanță folosind funcții de fereastră, Itzik Ben-Gan recomandă indicele POC. POC standuri pentru (P)ARTITION de, (O)RDER de, și (c)overing., El recomandă adăugarea oricăror coloane utilizate pentru filtrare înainte de partiție și ordine după coloane în cheie. Apoi adăugați orice coloane suplimentare necesare pentru a crea un index de acoperire ca coloane incluse. La fel ca orice altceva, va trebui să testați pentru a vedea cum un astfel de index influențează interogarea și volumul de muncă general. Desigur, nu puteți adăuga un index pentru fiecare interogare pe care o scrieți, dar dacă performanța unei anumite interogări care utilizează o funcție de fereastră este importantă, puteți încerca acest sfat.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID, SalesOrderID)
INCLUDE (TotalDue);
|
atunci Când rulați interogarea, la fel operațiune este acum plecat din planul de execuție:
Încadrare
În opinia mea, încadrarea este cel mai dificil concept pentru a înțelege atunci când procesul de învățare despre T-SQL fereastra de funcții. Pentru a afla mai multe despre sintaxa, consultați Introducere în funcțiile ferestrei T-SQL., Încadrarea este necesară pentru următoarele:
- Fereastra agregate cu SCOPUL de, a folosit pentru a rula totaluri sau medii mobile, de exemplu,
- FIRST_VALUE
- LAST_VALUE
din Fericire, încadrare nu este necesară în majoritatea timpului, dar, din păcate, este ușor pentru a sări peste cadru și de a folosi implicit. Cadrul implicit este întotdeauna interval între nelimitat precedent și rândul curent. În timp ce veți obține rezultatele corecte, atâta timp cât opțiunea Comandă după constă dintr-o coloană unică sau un set de coloane, veți vedea o lovitură de performanță.,id=”4b64f77d3b”>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SET STATISTICI IO PE;
GO
SELECTAȚI CustomerID,
SalesOrderID,
TotalDue,
SUMA(TotalDue) OVER(PARTITION BY CustomerID SCOPUL DE SalesOrderID)
CA RunningTotal
DIN Vânzări.,SalesOrderHeader;
SELECTAȚI CustomerID,
SalesOrderID,
TotalDue,
SUMA(TotalDue) OVER(PARTITION BY CustomerID SCOPUL DE SalesOrderID
RÂNDURI ÎNTRE NEMĂRGINITĂ PRECEDENT ȘI CURENT RÂND)
CA RunningTotal
DIN Vânzări.SalesOrderHeader;
|
rezultatele sunt aceleași, dar performanta este foarte diferit. Din păcate, planul de execuție nu vă spune adevărul în acest caz., Se raportează că fiecare interogare a luat 50% de resurse:
Dacă vă revizui statisticile IO valori, veți vedea diferența:
Utilizarea corectă cadru este chiar mai important în cazul în care COMANDA DE opțiune nu este unic sau dacă utilizați LAST_VALUE. În acest exemplu, comanda după coloană este OrderDate, dar unii clienți au plasat mai multe comenzi la o anumită dată. Când nu specificați cadrul sau nu utilizați intervalul, funcția tratează datele de potrivire ca parte a aceleiași ferestre.,”>
motivul pentru diferența este că GAMA vede de date în mod logic, în timp ce RÂNDURILE se vede pozițional. Există două soluții pentru această problemă. Unul este să vă asigurați că comanda prin opțiune este unică. Cealaltă și mai importantă opțiune este să specificați întotdeauna cadrul în care este acceptat.
celălalt loc în care framing cauzează probleme logice este cu LAST_VALUE., Last_value returnează o expresie din ultimul rând al cadrului. Deoarece cadrul implicit (intervalul dintre rândul precedent nelimitat și rândul curent) urcă doar la rândul curent, ultimul rând al cadrului este rândul în care se efectuează calculul.,
Fereastra Agregate
Una dintre cele mai la îndemână funcție de T-SQL fereastra de funcții este posibilitatea de a adăuga un agregat de expresie de la un non-agregat de interogare. Din păcate, acest lucru poate funcționa adesea prost. Pentru a vedea problema, trebuie să vă uitați la statisticile IO rezultate unde veți vedea un număr mare de lecturi logice., Sfatul meu atunci când trebuie să returnați valori la diferite granularități în cadrul aceleiași interogări pentru un număr mare de rânduri este să utilizați una dintre tehnicile mai vechi, cum ar fi o expresie comună a tabelului (CTE), o masă temp sau chiar o variabilă. Dacă este posibilă pre-agregarea înainte de a utiliza agregatul ferestrei, aceasta este o altă opțiune.,ows diferența între fereastră agregate și o altă tehnică:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECTAȚI SalesOrderID,
TotalDue,
SUMA(TotalDue) PESTE() CA OverallTotal
DIN Vânzări.,SalesOrderHeader
în cazul în CARE AN(OrderDate) =2013;
DECLAR @OverallTotal BANI;
SELECTAȚI @OverallTotal = SUM(TotalDue)
DIN Vânzări.SalesOrderHeader
în cazul în CARE AN(OrderDate) = 2013;
SELECTAȚI SalesOrderID,
TotalDue,
@OverallTotal CA OverallTotal
DIN Vânzări.,SalesOrderHeader CA SOH
în cazul în CARE AN(OrderDate) = 2013;
|
prima interogare doar scanează masă o dată, dar nu a 28,823 logică citește într-o masa de lucru. A doua metodă scanează tabelul de două ori, dar nu are nevoie de masa de lucru.
următorul exemplu folosește un windows agregate aplicat la un total de exprimare:
atunci Când se utilizează funcții fereastră într-un agregat de interogare, expresia trebuie să urmeze aceleași reguli ca și SELECTAȚI COMANDA PRIN clauze., În acest caz, funcția fereastră este aplicată la SUM(TotalDue). Arată ca un agregat imbricat, dar este într-adevăr o funcție de fereastră aplicată unei expresii agregate.deoarece datele au fost agregate înainte de aplicarea funcției window, performanța este bună:
există un lucru mai interesant de știut despre utilizarea agregatelor de ferestre. Dacă utilizați mai multe expresii care utilizează potrivirea peste definițiile clauzei, nu veți vedea o degradare suplimentară a performanței.sfatul meu este să folosiți această funcționalitate cu prudență., Este destul de la îndemână, dar nu se scalează atât de bine.
comparații de performanță
exemplele prezentate până acum au folosit vânzările mici.Tabelul SalesOrderHeader de la AdventureWorks și revizuite planurile de execuție și citește logice. În viața reală, clienților dvs. nu le va păsa de planul de execuție sau de citirile logice; le va păsa de cât de repede rulează interogările. Pentru a vedea mai bine diferența în timpii de rulare, am folosit scenariul de gândire mare (aventură) al lui Adam Machanic cu o răsucire.scriptul creează un tabel numit bigTransactionHistory care conține peste 30 de milioane de rânduri., După ce am rulat scenariul lui Adam, am creat încă două copii ale tabelului său, cu 15 și, respectiv, 7,5 milioane de rânduri. De asemenea, am activat proprietatea de eliminare a rezultatelor după execuție în Editorul de interogări, astfel încât popularea grilei să nu afecteze timpii de rulare. Am rulat fiecare test de trei ori și am șters memoria cache tampon înainte de fiecare rulare.,
I can’t say enough about how important it is to use the frame when it’s supported., Pentru a vedea diferența, am făcut un test pentru a calcula totalurile rulate folosind patru metode:
- Cursor solution
- sub-interogare corelată
- funcția fereastră cu cadru implicit
- funcția fereastră cu rânduri
am rulat testul pe cele trei tabele noi. Aici sunt rezultatele într-un format grafic:
atunci Când rulează cu RÂNDURI cadru, 7,5 milioane de rând masă a luat mai puțin de o secundă pentru a rula pe sistemul pe care îl folosea atunci când se efectuează testul. Tabelul de 30 de milioane de rânduri a durat aproximativ un minut pentru a rula., 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., În acest caz, am folosit doar tabelul de 30 de milioane de rânduri, dar am efectuat una, două sau trei calcule folosind aceeași granularitate și, prin urmare, aceeași clauză. Am comparat performanța agregată a ferestrei cu un CTE și cu un subquery corelat.
agregatul ferestrei a efectuat cel mai rău, aproximativ 1,75 minute în fiecare caz. CTE a efectuat cel mai bine la creșterea numărului de calcule, deoarece tabelul a fost atins doar o dată pentru toate cele trei., Subquery corelat efectuat mai rău atunci când creșterea numărului de calcule, deoarece fiecare calcul a trebuit să ruleze separat, și a atins tabelul un total de patru ori.,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
DE la bigTransactionHistory CA O
ALĂTURAȚI-vă Calcs PE O. ProductID = Calcs.ProductID;
|
concluzie
funcțiile ferestrei T-SQL au fost promovate ca fiind excelente pentru performanță. În opinia mea, ele ușurează interogările de scriere, dar trebuie să le înțelegeți bine pentru a obține performanțe bune. Indexarea poate face diferența, dar nu puteți crea un index pentru fiecare interogare pe care o scrieți., Încadrarea poate să nu fie ușor de înțeles, dar este atât de important dacă trebuie să vă extindeți la mese mari.