페이징을 구현하는 효율적인 방법

페이징을 구현하는 효율적인 방법

---

페이징에 LINQ Skip()와 Take()방법을 사용해야합니까 , 아니면 SQL 쿼리로 자체 페이징을 구현해야합니까?

어느 것이 가장 효율적입니까? 왜 다른 것을 선택해야합니까?

SQL Server 2008, ASP.NET MVC 및 LINQ를 사용하고 있습니다.

---

skip(n).take(m)linq에서 데이터베이스 서버로 SQL 2005/2008을 사용 하여 메소드 를 실행하면 쿼리가 Select ROW_NUMBER() Over ...명령문을 사용하게 되며 SQL 엔진에서 직접 페이징됩니다.

예를 들어, 나는 db 테이블이라고 mtcity하고 다음 쿼리를 작성했습니다 (엔티티와 linq와 함께 작동 함).

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

결과 쿼리는 다음과 같습니다.

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]

이것은 윈도우 데이터 액세스입니다 (매우 시원하지만 btw cuz는 처음부터 데이터를 반환하고 조건이 충족되는 한 테이블에 액세스합니다). 이것은 다음과 매우 유사합니다.

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

단,이 두 번째 쿼리는 데이터 액세스 창을 만들기 위해 인덱스 만 사용하므로 linq 결과보다 빠르게 실행됩니다. 즉, 일부 필터링이 필요한 경우 필터링은 엔티티 목록 (행이 작성된 위치)에 있어야하고 성능을 유지하기 위해 일부 색인도 작성해야합니다.

이제 더 나은 게 뭐야?

논리에 워크 플로가 상당히 많으면 적절한 SQL 방식을 구현하는 것이 복잡합니다. 이 경우 LINQ가 해결책이 될 것입니다.

If you can lower that part of the logic directly to SQL (in a stored procedure), it will be even better because you can implement the second query I showed you (using indexes) and allow SQL to generate and store the Execution Plan of the query (improving performance).

---

Try using

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

to get the rows from 501 to 600 in the SQL server, without loading them in memory. Note that this syntax has become available with SQL Server 2012 only

---

While LINQ-to-SQL will generate an OFFSET clause (possibly emulated using ROW_NUMBER() OVER() as others have mentioned), there is an entirely different, much faster way to perform paging in SQL. This is often called the "seek method" as described in this blog post here.

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

The @previousScore and @previousPlayerId values are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BY direction is ASC, simply use > instead.

With the above method, you cannot immediately jump to page 4 without having first fetched the previous 40 records. But often, you do not want to jump that far anyway. Instead, you get a much faster query that might be able to fetch data in constant time, depending on your indexing. Plus, your pages remain "stable", no matter if the underlying data changes (e.g. on page 1, while you're on page 4).

This is the best way to implement paging when lazy loading more data in web applications, for instance.

Note, the "seek method" is also called keyset paging.

---

LinqToSql will automatically convert a .Skip(N1).Take(N2) into the TSQL syntax for you. In fact, every "query" you do in Linq, is actually just creating a SQL query for you in the background. To test this, just run SQL Profiler while your application is running.

The skip/take methodology has worked very well for me, and others from what I read.

Out of curiosity, what type of self-paging query do you have, that you believe is more efficient than Linq's skip/take?

---

We use a CTE wrapped in Dynamic SQL (because our application requires dynamic sorting of data server side) within a stored procedure. I can provide a basic example if you'd like.

I haven't had a chance to look at the T/SQL that LINQ produces. Can someone post a sample?

We don't use LINQ or straight access to the tables as we require the extra layer of security (granted the dynamic SQL breaks this somewhat).

Something like this should do the trick. You can add in parameterized values for parameters, etc.

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'

---

In 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]

In t0 are all records In t1 are only those corresponding to that page

---

you can further improve the performance, chech this

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

if you will use the from in this way it will give better result:

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

reason: because you are using the where class on the CityEntities table which will eliminate many record before joining the MtCity, so 100% sure it will increase the performance many fold...

Anyway answer by rodrigoelp is really helpfull.

Thanks

---

You can implement paging in this simple way by passing 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

---

In 2008 we cant use Skip().Take()

The way is:

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();

참고URL : https://stackoverflow.com/questions/548475/efficient-way-to-implement-paging