Jak działa stronicowanie z ROW_NUMBER w SQL Server?

Mam Employeetabelę, która ma milion rekordów. Mam następujące SQL do stronicowania danych w aplikacji internetowej. Działa dobrze. Jednak widzę problem - pochodna tabela tblEmployeewybiera wszystkie rekordy w Employeetabeli (aby utworzyć MyRowNumberwartości).

Myślę, że to powoduje wybór wszystkich rekordów w Employeetabeli.

Czy to naprawdę tak działa? Czy też SQL Server jest zoptymalizowany pod kątem wybrania tylko 5 rekordów z oryginalnej Employeetabeli?

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize 

Alternatywą dla testu może być:

;WITH x AS (SELECT EmpID, k = ROW_NUMBER() OVER (ORDER BY EmpID) FROM dbo.Emp)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
WHERE x.k BETWEEN (((@Index - 1) * @PageSize) + 1) AND @Index * @PageSize
ORDER BY ...;

Tak, trafiłeś dwukrotnie w stół, ale w CTE, gdzie skanujesz cały stół, chwytasz tylko klucz, a nie WSZYSTKIE dane. Ale naprawdę powinieneś spojrzeć na ten artykuł:

http://www.sqlservercentral.com/articles/T-SQL/66030/

I dalsza dyskusja:

http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx

W SQL Server 2012 oczywiście można użyć nowej OFFSET/ FETCH NEXTskładni:

;WITH x AS 
(
  SELECT EmpID FROM dbo.Emp
    ORDER BY EmpID
    OFFSET  @PageSize * (@Index - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
ORDER BY ...; 

Chociaż możesz nie znać tego mechanizmu, możesz to sprawdzić samodzielnie, porównując wydajność swojego zapytania do: wybierz * z pracownika.

Nowsze wersje SQL Server radzą sobie całkiem dobrze z optymalizacją, ale może zależeć od kilku czynników.

Sposób działania funkcji ROW_NUMBER będzie zależał od klauzuli Order By. W twoim przykładzie większość zgadłaby, że EmpID jest kluczem podstawowym.

Istnieją pewne gdzie klauzule, które są tak złożone i / lub źle kodowane lub indeksowane, może być lepiej po prostu powrocie cały zestaw danych (to rzadko i może być ustalona). Używanie BETWEEN ma problemy.

Przed założeniem, że lepiej byłoby zwrócić wszystkie wiersze do aplikacji i pozwolić, aby się zorientował, powinieneś popracować nad optymalizacją zapytania. Sprawdź prognozy. Zapytaj Analizator zapytań. Przetestuj kilka alternatyw.

Wiem, że pytanie dotyczy row_number (), ale chcę dodać jedną nową funkcję serwera SQL 2012. Na serwerze SQL 2012 nowa funkcja OFFSET Pobierz następną i jest bardzo szybka niż row_number (). Użyłem go i daje mi to dobry wynik, mam nadzieję, że również wypełniacie to samo doświadczenie.

Znalazłem jeden przykład na http://blogfornet.com/2013/06/sql-server-2012-offset-use/

co jest przydatne. Mam nadzieję, że pomoże Ci również we wdrażaniu nowych funkcji ....

Nie sądzę, że to zwraca wszystkie wiersze w oryginalnej tabeli. Serwer SQL optymalizuje. W przeciwnym razie wybranie miliona wpisów zajmie dużo czasu. Obecnie używam tego i jest to znacznie szybsze niż wybieranie wszystkich wierszy. Tak więc na pewno nie wszystkie wiersze. Jest jednak wolniejszy niż pobieranie pierwszych pięciu rzędów, prawdopodobnie z powodu czasu potrzebnego na zamówienie

DECLARE @PageIndex int;
DECLARE @PageSize int;
SET @PageIndex = 4;
SET @PageSize = 5;
;With ranked AS   --- Or you can make it a view
(
   SELECT ROW_NUMBER() OVER(ORDER BY IdentityId) AS RowNum,  *
   FROM logeventnew
)
SELECT *   --Your fields here
FROM Ranked
WHERE RowNum BETWEEN ((@PageIndex - 1) * @PageSize + 1)
    AND (@PageIndex * @PageSize)
ORDER BY IdentityId