skuteczny sposób na implementację stronicowania

Czy powinienem używać LINQ Skip()i Take()metody do stronicowania, czy zaimplementować własne stronicowanie za pomocą zapytania SQL?

Który jest najbardziej wydajny? Dlaczego miałbym wybierać jedną z nich?

Używam SQL Server 2008, ASP.NET MVC i LINQ.

Próbując dać ci krótką odpowiedź na twoje wątpliwości, jeśli wykonasz skip(n).take(m)metody na linq (z SQL 2005/2008 jako serwerem bazy danych), twoje zapytanie będzie używać Select ROW_NUMBER() Over ...instrukcji, z jakoś bezpośrednim stronicowaniem w silniku SQL.

Podając przykład, mam tabelę db o nazwie mtcityi napisałem następujące zapytanie (działa również z linq do encji):

using (DataClasses1DataContext c = new DataClasses1DataContext())
{
    var query = (from MtCity2 c1 in c.MtCity2s
                select c1).Skip(3).Take(3);
    //Doing something with the query.
}

Wynikowe zapytanie będzie wyglądać następująco:

SELECT [t1].[CodCity], 
    [t1].[CodCountry], 
    [t1].[CodRegion], 
    [t1].[Name],  
    [t1].[Code]
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]) AS [ROW_NUMBER], 
        [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
    FROM [dbo].[MtCity] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

Jest to dostęp do danych w oknie (całkiem fajny, bo będzie zwracał dane od samego początku i będzie miał dostęp do tabeli, o ile zostaną spełnione warunki). Będzie to bardzo podobne do:

With CityEntities As 
(
    Select ROW_NUMBER() Over (Order By CodCity) As Row,
        CodCity //here is only accessed by the Index as CodCity is the primary
    From dbo.mtcity
)
Select [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
From CityEntities c
Inner Join dbo.MtCity t0 on c.CodCity = t0.CodCity
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

Z wyjątkiem tego, że to drugie zapytanie zostanie wykonane szybciej niż wynik linq, ponieważ będzie używał wyłącznie indeksu do tworzenia okna dostępu do danych; Oznacza to, że jeśli potrzebujesz filtrowania, filtrowanie powinno znajdować się (lub musi znajdować się) na liście jednostek (tam, gdzie tworzony jest wiersz), a także należy utworzyć kilka indeksów, aby utrzymać dobrą wydajność.

A co jest lepsze?

Jeśli masz dość solidną organizację pracy w swojej logice, implementacja właściwego sposobu SQL będzie skomplikowana. W takim przypadku LINQ będzie rozwiązaniem.

Jeśli możesz obniżyć tę część logiki bezpośrednio do SQL (w procedurze składowanej), będzie jeszcze lepiej, ponieważ możesz zaimplementować drugie zapytanie, które ci pokazałem (używając indeksów) i zezwolić SQL na wygenerowanie i przechowywanie planu wykonania zapytanie (poprawiające wydajność).

Spróbuj użyć

FROM [TableX]
ORDER BY [FieldX]
OFFSET 500 ROWS
FETCH NEXT 100 ROWS ONLY

aby uzyskać wiersze od 501 do 600 na serwerze SQL, bez ładowania ich do pamięci. Zauważ, że składnia ta stała się dostępna z SQL Server 2012 tylko

Podczas gdy LINQ-to-SQL wygeneruje OFFSETklauzulę (prawdopodobnie emulowaną przy użyciu, ROW_NUMBER() OVER() jak wspominali inni ), istnieje zupełnie inny, znacznie szybszy sposób wykonywania stronicowania w języku SQL. Jest to często nazywane „metodą wyszukiwania”, jak opisano w tym wpisie na blogu .

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

Wartości @previousScorei @previousPlayerIdsą odpowiednimi wartościami ostatniego rekordu z poprzedniej strony. Pozwala to na pobranie „następnej” strony. Jeśli ORDER BYkierunek jest taki ASC, po prostu użyj >zamiast tego.

Przy użyciu powyższej metody nie można od razu przejść do strony 4 bez wcześniejszego pobrania poprzednich 40 rekordów. Ale często i tak nie chcesz skakać tak daleko. Zamiast tego otrzymujesz znacznie szybsze zapytanie, które może być w stanie pobierać dane w stałym czasie, w zależności od indeksowania. Ponadto Twoje strony pozostają „stabilne”, bez względu na to, czy zmieniają się podstawowe dane (np. Na stronie 1, gdy jesteś na stronie 4).

Jest to najlepszy sposób implementacji stronicowania, na przykład podczas leniwego ładowania większej ilości danych w aplikacjach internetowych.

Uwaga, „metoda wyszukiwania” jest również nazywana stronicowaniem zestawu kluczy .

LinqToSql automatycznie skonwertuje .Skip (N1) .Take (N2) do składni TSQL. W rzeczywistości każde „zapytanie”, które wykonujesz w Linq, jest po prostu tworzeniem zapytania SQL w tle. Aby to przetestować, po prostu uruchom program SQL Profiler podczas działania aplikacji.

Metodologia pomiń / przyjmij bardzo dobrze się sprawdziła dla mnie i innych z tego, co przeczytałem.

Z ciekawości, jaki masz typ zapytania ze stronicowaniem, które Twoim zdaniem jest bardziej wydajne niż polecenie skip / take Linq?

Używamy CTE opakowanego w Dynamic SQL (ponieważ nasza aplikacja wymaga dynamicznego sortowania danych po stronie serwera) w ramach procedury składowanej. Jeśli chcesz, mogę podać podstawowy przykład.

Nie miałem okazji spojrzeć na T / SQL, który tworzy LINQ. Czy ktoś może opublikować próbkę?

Nie używamy LINQ ani bezpośredniego dostępu do tabel, ponieważ wymagamy dodatkowej warstwy bezpieczeństwa (pod warunkiem, że dynamiczny SQL nieco to łamie).

Coś takiego powinno załatwić sprawę. Możesz dodać sparametryzowane wartości parametrów itp.

exec sp_executesql 'WITH MyCTE AS (
    SELECT TOP (10) ROW_NUMBER () OVER ' + @SortingColumn + ' as RowID, Col1, Col2
    FROM MyTable
    WHERE Col4 = ''Something''
)
SELECT *
FROM MyCTE
WHERE RowID BETWEEN 10 and 20'

W programie SQL Server 2008:

DECLARE @PAGE INTEGER = 2
DECLARE @TAKE INTEGER = 50

SELECT [t1].*
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[COLUMNORDER] DESC) AS [ROW_NUMBER], [t0].*
    FROM [dbo].[TABLA] AS [t0]
    WHERE ([t0].[COLUMNS_CONDITIONS] = 1)
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN ((@PAGE*@TAKE) - (@TAKE-1)) AND (@PAGE*@TAKE)
ORDER BY [t1].[ROW_NUMBER]

W t0 są wszystkie rekordy, w t1 są tylko te, które odnoszą się do tej strony

Podejście, które podaję, to najszybsza paginacja, jaką może osiągnąć serwer SQL. Przetestowałem to na 5 milionach płyt. To podejście jest o wiele lepsze niż „OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY” zapewniane przez SQL Server.

-- The below given code computes the page numbers and the max row of previous page
-- Replace <<>> with the correct table data.
-- Eg. <<IdentityColumn of Table>> can be EmployeeId and <<Table>> will be dbo.Employees

DECLARE @PageNumber int=1; --1st/2nd/nth page. In stored proc take this as input param.
DECLARE @NoOfRecordsPerPage int=1000;

 DECLARE @PageDetails TABLE
       (
        <<IdentityColumn of Table>> int,
        rownum int,
        [PageNumber] int
       )           
       INSERT INTO @PageDetails values(0, 0, 0)
       ;WITH CTE AS
       (
       SELECT <<IdentityColumn of Table>>, ROW_NUMBER() OVER(ORDER BY <<IdentityColumn of Table>>) rownum FROM <<Table>>
       )
       Insert into @PageDetails 
       SELECT <<IdentityColumn of Table>>, CTE.rownum, ROW_NUMBER() OVER (ORDER BY rownum) as [PageNumber] FROM CTE WHERE CTE.rownum%@NoOfRecordsPerPage=0


--SELECT * FROM @PageDetails 

-- Actual pagination
SELECT TOP (@NoOfRecordsPerPage)
FROM <<Table>> AS <<Table>>
WHERE <<IdentityColumn of Table>> > (SELECT <<IdentityColumn of Table>> FROM 
@PageDetails WHERE PageNumber=@PageNumber)
ORDER BY <<Identity Column of Table>>

możesz jeszcze bardziej poprawić wydajność, sprawdź to

From CityEntities c
Inner Join dbo.MtCity t0 on c.CodCity = t0.CodCity
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

jeśli użyjesz from w ten sposób, da to lepszy efekt:

From   dbo.MtCity  t0
   Inner Join  CityEntities c on c.CodCity = t0.CodCity

powód: ponieważ używasz klasy where w tabeli CityEntities, która wyeliminuje wiele rekordów przed dołączeniem do MtCity, więc 100% pewności, że zwiększy wydajność wielokrotnie ...

W każdym razie odpowiedź rodrigoelp jest bardzo pomocna.

Dzięki

Możesz zaimplementować stronicowanie w ten prosty sposób, przekazując PageIndex

Declare @PageIndex INT = 1
Declare  @PageSize INT = 20

Select ROW_NUMBER() OVER ( ORDER BY Products.Name ASC )  AS RowNumber,
    Products.ID,
    Products.Name
into #Result 
From Products

SELECT @RecordCount = COUNT(*) FROM #Results 

SELECT * 
FROM #Results
WHERE RowNumber
BETWEEN
    (@PageIndex -1) * @PageSize + 1 
    AND
    (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

W 2008 roku nie możemy używać Skip (). Take ()

Droga jest następująca:

var MinPageRank = (PageNumber - 1) * NumInPage + 1
var MaxPageRank = PageNumber * NumInPage

var visit = Visita.FromSql($"SELECT * FROM (SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY Hora DESC),* FROM Visita WHERE ) A WHERE A.[RANK] BETWEEN {MinPageRank} AND {MaxPageRank}").ToList();