Redgate Hub (Čeština)
funkce okna T-SQL usnadňují psaní mnoha dotazů a často poskytují lepší výkon než starší techniky. Například použití funkce LAG je mnohem lepší než samostatné připojení. Získat lepší výkon celkově, nicméně, musíte pochopit koncept rámování a jak okno funkce spoléhat na třídění poskytnout výsledky.
poznámka: podívejte se na můj nový článek, kde se dozvíte, jak zlepšení optimalizátoru v roce 2019 ovlivňují výkon!,
klauzule OVER a třídění
v klauzuli OVER jsou dvě možnosti, které mohou způsobit třídění: rozdělení podle a pořadí podle. Oddíl by je podporován všemi funkcemi okna, ale je to volitelné. Pořadí podle Je vyžadováno pro většinu funkcí. V závislosti na tom, co se snažíte dosáhnout, budou data seřazena na základě VÍCE ustanovení, a to by mohlo být místo výkonu dotazu.
příkaz podle volby v klauzuli OVER je vyžadován, aby databázový motor mohl řadit řádky, abych tak řekl, za účelem uplatnění funkce ve správném pořadí., Řekněme například, že chcete, aby funkce ROW_NUMBER byla použita v pořadí SalesOrderID. Výsledky budou vypadat jinak, než pokud chcete funkci použít v pořadí TotalDue v sestupném pořadí., Zde je příklad:
|
1
2
3
4
5
6
7
8
9
10
11
|
POUŽÍT AdventureWorks2017; … nebo podle toho, co verzi máte
VYBERTE SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY SalesOrderID) JAKO RowNum
Z Prodeje.,SalesOrderHeader;
VYBERTE SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) JAKO RowNum
Z Prodeje.SalesOrderHeader;
|
Od prvního dotazu je pomocí clusteru klíč jako volba seřadit PODLE, bez třídění je nutné.
druhý dotaz má nákladnou operaci řazení.,
objednávka v klauzuli OVER není připojena k objednávce klauzulí přidanou k celkovému dotazu, který by mohl být zcela odlišný., 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
OBJEDNÁVKY O SalesOrderID;
|
seskupený index klíče je SalesOrderID, ale řádky musí být nejprve řazena dle TotalDue v sestupném pořadí, a pak zpět do SalesOrderID. Podívejte se na plán provádění:
oddíl podle klauzule, podporovaný, ale volitelný, pro všechny funkce okna T-SQL také způsobuje třídění. Je to podobné, ale ne přesně jako, skupina podle klauzule pro agregované dotazy., 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;
|
plán vykonání ukazuje jen jeden druh operace, kombinace Kódzákazníka a SalesOrderID.
jediným způsobem, jak překonat dopad třídění na výkon, je vytvořit index speciálně pro klauzuli OVER. Ve své knize Microsoft SQL Server 2012 vysoce výkonný T-SQL pomocí funkcí okna doporučuje Itzik Ben-Gan index POC. POC znamená (P)ARTITION BY, (O)rder BY, A (c)overing., Doporučuje přidat všechny sloupce používané pro filtrování před oddíl a pořadí podle sloupců v klíči. Poté přidejte další sloupce potřebné k vytvoření krycího indexu jako zahrnutých sloupců. Stejně jako cokoli jiného, budete muset vyzkoušet, jak takový index ovlivňuje váš dotaz a celkovou pracovní zátěž. Samozřejmě nemůžete přidat index pro každý dotaz, který píšete, ale pokud je důležitý výkon konkrétního dotazu, který používá funkci okna, můžete tuto radu vyzkoušet.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(Kódzákazníka, SalesOrderID)
PATŘÍ (TotalDue);
|
Při opakování dotazu, druh operace je nyní pryč od provedení plánu:
Rámování
podle mého názoru, rámování je nejvíce obtížné koncept pochopit, když se učí o T-SQL funkcí okna. Další informace o syntaxi naleznete v úvodu k funkcím okna T-SQL., Rámování je nutné pro následující:
- Okno agregátů s CÍLEM, který se používá pro běh součty nebo klouzavé průměry, například
- FIRST_VALUE
- LAST_VALUE
Naštěstí, rámování není nutné většinu času, ale bohužel, je snadné přeskočit rámu a použít výchozí. Výchozí rámeček je vždy v rozmezí mezi neohraničeným předcházejícím a aktuálním řádkem. I když získáte správné výsledky, pokud se objednávka podle volby skládá z jedinečného sloupce nebo sady sloupců, uvidíte hit výkonu.,id=“4b64f77d3b“>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SET STATISTICS IO ON;
VYBERTE Kódzákazníka
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION Kódzákazníka OBJEDNÁVKY O SalesOrderID)
JAKO RunningTotal
Z Prodeje.,SalesOrderHeader;
VYBERTE Kódzákazníka
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION Kódzákazníka OBJEDNÁVKY O SalesOrderID
MEZI ŘÁDKY NESPOUTANÝ PŘEDCHOZÍ A AKTUÁLNÍ ŘÁDEK)
JAKO RunningTotal
Z Prodeje.SalesOrderHeader;
|
výsledky jsou stejné, ale výkon je velmi odlišná. Bohužel, plán popravy vám v tomto případě neříká pravdu., Uvádí, že každý dotaz vzal 50% zdrojů:
Pokud se vám recenze IO statistické hodnoty, uvidíte ten rozdíl:
Pomocí správného rámu je ještě více důležité, pokud vaše OBJEDNÁVKY možnost není jedinečný nebo pokud používáte LAST_VALUE. V tomto příkladu je pořadí podle sloupcedatum, ale někteří zákazníci zadali k danému datu více než jednu objednávku. Při neuvedení rámce, nebo pomocí rozsahu, funkce zachází odpovídající data jako součást stejného okna.,“>
důvodem pro rozpor je, že ROZSAH vidí data logicky, zatímco ŘÁDKY to vidí polohách. Pro tento problém existují dvě řešení. Jedním z nich je zajistit, aby objednávka podle volby byla jedinečná. Druhou a důležitější možností je vždy určit rámeček, kde je podporován.
druhé místo, které rámování způsobuje logické problémy, je s LAST_VALUE., LAST_VALUE vrací výraz z posledního řádku rámečku. Vzhledem k tomu, výchozí rámec (ROZSAH MEZI NESPOUTANÝ PŘEDCHOZÍ A AKTUÁLNÍ ŘÁDEK) jde pouze na aktuálním řádku, poslední řádek rámu je řádek, kde se výpočet provádí.,
Okno Agregáty
Jeden z handiest funkcí T-SQL okno funkcí je možnost přidat souhrnný výraz pro non-souhrnný dotaz. Bohužel to může často fungovat špatně. Chcete-li problém vidět, musíte se podívat na výsledky statistik IO, kde uvidíte velké množství logických čtení., Moje rada, když budete muset vrátit hodnoty v různých granularities ve stejný dotaz pro velký počet řádků je použít jeden ze starších technik, jako běžné tabulky výraz (CTE), temp tabulky, nebo dokonce variabilní. Pokud je možné před použitím agregátu okna předběžně agregovat, je to další možnost.,ows rozdíl mezi oknem agregát a další technika:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
VYBERTE SalesOrderID,
TotalDue,
SUM(TotalDue) OVER() JAKO OverallTotal
Z Prodeje.,SalesOrderHeader
, KDE ROK(Datumobjednávky) =2013;
DECLARE @OverallTotal PENÍZE;
SELECT @OverallTotal = SUM(TotalDue)
Z Prodeje.SalesOrderHeader
, KDE ROK(Datumobjednávky) = 2013;
VYBERTE SalesOrderID,
TotalDue,
@OverallTotal JAKO OverallTotal
Z Prodeje.,SalesOrderHeader JAKO SOH
, KDE ROK(Datumobjednávky) = 2013;
|
první dotaz pouze skenuje stolu jednou, ale to má 28,823 logické čte v pracovním stole. Druhá metoda skenuje tabulku dvakrát, ale nepotřebuje pracovní stůl.
další příklad používá systém windows agregát použít pro souhrnné vyjádření:
Při použití funkcí okna v souhrnném dotazu, výraz musí dodržovat stejná pravidla jako SELECT a ORDER BY klauzule., V tomto případě se funkce okna použije na SUM (TotalDue). Vypadá to jako vnořený agregát, ale je to opravdu funkce okna aplikovaná na souhrnný výraz.
Protože údaje byly agregovány před okno funkce byla použita, výkon je dobrý:
je Tu ještě jedna zajímavá věc, vědět o použití okna agregáty. Používáte-li více výrazů, které používají shodu nad definicemi klauzulí, neuvidíte další degradaci výkonu.
moje rada je používat tuto funkci s opatrností., Je to docela šikovný, ale není měřítko, že dobře.
porovnání výkonu
dosud uvedené příklady použily malý prodej.SalesOrderHeader tabulka od AdventureWorks a přezkoumala plány provádění a logické čtení. V reálném životě se vaši zákazníci nebudou starat o plán provádění nebo logické čtení; budou se starat o to, jak rychle běží dotazy. Abych lépe viděl rozdíl v době běhu, použil jsem scénář Adama Machanica Thinking Big (Adventure) s twistem.
skript vytvoří tabulku s názvem bigTransactionHistory obsahující více než 30 milionů řádků., Po spuštění Adamova scénáře jsem vytvořil další dvě kopie jeho stolu, s 15 a 7, 5 miliony řádků. Také jsem zapnul výsledky vyřazení po provedení vlastnosti v editoru dotazů, takže vyplnění mřížky neovlivnilo dobu běhu. Běžel jsem každý test třikrát a vymazal vyrovnávací paměť před každým spuštěním.,
I can’t say enough about how important it is to use the frame when it’s supported., Vidět rozdíl, provedl jsem test na výpočet běží součty pomocí čtyř metod:
- Kurzor řešení
- Korelovaných sub-query
- funkce Okna s default rám
- funkce Okna s ŘÁDKY
jsem běžel test na tři nové tabulky. Zde jsou výsledky v grafu formát:
Když běží s ŘÁDKY rám, 7,5 milionu řádek tabulky trvalo méně než sekundu ke spuštění v systému jsem používal při provádění testu. Tabulka 30 milionů řádků trvala asi jednu minutu., 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., V tomto případě jsem použil pouze tabulku 30 milionů řádků, ale provedl jsem jeden, dva nebo tři výpočty pomocí stejné zrnitosti, a tedy stejné klauzule. Porovnal jsem celkový výkon okna s CTE a s korelovanou subquery.
okno agregát provedl nejhorší, v každém případě asi 1,75 minuty. CTE provedl to nejlepší při zvyšování počtu výpočtů, protože tabulka byla jen jednou dotkl pro všechny tři., Ten korelovaný poddotaz provádí horší, když se zvyšuje počet výpočtů, protože každý výpočet musel běžet samostatně, a to se dotkla stolu celkem čtyřikrát.,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
OD bigTransactionHistory JAKO O
PŘIPOJIT Výpočty NA O. ProductID = Výpočty.ProductID;
|
Závěr
T-SQL okno funkce byly inzerovány jako skvělý výkon. Podle mého názoru usnadňují psaní dotazů, ale musíte je dobře pochopit, abyste získali dobrý výkon. Indexování může změnit, ale nemůžete vytvořit index pro každý dotaz, který píšete., Rámování nemusí být snadné pochopit, ale je to tak důležité, pokud potřebujete zvětšit až na velké tabulky.