OFFSET FETCH

OFFSET FETCH to składnia SQL służąca do stronicowania wyników, wymagająca klauzuli ORDER BY.

OFFSET FETCH jako mechanizm stronicowania wyników SQL

OFFSET FETCH jest składnią języka SQL używaną do pobierania fragmentu uporządkowanego zbioru danych. Dzięki niej można podzielić długą listę rekordów na strony, ograniczyć liczbę wierszy zwracanych przez zapytanie albo pobrać określony wycinek wyników. Takie rozwiązanie znajduje zastosowanie między innymi w listach produktów, tabelach administracyjnych, katalogach, historiach operacji oraz wynikach wyszukiwania.

Działanie tej składni opiera się na dwóch elementach. OFFSET określa, ile początkowych rekordów ma zostać pominiętych, natomiast FETCH wskazuje, ile kolejnych rekordów powinno zostać zwróconych. Przykładowo, zapytanie może pominąć pierwszych dwadzieścia wierszy i pobrać następne dziesięć. W praktyce odpowiada to wyświetleniu kolejnej strony listy, o ile rozmiar strony wynosi dziesięć rekordów.

Istotne jest, że OFFSET FETCH nie służy do przypadkowego wybierania danych. Wynik powinien być uporządkowany przy użyciu ORDER BY, ponieważ bez jednoznacznego porządku nie ma stabilnej odpowiedzi na pytanie, które rekordy znajdują się na początku, a które na dalszych pozycjach. Sama składnia ograniczająca liczbę wierszy nie zastępuje sortowania i nie gwarantuje powtarzalnych stron.

OFFSET FETCH, ORDER BY i znaczenie stabilnej kolejności

Najczęściej stosowany schemat ma postać zbliżoną do: SELECT kolumny FROM tabela ORDER BY data_utworzenia DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY. W takim przykładzie wynik zostaje posortowany malejąco według daty, pomijanych jest dwadzieścia pierwszych rekordów, a następnie zwracanych jest dziesięć kolejnych. Konkretne słowa kluczowe i wariant zapisu mogą zależeć od używanego systemu bazodanowego, dlatego przed wdrożeniem należy sprawdzić dokumentację danej platformy.

Warto odróżnić OFFSET od numeru strony. OFFSET jest liczbą wierszy pomijanych przed rozpoczęciem zwracania danych. Jeżeli rozmiar strony wynosi piętnaście, pierwsza strona ma przesunięcie równe zero, druga piętnaście, trzecia trzydzieści, a kolejna czterdzieści pięć. Zależność można zapisać jako: przesunięcie = numer strony pomniejszony o jeden, pomnożony przez rozmiar strony. W aplikacji numer strony nie powinien być bezpośrednio traktowany jako wartość OFFSET.

ORDER BY powinno wskazywać kolumnę lub zestaw kolumn, który zapewnia możliwie jednoznaczny porządek. Sortowanie wyłącznie po dacie może być niewystarczające, jeżeli wiele rekordów powstało w tej samej chwili. W takiej sytuacji przydatne jest dodanie drugiego kryterium, na przykład identyfikatora rekordu. Stabilny porządek ogranicza ryzyko powtórzenia wiersza na dwóch stronach albo pominięcia rekordu przy kolejnych odczytach.

OFFSET FETCH różni się od filtrowania warunkiem WHERE. WHERE zmniejsza zbiór na podstawie cech rekordu, takich jak status, zakres dat lub przypisana kategoria. OFFSET przesuwa pozycję w już uporządkowanym wyniku. Oba mechanizmy mogą być używane razem: najpierw wybiera się rekordy spełniające warunek, następnie ustala kolejność, a na końcu pobiera odpowiednią stronę.

OFFSET FETCH a typowe problemy wydajnościowe i logiczne

Przy niewielkich tabelach proste stronicowanie zwykle działa bez zauważalnych trudności. Wraz ze wzrostem liczby danych duże wartości OFFSET mogą jednak powodować dodatkową pracę po stronie bazy. Aby zwrócić rekordy z odległej strony, silnik może najpierw odnaleźć i uporządkować także wiele wcześniejszych wierszy, a dopiero potem je odrzucić. Im większe przesunięcie, tym mniej korzystne może być takie podejście.

Znaczenie ma także indeksowanie. Kolumny wykorzystywane w ORDER BY powinny być przeanalizowane pod kątem planu wykonania zapytania, zwłaszcza gdy jednocześnie stosowany jest WHERE. Brak właściwego indeksu może prowadzić do pełnego odczytu tabeli, kosztownego sortowania albo długiego czasu oczekiwania. Samo dodanie OFFSET FETCH nie poprawia wydajności automatycznie.

Drugim częstym problemem jest zmienność danych pomiędzy żądaniami. Jeżeli ktoś doda, usunie lub zmodyfikuje rekord między pobraniem pierwszej i drugiej strony, pozycje w wyniku mogą się przesunąć. W konsekwencji jeden wiersz może pojawić się ponownie, a inny może nie zostać pokazany. Ryzyko jest większe przy sortowaniu po polu, które często się zmienia, albo przy długim przeglądaniu listy.

W systemach, w których istotna jest spójność odczytu, można rozważyć odpowiednią izolację transakcji, kontrolę wersji danych albo przechowywanie warunków, według których rozpoczęto przeglądanie. Nie zawsze konieczne jest blokowanie tabeli. Ważne jest natomiast określenie, czy użytkownik oczekuje migawki danych, czy tylko orientacyjnego przeglądu aktualnej listy.

Warto również zweryfikować wartości wejściowe. Ujemny OFFSET, zerowy lub nadmiernie wysoki rozmiar strony oraz niepoprawnie zbudowane ORDER BY mogą prowadzić do błędów albo nieoczekiwanych rezultatów. Parametry powinny być sprawdzane po stronie aplikacji, a nazwy kolumn i kierunki sortowania nie powinny być bezpośrednio sklejane z niesprawdzonym tekstem wprowadzonym przez użytkownika.

OFFSET FETCH i porównanie z innymi metodami paginacji

Najprostszą alternatywą dla OFFSET FETCH jest ograniczenie liczby zwracanych wierszy. Takie rozwiązanie przydaje się przy pobieraniu pierwszej części wyniku, ale nie wystarcza do wygodnego przechodzenia między dalszymi stronami. OFFSET FETCH daje czytelny sposób wyrażenia przesunięcia i rozmiaru fragmentu, dlatego dobrze pasuje do klasycznej paginacji opartej na numerze strony.

Innym podejściem jest paginacja kursorowa, nazywana również stronicowaniem według klucza. Zamiast pomijać określoną liczbę wierszy, zapytanie pobiera rekordy znajdujące się za ostatnim elementem poprzedniej strony, na przykład za konkretną datą i identyfikatorem. Taka metoda może być korzystniejsza przy bardzo dużych zbiorach oraz przy ciągłym przewijaniu, ponieważ nie wymaga przechodzenia przez wszystkie wcześniejsze rekordy.

Stronicowanie według klucza wymaga jednak jednoznacznego sortowania i przekazania wartości granicznej z poprzedniej odpowiedzi. Jest mniej intuicyjne, gdy potrzebne są przyciski przejścia bezpośrednio do odległej strony. OFFSET FETCH pozostaje wtedy wygodniejszy, szczególnie w raportach, panelach i prostych zestawieniach.

Nie należy też mylić ograniczania wyniku z filtrowaniem po numerze wiersza. Konstrukcje oparte na funkcjach okienkowych mogą zapewnić podobny efekt i bywają przydatne w bardziej złożonych zapytaniach, na przykład wtedy, gdy paginacja ma być połączona z rankingiem, grupowaniem lub wyborem jednego rekordu z każdej grupy. Wybór metody powinien wynikać z rozmiaru danych, oczekiwanej spójności oraz sposobu poruszania się po wynikach.

OFFSET FETCH w praktyce: wskazówki dla aplikacji i diagnostyki

Przy projektowaniu paginacji warto najpierw ustalić stały rozmiar strony, a następnie konsekwentnie przeliczać numer strony na OFFSET. Dobrą praktyką jest ograniczenie maksymalnego rozmiaru strony, aby pojedyncze żądanie nie zwracało nadmiernej ilości danych. Należy także zdecydować, czy odpowiedź ma zawierać całkowitą liczbę rekordów. Osobne zapytanie zliczające może być przydatne dla interfejsu, ale przy dużych tabelach również wymaga oceny kosztu.

W testach powinno się sprawdzać nie tylko pierwszą stronę. Należy przeanalizować strony początkowe, środkowe i odległe, a także przypadki braku wyników, ostatniej niepełnej strony i zmiany danych podczas przeglądania. Pomocne jest porównanie planu wykonania zapytania oraz czasu odpowiedzi dla różnych wartości OFFSET. Dzięki temu można rozpoznać moment, w którym klasyczna paginacja przestaje spełniać wymagania.

Jeżeli zapytanie działa wolno, warto sprawdzić kolejno: warunki WHERE, kolejność sortowania, indeksy, typy danych, liczbę zwracanych kolumn oraz aktualność statystyk. Szerokie rekordy, niepotrzebne pola i sortowanie po wyrażeniu mogą zwiększać koszt operacji. Nie należy zakładać, że samo zmniejszenie liczby zwracanych wierszy rozwiąże problem, ponieważ baza może nadal przetwarzać wiele rekordów przed zastosowaniem końcowego ograniczenia.

W przypadku trudności z działaniem programu, błędnym wyświetlaniem kolejnych stron albo niestabilnym czasem odpowiedzi przydatne jest zebranie informacji o używanej bazie, wersji aplikacji, przykładowym zapytaniu i zachowaniu problemu. W praktyce technicznej pomocne może być również sprawdzenie kondycji komputera, na którym uruchamiane są narzędzia bazodanowe. W Warszawie takie czynności wykonuje między innymi Warszawskie Pogotowie Komputerowe 24/7.