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

의심에 대한 간단한 대답을 제공하려고 노력하면 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가 해결책이 될 것입니다.

논리의 해당 부분을 SQL (저장 프로 시저에서)로 직접 낮출 수 있다면, 제가 보여 드린 두 번째 쿼리 (인덱스 사용)를 구현하고 SQL이 실행 계획을 생성하고 저장할 수 있기 때문에 더 좋습니다. 쿼리 (성능 향상).

답변

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

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

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'

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]

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