Redgate Hub (Polski)
funkcje okna T-SQL ułatwiają pisanie wielu zapytań i często zapewniają lepszą wydajność w porównaniu ze starszymi technikami. Na przykład korzystanie z funkcji LAG jest o wiele lepsze niż samoczynne łączenie. Aby jednak uzyskać lepszą ogólną wydajność, musisz zrozumieć koncepcję kadrowania i sposób, w jaki funkcje okna polegają na sortowaniu, aby zapewnić wyniki.
Uwaga: Zobacz mój nowy artykuł, aby dowiedzieć się, jak ulepszenia optymalizatora w 2019 wpływają na wydajność!,
klauzula OVER I SORTOWANIE
w klauzuli OVER są dwie opcje, które mogą powodować sortowanie: partycja Według i kolejność według. Partycja By jest obsługiwana przez wszystkie funkcje okna, ale jest opcjonalna. ORDER BY jest wymagany dla większości funkcji. W zależności od tego, co próbujesz osiągnąć, dane będą sortowane na podstawie klauzuli OVER, co może być wąskim gardłem wydajności Twojego zapytania.
opcja ORDER BY w klauzuli OVER jest wymagana, aby silnik bazy danych mógł ustawiać wiersze, że tak powiem, w celu zastosowania funkcji we właściwej kolejności., Na przykład, powiedzmy, że chcesz, aby funkcja ROW_NUMBER była zastosowana w kolejności SalesOrderID. Wyniki będą wyglądać inaczej niż w przypadku, gdy chcesz, aby funkcja została zastosowana w kolejności TotalDue w porządku malejącym., Oto przykład:
|
1
2
3
4
5
6
7
8
9
10
11
|
UŻYJ AdventureWorks2017; –lub którąkolwiek wersję wybierzesz
GO
SELECT SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNum
FROM Sales.,SalesOrderHeader;
SELECT SALESORDERID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) as RowNum
FROM Sales.SalesOrderHeader;
|
ponieważ pierwsze zapytanie używa klucza klastra jako opcji ORDER BY, sortowanie nie jest konieczne.
drugie zapytanie ma kosztowną operację sortowania.,
klauzula ORDER BY in the OVER nie jest połączona z klauzulą ORDER BY dodaną do ogólnego zapytania, która może być zupełnie inna., 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
zamów za pomocą SALESORDERID;
|
klucz indeksu klastra to salesorderid, ale wiersze muszą być najpierw sortowane według totaldue w kolejności malejącej, a następnie z powrotem do salesorderid. Spójrz na plan wykonania:
klauzula PARTITION BY, obsługiwana, ale opcjonalna, dla wszystkich funkcji okna T-SQL również powoduje sortowanie. Jest podobna do klauzuli GROUP BY dla zapytań zbiorczych, ale nie do końca., 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;
|
plan realizacji pokazuje tylko jedną operację sortowania, kombinację CustomerID i salesorderid.
jedynym sposobem na pokonanie wpływu sortowania na wydajność jest utworzenie indeksu specjalnie dla klauzuli OVER. W swojej książce Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions Itzik Ben-Gan zaleca indeks POC. POC oznacza (P)ARTITION BY, (O)RDER BY I (c) overing., Zaleca dodawanie kolumn używanych do filtrowania przed partycją Według i kolejność według kolumn w kluczu. Następnie dodaj dodatkowe kolumny potrzebne do utworzenia indeksu pokrycia jako kolumny włączone. Podobnie jak Wszystko inne, będziesz musiał przetestować, aby zobaczyć, jak taki indeks wpływa na twoje zapytanie i ogólne obciążenie pracą. Oczywiście nie można dodać indeksu dla każdego zapytania, które piszesz, ale jeśli wydajność konkretnego zapytania używającego funkcji okna jest ważna, możesz wypróbować tę poradę.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID, SalesOrderID)
INCLUDE (TotalDue);
|
Po ponownym uruchomieniu zapytania operacja sortowania jest teraz wyłączona z planu wykonania:
kadrowanie
moim zdaniem kadrowanie jest najtrudniejszym pojęciem do zrozumienia podczas nauki o funkcjach okien T-SQL. Aby dowiedzieć się więcej o składni zobacz Wprowadzenie do funkcji okna T-SQL., Kadrowanie jest wymagane w następujących przypadkach:
- okno agreguje się w kolejności według, używane do uruchamiania wartości całkowitych lub średnich kroczących, na przykład
- FIRST_VALUE
- LAST_VALUE
na szczęście kadrowanie nie jest wymagane przez większość czasu, ale niestety, łatwo jest pominąć ramkę i użyć domyślnej. Domyślną ramką jest zawsze zakres między nieograniczonym poprzedzającym i bieżącym wierszem. Chociaż otrzymasz poprawne wyniki, o ile opcja ORDER BY składa się z unikalnej kolumny lub zestawu kolumn, zobaczysz hit wydajności.,id=”4b64f77d3b”>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
zainstalować statystyki na IO;
przejdź
Select Idklienta,
wartości salesorderid,
pole totaldue,
kwota(pole totaldue) nad(sekcja Idklienta wartości zamówienia salesorderid)
jak RunningTotal
od sprzedaży.,SalesOrderHeader;
SELECT CustomerID,
Salesorderid,
TotalDue,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SALESORDERID
wiersze między niepohamowanym poprzedzającym i bieżącym wierszem)
AS RunningTotal
FROM sprzedaż.SalesOrderHeader;
|
wyniki są takie same, ale wydajność jest bardzo inna. Niestety, Plan egzekucji nie mówi ci prawdy w tym przypadku., Raport informuje, że każde zapytanie zajęło 50% zasobów:
jeśli przejrzysz wartości IO statystyk, zobaczysz różnicę:
używanie poprawnej ramki jest jeszcze ważniejsze, jeśli opcja ORDER BY nie jest unikalna lub jeśli używasz LAST_VALUE. W tym przykładzie, ORDER BY column to OrderDate, ale niektórzy klienci złożyli więcej niż jedno zamówienie w danym dniu. Gdy nie podano ramki lub nie użyto zakresu, funkcja traktuje pasujące daty jako część tego samego okna.,”>
powodem rozbieżności jest to, że RANGE widzi dane logicznie, podczas gdy ROWS widzi je pozycyjnie. Istnieją dwa rozwiązania tego problemu. Jednym z nich jest upewnienie się, że opcja ORDER BY jest unikalna. Inną i ważniejszą opcją jest zawsze określenie ramki, w której jest obsługiwana.
innym miejscem, w którym kadrowanie powoduje problemy logiczne, jest last_value., Last_value zwraca wyrażenie z ostatniego wiersza ramki. Ponieważ domyślna ramka (zakres między nieograniczonym poprzedzającym i bieżącym wierszem) przechodzi tylko do bieżącego wiersza, ostatnim wierszem ramki jest wiersz, w którym wykonywane są obliczenia.,
Agregaty okienne
jedna z najbardziej przydatnych funkcji z funkcji okna T-SQL jest możliwość dodawania wyrażenia zbiorczego do zapytania Nie-zbiorczego. Niestety, często może to wykonać słabo. Aby zobaczyć problem, trzeba spojrzeć na statystyki IO wyniki, gdzie można zobaczyć dużą liczbę odczytów logicznych., Moja rada, gdy musisz zwrócić wartości o różnych ziarnistościach w ramach tego samego zapytania dla dużej liczby wierszy, polega na użyciu jednej ze starszych technik, takich jak common table expression (CTE), temp table, a nawet zmiennej. Jeśli możliwe jest wstępne agregowanie przed użyciem agregatu okna, jest to inna opcja.,różnica pomiędzy urządzeniem okien i innego sprzętu:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
div> 15
16
17
|
WYBIERZ SalesOrderID,
TotalDue,
SUM(TotalDue) OVER() JAK OverallTotal
Z Sales.,SalesOrderHeader
WHERE YEAR(OrderDate) =2013;
DECLARE @OverallTotal MONEY;
SELECT @OverallTotal = SUM (TotalDue)
FROM Sales.SalesOrderHeader
WHERE YEAR (OrderDate) = 2013;
SELECT SalesOrderID,
TotalDue,
@OverallTotal AS OverallTotal
FROM Sales.,SalesOrderHeader AS SOH
gdzie rok(OrderDate) = 2013;
|
pierwsze zapytanie skanuje tabelę tylko raz, ale ma 28 823 odczyty logiczne w tabeli roboczej. Druga metoda skanuje tabelę dwukrotnie, ale nie potrzebuje tabeli roboczej.
następny przykład używa agregatu windows zastosowanego do wyrażenia zbiorczego:
podczas korzystania z funkcji okna w zapytaniu zbiorczym wyrażenie musi spełniać te same zasady, co klauzule SELECT i ORDER BY., W tym przypadku funkcja okna jest zastosowana do SUM (TotalDue). Wygląda jak zagnieżdżony agregat, ale tak naprawdę jest to funkcja okna zastosowana do wyrażenia agregującego.
ponieważ dane zostały zagregowane przed zastosowaniem funkcji okna, wydajność jest dobra:
jest jeszcze jedna interesująca rzecz, którą należy wiedzieć o używaniu agregatów okien. Jeśli używasz wielu wyrażeń, które używają definicji dopasowania nad klauzulami, nie zobaczysz dodatkowego pogorszenia wydajności.
radzę ostrożnie korzystać z tej funkcjonalności., Jest dość poręczny, ale nie skaluje się tak dobrze.
porównania wydajności
w prezentowanych do tej pory przykładach wykorzystano małą sprzedaż.SalesOrderHeader table od AdventureWorks i przejrzał plany realizacji i odczyty logiczne. W rzeczywistości twoi klienci nie będą dbać o plan wykonania lub logiczne odczyty; będą dbać o to, jak szybko działają zapytania. Aby lepiej zobaczyć różnicę w czasie biegu, użyłem scenariusza Adama Machanica Thinking Big (Adventure)z akcentem.
skrypt tworzy tabelę o nazwie bigTransactionHistory zawierającą ponad 30 milionów wierszy., Po uruchomieniu skryptu Adama utworzyłem jeszcze dwie kopie jego tabeli, z odpowiednio 15 i 7,5 milionami wierszy. Włączyłem również właściwość Discard results after execution w Edytorze zapytań, aby wypełnianie siatki nie miało wpływu na czasy uruchomienia. Przeprowadziłem każdy test trzy razy i wyczyściłem bufor przed każdym uruchomieniem.,
I can’t say enough about how important it is to use the frame when it’s supported., Aby zobaczyć różnicę, przeprowadziłem test, aby obliczyć uruchomione sumy za pomocą czterech metod:
- rozwiązanie kursora
- skorelowane zapytanie podrzędne
- funkcja okna z domyślną ramką
- funkcja okna z wierszami
przeprowadziłem test na trzech nowych tabelach. Oto wyniki w formacie wykresu:
podczas uruchamiania z ramką ROWS, uruchomienie tabeli 7,5 miliona wierszy zajęło mniej niż sekundę w systemie, którego używałem podczas wykonywania testu. 30 mln row tabeli trwało około jednej minuty, aby uruchomić., 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., W tym przypadku użyłem tylko tabeli 30 milionów wierszy, ale wykonałem jeden, dwa lub trzy obliczenia przy użyciu tej samej ziarnistości, a zatem tej samej klauzuli OVER. Porównałem wydajność agregatu okna do CTE i skorelowanych zapytań podrzędnych.
najgorzej wypadło okno, około 1,75 minuty w każdym przypadku. CTE wykonał najlepiej przy zwiększaniu liczby obliczeń, ponieważ tabela została dotknięta tylko raz dla wszystkich trzech., Skorelowane zapytania podrzędne wypadały gorzej przy zwiększaniu liczby obliczeń, ponieważ każde obliczenia musiały przebiegać osobno i dotykały tabeli łącznie cztery razy.,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
z bigTransactionHistory jako o
JOIN Calcs ON O. ProductID = Calcs.ProductID;
|
podsumowanie
funkcje okna T-SQL zostały promowane jako doskonałe dla wydajności. Moim zdaniem ułatwiają pisanie zapytań, ale trzeba je dobrze zrozumieć, aby uzyskać dobrą wydajność. Indeksowanie może mieć znaczenie, ale nie można utworzyć indeksu dla każdego pisanego zapytania., Kadrowanie może nie być łatwe do zrozumienia, ale jest tak ważne, jeśli trzeba skalować do dużych tabel.