Dlaczego istnieją różnice w planie wykonania między OFFSET… FETCH a starym schematem ROW_NUMBER?

Nowy OFFSET ... FETCHmodel wprowadzony wraz z SQL Server 2012 oferuje proste i szybsze stronicowanie. Dlaczego w ogóle istnieją jakiekolwiek różnice, biorąc pod uwagę, że te dwie formy są semantycznie identyczne i bardzo powszechne?

Zakłada się, że optymalizator rozpoznaje oba i optymalizuje je (trywialnie) w pełni.

Oto bardzo prosty przypadek, w którym OFFSET ... FETCHwedług szacunków kosztów jest on ~ 2x szybszy.

SELECT * INTO #objects FROM sys.objects

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #objects
) x
WHERE r >= 30 AND r < (30 + 10)
    ORDER BY object_id

SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

Ten przypadek testowy można zmieniać, tworząc CI object_idlub dodając filtry, ale nie można usunąć wszystkich różnic w planie. OFFSET ... FETCHjest zawsze szybszy, ponieważ wykonuje mniej pracy w czasie wykonywania.

Przykłady w pytaniu nie dają takich samych wyników (w OFFSETprzykładzie występuje błąd „jeden po drugim”). Poniższe zaktualizowane formularze rozwiązują ten problem, usuwają dodatkowe sortowanie ROW_NUMBERsprawy i wykorzystują zmienne, aby rozwiązanie było bardziej ogólne:

DECLARE 
    @PageSize bigint = 10,
    @PageNumber integer = 3;

WITH Numbered AS
(
    SELECT TOP ((@PageNumber + 1) * @PageSize) 
        o.*,
        rn = ROW_NUMBER() OVER (
            ORDER BY o.[object_id])
    FROM #objects AS o
    ORDER BY 
        o.[object_id]
)
SELECT
    x.name,
    x.[object_id],
    x.principal_id,
    x.[schema_id],
    x.parent_object_id,
    x.[type],
    x.type_desc,
    x.create_date,
    x.modify_date,
    x.is_ms_shipped,
    x.is_published,
    x.is_schema_published
FROM Numbered AS x
WHERE
    x.rn >= @PageNumber * @PageSize
    AND x.rn < ((@PageNumber + 1) * @PageSize)
ORDER BY
    x.[object_id];

SELECT
    o.name,
    o.[object_id],
    o.principal_id,
    o.[schema_id],
    o.parent_object_id,
    o.[type],
    o.type_desc,
    o.create_date,
    o.modify_date,
    o.is_ms_shipped,
    o.is_published,
    o.is_schema_published
FROM #objects AS o
ORDER BY 
    o.[object_id]
    OFFSET @PageNumber * @PageSize - 1 ROWS 
    FETCH NEXT @PageSize ROWS ONLY;

ROW_NUMBERPlan ma szacunkowy koszt 0.0197935 :

OFFSETPlan ma szacunkowy koszt 0.0196955 :

Jest to oszczędność 0,000098 szacowanych jednostek kosztów (chociaż OFFSETplan wymagałby dodatkowych operatorów, jeśli chcesz zwrócić numer wiersza dla każdego wiersza). OFFSETPlan nadal będą nieco tańsze, ogólnie rzecz biorąc, ale pamiętam, że szacowane koszty są dokładnie tym - nadal wymagane jest prawdziwe testy. Większość kosztów w obu planach to koszt pełnego rodzaju zestawu danych wejściowych, więc pomocne indeksy byłyby korzystne dla obu rozwiązań.

Tam, gdzie stosowane są stałe wartości literalne (np. OFFSET 30W oryginalnym przykładzie), optymalizator może użyć sortowania TopN zamiast pełnego sortowania, po którym następuje Top. Gdy wiersze potrzebne w sortowaniu TopN są stałym literałem, a <= 100 (suma OFFSETi FETCH) silnik wykonawczy może użyć innego algorytmu sortowania, który może działać szybciej niż uogólnione sortowanie TopN. Wszystkie trzy przypadki mają ogólnie różne charakterystyki wydajności.

Powód, dla którego optymalizator nie przekształca automatycznie ROW_NUMBERwzorca składni OFFSET, jest kilka powodów:

1. Niemal niemożliwe jest napisanie transformacji, która pasowałaby do wszystkich istniejących zastosowań
2. Automatyczne przekształcanie niektórych zapytań stronicujących i brak innych może być mylące
3. OFFSETPlan nie gwarantuje się lepiej we wszystkich przypadkach

Jeden przykład dla trzeciego punktu powyżej występuje, gdy zestaw stronicowania jest dość szeroki. Znacznie bardziej wydajne może być wyszukiwanie potrzebnych kluczy za pomocą indeksu nieklastrowanego i ręczne wyszukiwanie indeksu klastrowanego w porównaniu ze skanowaniem indeksu za pomocą OFFSETlub ROW_NUMBER. Są dodatkowe kwestie do rozważenia, jeśli aplikacja stronicująca musi wiedzieć, ile jest w sumie wierszy lub stron. Nie ma innego dobra dyskusja o zaletach „klucza szukać” i „przesunięcie” metody tutaj .

Ogólnie rzecz biorąc, prawdopodobnie lepiej jest, aby ludzie podjęli świadomą decyzję o zmianie zapytań stronicowania, aby w OFFSETrazie potrzeby użyć ich po dokładnych testach.

Po lekkim skrzypieniu zapytania otrzymuję jednakowy kosztorys (50/50) i równe statystyki IO:

; WITH cte AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #objects
)
SELECT *
FROM cte
WHERE r >= 30 AND r < 40
ORDER BY r

SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

Pozwala to uniknąć dodatkowego sortowania, które pojawia się w Twojej wersji, sortując dalej rzamiast object_id.

Zmodyfikowali optymalizator zapytań, aby dodać tę funkcję. Oznacza to, że wdrożyli mechanizmy specjalnie do obsługi polecenia offset ... fetch. Innymi słowy, dla najpopularniejszego zapytania SQL Server musi wykonać znacznie więcej pracy. Zatem różnica w planach zapytań.