OFFSET FETCH w TSQL – czyli jak stronicować rezultat zapytania TSQL

Z każdą kolejną wersją TSQL jest wzbogacany o nowe funkcjonalności. Jedne z nich są długo oczekiwanymi nowościami (jak np. funkcje okna) inne z kolei przechodzą bez większego echa jak bohater dzisiejszego artykułu tj. funkcjonalność OFFSET FETCH pozwalająca  osiągnąć ciekawe rezultaty w bardzo prosty sposób. Funkcjonalność ta została wprowadzona w Denali czyli w SQL Server 2012, która to wersja była bardzo bogata w w nowości związane z językiem TSQL. Do czego służy ta funkcjonalność i jak jej użyć? Zapraszam do zapoznania się z niniejszym artykułem.

Nie owijając w bawełnę przejdźmy do konkretów. Przede wszystkim OFFSET jest rozszerzeniem klauzuli sortującej i pozwala na “stronicowanie” rezultatu. Co w tym przypadku oznacza stronicowanie? Chodzi o to, że możemy pobrać np. część rezultatu np. 10 pierwszych wierszy ze wszystkich 100 potem następne 10 itd. Tutaj od razu może pojawić się pytanie czy czegoś takiego nie robi przypadkiem TOP? Odpowiedź jest prosta – tak robi to! Jednakże OFFSET daje nam pewną dowolność. Wyobraźmy sobie, że chcemy wyświetlić 20 wierszy ze zbioru według określonego kryterium sortowania – osiągniemy to bardzo prosto używając popularnego TOP:

SELECT TOP 20
	I.InvoiceID,
	I.InvoiceDate,
	SUM(IL.Quantity) AS Quantity
FROM Sales.Invoices AS I
JOIN Sales.InvoiceLines AS IL
ON IL.InvoiceID=I.InvoiceID
GROUP BY 	I.InvoiceID,
	I.InvoiceDate
ORDER BY Quantity DESC

W rezultacie otrzymaliśmy pożądany wynik – co jednak gdy my chcemy zwrócić wiersze od 6 do 10 tak jak zostało to przedstawione poniżej:

Tutaj również z pomocą może nam przyjść TOP oraz podzapytanie:

SELECT TOP 5
	InvoiceID,
	InvoiceDate,
	Quantity
FROM
(
	SELECT TOP 10
		I.InvoiceID,
		I.InvoiceDate,
		SUM(IL.Quantity) AS Quantity
	FROM Sales.Invoices AS I
	JOIN Sales.InvoiceLines AS IL
	ON IL.InvoiceID=I.InvoiceID
	GROUP BY 	I.InvoiceID,
		I.InvoiceDate
	ORDER BY Quantity DESC
) AS InQuery
ORDER BY Quantity ASC

Powyższy wynik w jakimś stopniu może spełniać nasze oczekiwania, nie jest on jednak dokładny (widzimy, że poszczególne faktury nie są w odpowiedniej kolejności co może być dla nas nie do przyjęcia) i użycie podzapytania również nie jest czymś co chcielibyśmy zrobić przy tak prostym założeniu. Innym sposobem jest użycie funkcji okna, a dokładniej rzecz ujmując funkcji ROW_NUMBER:

SELECT
	InvoiceID,
	InvoiceDate,
	Quantity
FROM
(
	SELECT ROW_NUMBER() OVER(ORDER BY SUM(IL.QUANTITY) DESC) AS LP,
		I.InvoiceID,
		I.InvoiceDate,
		SUM(IL.Quantity) AS Quantity
	FROM Sales.Invoices AS I
	JOIN Sales.InvoiceLines AS IL
	ON IL.InvoiceID=I.InvoiceID
	GROUP BY 	I.InvoiceID,
		I.InvoiceDate
) AS InQuery
WHERE LP BETWEEN 6 AND 10

Otrzymany rezultat spełnia nasze oczekiwania i jest odpowiednio posortowany. Sam zapis zapytania według mnie jest dużo bardziej przystępny jednakże to tylko subiektywna opinia. To co może być dla nas interesujące dzieje się w planach wykonania obu zapytań – porównajmy je według estymatów:

Pierwsze zapytanie przynajmniej na papierze wydaje się być wydajniejsze! Ogólnie duża część operatorów działa w trybie batchowym jednakże nie zmienia to faktu, że funkcja okna daje tutaj nieco bardziej skomplikowany i kosztowniejszy plan.

Oprócz dwóch powyższych podejść możemy zastosować OFFSET FETCH:

	SELECT 
		I.InvoiceID,
		I.InvoiceDate,
		SUM(IL.Quantity) AS Quantity
	FROM Sales.Invoices AS I
	JOIN Sales.InvoiceLines AS IL
	ON IL.InvoiceID=I.InvoiceID
	GROUP BY 	I.InvoiceID,
		I.InvoiceDate
	ORDER BY Quantity DESC
	OFFSET 5 ROWS
	FETCH NEXT 5 ROWS ONLY

Warto zaznaczyć, że możemy zamiennie w powyższym zapisie używać ROWS i ROW oraz NEXT i FIRST – są one innym zapisem tej samej operacji. Jak to naprawdę działa? Pierwszym krokiem jest oczywiście posortowany rezultat – na tym zbiorze danych wykonywane jest przesunięcie czyli omijane jest tyle wierszy ile podamy w klauzuli OFFSET – na poniższym obrazku widzimy OFFSET 5 wierszy:

Na tym możemy poprzestać i zwrócone zostaną wszystkie pozostałe wiersze tj. pozostałe operatory takie jak FETCH nie są obligatoryjne. Jednakże jeśli podamy FETCH NEXT n ROWS ONLY to pobrane zostanie następne n wierszy – pozostałe zostaną odrzucone:

Wszystko wydaje się proste w działaniu i rzeczywiście takie jest! Spójrzmy na plan zapytania (użyjemy Plan Explorera aby łatwiej móc obserwować ilości wierszy):

Jak możecie zauważyć na powyższym planie z operatora Nested Loops wychodzi tylko 10 wierszy – potrzebujemy takiej liczby ponieważ chcemy 5 wierszy ominąć i kolejne 5 pobrać. W następnym kroku operator TOP odsiewa początkowe 5 wierszy i mamy nasz rezultat. Zastosowanie tutaj znajduje mechanizm ROW GOAL, który propaguje od lewej do prawej ile wierszy jest oczekiwane dzięki czemu całe przetwarzanie zakończyło się w momencie zwrócenia przez Nested Loops 10 wierszy.

Sprawdźmy potencjalną wydajność zapisu z OFFSET w porównaniu z pozostałymi zapytaniami:

Według planów estymownych zapis z OFFSET FETCH jest najmniej kosztowny – jednakże pamiętajmy, że to tylko estymaty. Patrząc na statystyki IO oraz czasowe odczuwalnej różnicy nie ma. Jednakże według mnie sam fakt eliminacji podzapytania sprawia, że nasze zapytanie jest dużo czytelniejsze.

Gdy chcemy używać omawianego rezultatu musimy pamiętać o kilku ograniczeniach tj.:

• nie ma możliwości użycia OFFSET bez ORDER BY
• nie ma możliwości użycia FETCH bez ORDER BY i OFFSET
• w momencie gdy używamy OFFSET nie możemy użyć TOP
• ilość wierszy w OFFSET lub FETCH może być wyrażeniem arytmetycznym, zmienną lub parametrem ale nie może być podzapytaniem zwracającym wartość skalarną

Odnieśmy się do ostatniego punktu i podstawmy coś dynamicznego pod liczbę wierszy przekazaną do OFFSET:

DECLARE @NoOfRowsToSkip INT=3
DECLARE @NoOfRowsToFetch INT=10

	SELECT 
		I.InvoiceID,
		I.InvoiceDate,
		SUM(IL.Quantity) AS Quantity
	FROM Sales.Invoices AS I
	JOIN Sales.InvoiceLines AS IL
	ON IL.InvoiceID=I.InvoiceID
	GROUP BY 	I.InvoiceID,
		I.InvoiceDate
	ORDER BY Quantity DESC
	OFFSET @NoOfRowsToSkip ROWS
	FETCH NEXT @NoOfRowsToFetch-2 ROWS ONLY

Otrzymany rezultat jest oczywiście zgodny z zamierzeniem:

W tym miejscu możecie zadać pytanie jakie jest zastosowanie tego zapisu? Zastosowań jest całkiem sporo, aplikacje które mają za zadanie wyświetlać dużą ilość wierszy mogą je doczytywać w trakcie przewijania – można w tym miejscu zastosować OFFSET FETCH. Wielostronicowe niestandardowe raporty również mogą polegać na tym właśnie mechanizmie. Pamiętajmy jednak, że jest to rozszerzenie ORDER BY dlatego też używajmy go z rozwagą bo wiemy jak wiele potrzeba aby posortować zbiór danych operatorem SORT. Jeżeli tylko możemy używajmy istniejących indeksów aby uniknąć dodatkowych operatorów sortowania. OFFSET warto rozważyć zawsze wtedy gdy chcemy pobrać podzbiór danych według określonego kryterium sortowania. Według moich testów nie ma ogólnej rady kiedy użyć jakiego podejścia – czasem funkcja okna jest dużo szybciej przetwarzana niż OFFSET i na odwrót, a bardzo często oba podejścia dają takie same rezultaty jeśli chodzi o wydajność – w takim przypadku wchodzi w grę inne kryterium jakim może być czytelność zapisu. Jedyne czego mogę odradzić to podzapytania i wielokrotne użycie ORDER BY i TOP ponieważ bardzo często to co otrzymamy może być niewłaściwe, a i wydajnościowo możemy strzelić sobie w stopę. Tak więc jeżeli wasze zapytanie działa wolno i filtrujecie po ROW_NUMBER – spróbujcie podejścia z OFFSET.