Redgate Hub (Magyar)

a T-SQL ablakban funkciók, hogy az írás sok lekérdezések könnyebb, ők gyakran nyújtanak jobb teljesítményt, mint jóval több, mint a régebbi technikák. Például a LAG funkció használata sokkal jobb,mint az öncsatlakozás. Ahhoz azonban, hogy összességében jobb teljesítményt érjünk el, meg kell értenünk a keretezés fogalmát, valamint azt, hogy az ablakfunkciók hogyan támaszkodnak az eredmények rendezésére.

megjegyzés: Lásd az új cikkemet, hogy megtudja, hogyan befolyásolják az optimalizáló 2019-es javításai a teljesítményt!,

A Túlzáradék és a rendezés

a túlzáradékban két lehetőség van, amelyek rendezést okozhatnak:partíció BY and ORDER BY. Partíció által támogatja az összes ablak funkciók, de ez nem kötelező. A megrendelés a legtöbb funkcióhoz szükséges. Attól függően, hogy mit próbál elérni, az adatok alapján lesz rendezve A over záradék, ez lehet a teljesítmény szűk keresztmetszete a lekérdezés.

az OVER záradékban a megrendelés opció szerint szükséges, hogy az adatbázismotor sorba állíthassa a sorokat, hogy úgy mondjam, hogy a funkciót a megfelelő sorrendben alkalmazza., Tegyük fel például, hogy a Row_number funkciót a SalesOrderID sorrendjében szeretné alkalmazni. Az eredmények másképp néznek ki, mint ha azt szeretné, hogy a függvényt a TotalDue sorrendben csökkenő sorrendben alkalmazzák., Itt a példa:

mivel az első lekérdezés a cluster billentyűt használja a sorrend opció szerint, nincs szükség rendezésre.

a második lekérdezés drága rendezési művelettel rendelkezik.,

a fenti záradék sorrendje nem kapcsolódik az Általános lekérdezéshez hozzáadott klauzulához, amely egészen más lehet., Here is an example showing what happens if the two are different:

a fürtözött index kulcs SalesOrderID, de a sorokat először a következő sorrendben kell rendezni totaldue csökkenő sorrendben, majd vissza salesorderid. Vessen egy pillantást a végrehajtási tervre:

a partíció záradék szerint, támogatott, de opcionális, minden T-SQL ablakfunkcióhoz rendezést is okoz. Hasonló, de nem pontosan olyan, mint az összesített lekérdezések csoportonkénti záradéka., This example starts the row numbers over for each customer.

a végrehajtási terv csak egy rendezési műveletet, a CustomerID és a SalesOrderID kombinációját mutatja.

a rendezés teljesítményhatásának leküzdésének egyetlen módja egy index létrehozása kifejezetten a TÚLZÁRADÉKHOZ. A Microsoft SQL Server 2012 nagy teljesítményű T-SQL Ablakfunkciók használatával című könyvében Itzik Ben-Gan ajánlja a POC indexet. POC jelentése (P)ARTITION BY, (O)rder BY, és (c)overing., Azt javasolja, hogy adjon hozzá minden oszlopot, amelyet a partíció előtti szűréshez használnak a kulcs oszlopai szerint. Ezután adjon hozzá minden további oszlopot, amely szükséges a fedési index létrehozásához a mellékelt oszlopokként. Csakúgy, mint bármi más, meg kell, hogy teszteljék, hogy egy ilyen index hatással van a lekérdezés, valamint az Általános munkaterhelés. Természetesen nem adhat hozzá indexet minden írandó lekérdezéshez, de ha fontos egy ablakfunkciót használó lekérdezés teljesítménye, akkor kipróbálhatja ezt a tanácsot.,

Here is an index that will improve the previous query:

Ha ismétlés a lekérdezés, az a fajta működés, most már a végrehajtási terv:

Kialakítása

véleményem szerint, megkomponálására a legtöbb nehéz ezt megérteni, ha tanulás T-SQL ablakban funkciók. Ha többet szeretne megtudni a szintaxis lásd Bevezetés A T – SQL ablak funkciók., Keretezés szükséges a következő:

• ablak aggregátumok a sorrendben, futtatásához használt totals vagy mozgó átlagok, például
• FIRST_VALUE
• LAST_VALUE

szerencsére keretezés nem szükséges a legtöbb időt, de sajnos, ez könnyű kihagyni a keretet, és használja az alapértelmezett. Az alapértelmezett keret mindig az előző és az aktuális sor közötti hatótávolság. Amíg a helyes eredményeket kapja, mindaddig, amíg a megrendelés opció egy egyedi oszlopból vagy oszlopkészletből áll, megjelenik egy teljesítmény találat.,id=”4b64f77d3b”>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SET STATISZTIKÁK IO A; UGRÁS VÁLASSZA ki a CustomerID, SalesOrderID, TotalDue, SUM(TotalDue) VÉGE(PARTÍCIÓ ÁLTAL CustomerID RENDELÉS SalesOrderID) MINT RunningTotal A Értékesítés.,SalesOrderHeader; SELECT CustomerID, SalesOrderID, TotalDue, SUM(TotalDue) OVER(PARTITION by CustomerID OrderId by SalesOrderID sorok között nem kötött előző és aktuális sor) mint RunningTotal értékesítés.SalesOrderHeader;

Az eredmények azonosak, de a teljesítmény nagyon eltérő. Sajnos a végrehajtási terv ebben az esetben nem mondja el az igazságot., Azt jelenti, hogy minden lekérdezés az erőforrások 50% – át tette ki:

ha áttekinti a statisztikai IO értékeket, látni fogja a különbséget:

a megfelelő keret használata még fontosabb, ha a megrendelés opció szerint nem egyedi, vagy ha a LAST_VALUE-t használja. Ebben a példában a megrendelés oszloponként OrderDate, de egyes ügyfelek egynél több megrendelést helyeztek el egy adott időpontban. Ha nem adja meg a keretet, vagy a tartományt használja, a funkció ugyanazon ablak részeként kezeli a megfelelő dátumokat.,”>

az eltérés oka az, hogy a tartomány logikusan látja az adatokat, míg a sorok pozicionálisan látják. Két megoldás van erre a problémára. Az egyik az, hogy megbizonyosodjon arról, hogy a megrendelés opció szerint egyedi. A másik és még fontosabb lehetőség, hogy mindig megadjuk azt a keretet, ahol támogatott.

a másik hely, ahol a keretezés logikai problémákat okoz, a LAST_VALUE., A LAST_VALUE egy kifejezést ad vissza a keret utolsó sorából. Mivel az alapértelmezett keret (az előző és az aktuális sor közötti tartomány) csak az aktuális sorhoz megy fel, a keret utolsó sora az a sor, ahol a számítást végrehajtják.,

ablak aggregátumok

az egyik leghasznosabb a T-SQL ablakfunkciók jellemzője az a képesség, hogy aggregált kifejezést adjunk egy nem aggregált lekérdezéshez. Sajnos ez gyakran rosszul teljesít. A probléma megtekintéséhez meg kell nézni a statisztikai IO eredményeket, ahol nagyszámú logikai olvasást fog látni., Azt tanácsolom, hogy ha ugyanazon lekérdezésen belül különböző granularitásokkal kell értékeket visszaadni nagyszámú sor esetén, akkor az egyik régebbi technikát kell használni, például egy közös táblázatkifejezést (CTE), temp táblát vagy akár egy változót. Ha lehetséges, hogy előre aggregált használata előtt az ablak aggregátum, ez egy másik lehetőség.,foglald el a különbség a között, hogy az ablak összesített, valamint egy másik technika:

az első lekérdezés csak egyszer vizsgálja meg a táblázatot, de 28,823 logikai olvas egy munkaasztal. A második módszer kétszer beolvassa az asztalt, de nincs szüksége a munkaasztalra.

a következő példa egy összesített kifejezésre alkalmazott windows aggregátumot használ:

az ablakfüggvények összesített lekérdezésben történő használatakor a kifejezésnek ugyanazokat a szabályokat kell követnie, mint a Select and ORDER by clauses., Ebben az esetben az ablakfüggvény az összegre vonatkozik(TotalDue). Úgy néz ki, mint egy beágyazott aggregátum, de ez valójában egy ablakfüggvény, amelyet egy aggregált kifejezésre alkalmaznak.

mivel az adatokat az ablakfüggvény alkalmazása előtt összesítették, a teljesítmény jó:

van még egy érdekes dolog, amit tudni kell az ablak aggregátumok használatáról. Ha több kifejezést használ, amelyek a megfelelő záradék-meghatározásokat használják, akkor a teljesítmény további romlása nem jelenik meg.

azt tanácsolom, hogy ezt a funkciót óvatosan használja., Ez nagyon praktikus, de nem skálázza olyan jól.

teljesítmény-összehasonlítások

az eddig bemutatott példák a kis értékesítéseket használták.SalesOrderHeader táblázat AdventureWorks és áttekintette a végrehajtási tervek és logikai olvasás. A valós életben az ügyfelek nem törődnek a végrehajtási tervvel vagy a logikai olvasással; érdekli őket, hogy milyen gyorsan futnak a lekérdezések. Annak érdekében, hogy jobban láthassam a futási idők különbségét, Adam Machanic gondolkodó nagy (kaland) szkriptjét csavarral használtam.

a szkript létrehoz egy bigTransactionHistory nevű táblázatot, amely több mint 30 millió sort tartalmaz., Adam forgatókönyvének futtatása után még két példányt készítettem az asztaláról, 15, illetve 7, 5 millió sorral. A Lekérdezésszerkesztőben a végrehajtás után is bekapcsoltam az Elvetési eredményeket, így a rács feltöltése nem befolyásolta a futási időt. Minden tesztet háromszor lefuttattam, és minden futás előtt töröltem a puffer gyorsítótárat.,

I can’t say enough about how important it is to use the frame when it’s supported., Látom a különbséget, leteszteltem kiszámításához futó összesen a négy módszerek:

• Kurzor megoldás
• Összefügg al-lekérdezés
• Ablak funkció alapértelmezett keret
• Ablak funkció SOR

lefuttattam a tesztet a három új táblák. Itt vannak az eredmények táblázatos formában:

Ha fut a SOROK keret, a 7,5 millió sor asztal volt kevesebb, mint egy másodperc alatt fut a rendszer azt használja, ha a vizsgálat végrehajtása. A 30 millió soros asztal körülbelül egy percig tartott., 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., Ebben az esetben csak a 30 millió sortáblát használtam, de egy, kettő vagy három számítást végeztem ugyanazzal a szemcsemérettel, ezért ugyanaz a záradék. Összehasonlítottam az ablak összesített teljesítményét egy CTE-vel és egy Korrelált alkerettel.

az ablak összesített végzett a legrosszabb, körülbelül 1,75 perc minden esetben. A CTE a legjobb teljesítményt nyújtotta a számítások számának növelésekor, mivel a táblázatot csak egyszer érintette mindhárom esetében., A korrelált alkeretek rosszabbul teljesítettek a számítások számának növelésekor, mivel minden számításnak külön kellett futnia, és összesen négyszer érintette a táblázatot.,v>

következtetés

t-az SQL ablakfunkciók a teljesítmény szempontjából nagyszerűnek bizonyultak. Véleményem szerint megkönnyítik a lekérdezések írását, de meg kell értened őket, hogy jó teljesítményt kapj. Indexelés lehet, hogy a különbség, de nem lehet létrehozni egy index minden lekérdezés írsz., Lehet, hogy a keretezést nem könnyű megérteni, de annyira fontos, ha nagy asztalokra kell méretezni.

---