Porównanie wydajności między użyciem funkcji łączenia i okna w celu uzyskania wartości odprowadzeń i opóźnień

Mam tabeli z wierszy 20m, a każdy wiersz ma 3 kolumny: time, id, i value. Dla każdego idi timeistnieje valuestatus. Chcę poznać wartości wyprzedzenia i opóźnienia określonego timedla określonego id.

Użyłem dwóch metod, aby to osiągnąć. Jedną z metod jest łączenie, a drugą - funkcja okna lead / lag z indeksem klastrowym timei id.

Porównałem wydajność tych dwóch metod według czasu wykonania. Metoda łączenia zajmuje 16,3 sekundy, a metoda funkcji okna zajmuje 20 sekund, nie licząc czasu na utworzenie indeksu. Zaskoczyło mnie to, ponieważ funkcja okna wydaje się być zaawansowana, podczas gdy metody łączenia są brutalne.

Oto kod dwóch metod:

Utwórz indeks

create clustered index id_time
 on tab1 (id,time)

Metoda łączenia

select a1.id,a1.time
   a1.value as value, 
   b1.value as value_lag,
   c1.value as value_lead
into tab2
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.time-1= b1.time
left join tab1 c1
on a1.id = c1.id
and a1.time+1 = c1.time

Statystyki IO generowane przy użyciu SET STATISTICS TIME, IO ON:

Oto plan wykonania metody łączenia

Metoda funkcji okna

select id, time, value, 
   lag(value,1) over(partition by id order by id,time) as value_lag,
   lead(value,1) over(partition by id order by id,time) as value_lead
into tab2
from tab1

(Zamawianie tylko timeoszczędza 0,5 sekundy.)

Oto plan wykonania dla metody funkcji Windows

Statystyka IO

[ Statystyka dla metody funkcji okna 4]

Sprawdziłem dane sample_orig_month_1999i wydaje się, że nieprzetworzone dane są dobrze uporządkowane przez idi time. Czy to jest przyczyną różnicy wydajności?

Wydaje się, że metoda łączenia ma więcej logicznych odczytów niż metoda funkcji okna, podczas gdy czas wykonania dla pierwszej jest w rzeczywistości krótszy. Czy dlatego, że ten pierwszy ma lepszą równoległość?

Podoba mi się metoda funkcji okna ze względu na zwięzły kod, czy jest jakiś sposób na przyspieszenie tego konkretnego problemu?

Używam SQL Server 2016 na 64-bitowym systemie Windows 10.

— Jason
źródło

Relatywnie niska wydajność funkcji wiersza LEADi LAGfunkcji okna w porównaniu z samodzielnymi połączeniami nie jest niczym nowym. Na przykład Michael Zilberstein pisał o tym na stronie SQLblog.com w 2012 roku. Operatorzy planu (Segment, Projekt sekwencji, Buforowanie okna i Stream Aggregate) mają sporo narzutów:

W SQL Server 2016 masz nową opcję, która umożliwia przetwarzanie w trybie wsadowym dla agregatów okien. Wymaga to pewnego rodzaju indeksu magazynu kolumn w tabeli, nawet jeśli jest on pusty. Obecność indeksu magazynu kolumn jest obecnie wymagana, aby optymalizator rozważył plany trybu wsadowego. W szczególności umożliwia znacznie wydajniejszy operator trybu wsadowego Window Aggregate.

Aby to sprawdzić w Twoim przypadku, utwórz pusty nieklastrowany indeks magazynu kolumn:

 -- Empty CS index
CREATE NONCLUSTERED COLUMNSTORE INDEX dummy 
ON dbo.tab1 (id, [time], [value]) 
WHERE id < 0 AND id > 0;

Zapytanie:

SELECT
    T1.id,
    T1.[time],
    T1.[value],
    value_lag = 
        LAG(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time]),
    value_lead =
        LEAD(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time])
FROM dbo.tab1 AS T1;

Powinien teraz podać plan wykonania, taki jak:

... które mogą działać znacznie szybciej.

Może być konieczne użycie OPTION (MAXDOP 1)innej wskazówki, aby uzyskać ten sam kształt planu podczas przechowywania wyników w nowej tabeli. Wersja równoległa planu wymaga sortowania w trybie wsadowym (lub dwóch), co może być nieco wolniejsze. To zależy od twojego sprzętu.

Więcej informacji na temat operatora agregacji okna trybu wsadowego można znaleźć w następujących artykułach Itzika Ben-Gana:

— Paul White 9
źródło