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

페이징에 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가 해결책이 될 것입니다.

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


답변

사용해보십시오

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

메모리에로드하지 않고 SQL 서버에서 501에서 600까지의 행을 가져옵니다. 이 구문은 SQL Server 2012 에서만 사용할 수 있습니다.


답변

LINQ-to-SQL이 OFFSET절 ( ROW_NUMBER() OVER() 다른 사람들이 언급 한대로 에뮬레이트 된 경우)을 생성하지만 SQL 에서 페이징을 수행하는 완전히 다르고 훨씬 빠른 방법이 있습니다. 여기에서이 블로그 게시물에 설명 된대로이를 종종 “검색 방법”이라고합니다 .

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

@previousScore@previousPlayerId값은 이전 페이지에서 마지막 레코드의 각각의 값입니다. 이렇게하면 “다음”페이지를 가져올 수 있습니다. 경우 ORDER BY방향은 ASC단순히 사용하는 >대신.

위의 방법을 사용하면 이전 40 개 레코드를 먼저 가져 오지 않고 즉시 4 페이지로 이동할 수 없습니다. 그러나 종종 당신은 어쨌든 그렇게 멀리 점프하고 싶지 않습니다. 대신 인덱싱에 따라 일정한 시간에 데이터를 가져올 수있는 훨씬 빠른 쿼리를 얻을 수 있습니다. 또한 기본 데이터가 변경 되더라도 (예 : 1 페이지, 4 페이지) 페이지가 “안정”상태로 유지됩니다.

예를 들어 웹 애플리케이션에서 더 많은 데이터를 지연로드 할 때 페이징을 구현하는 가장 좋은 방법입니다.

“seek method”는 keyset paging 이라고도 합니다.


답변

LinqToSql은 자동으로 .Skip (N1) .Take (N2)를 TSQL 구문으로 변환합니다. 실제로 Linq에서 수행하는 모든 “쿼리”는 실제로 백그라운드에서 SQL 쿼리를 생성하는 것입니다. 이를 테스트하려면 애플리케이션이 실행되는 동안 SQL 프로파일 러를 실행하기 만하면됩니다.

스킵 / 테이크 방법론은 저와 제가 읽은 다른 사람들에게 매우 효과적이었습니다.

호기심에서 Linq의 스킵 / 테이크보다 더 효율적이라고 생각하는 셀프 페이징 쿼리 유형은 무엇입니까?


답변

저장 프로 시저 내에서 동적 SQL로 래핑 된 CTE를 사용합니다 (애플리케이션에 데이터 서버 측의 동적 정렬이 필요하기 때문). 원하시면 기본적인 예를 들어 드리겠습니다.

LINQ가 생성하는 T / SQL을 볼 기회가 없었습니다. 누군가 샘플을 게시 할 수 있습니까?

추가 보안 계층이 필요하므로 LINQ 또는 테이블에 대한 직접적인 액세스를 사용하지 않습니다 (동적 SQL이이를 다소 중단 함).

이와 같은 것이 트릭을 수행해야합니다. 매개 변수 등에 대한 매개 변수화 된 값을 추가 할 수 있습니다.

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'


답변

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]

t0에는 모든 레코드가 있습니다. t1에는 해당 페이지에 해당하는 레코드 만 있습니다.


답변

제가 제공하는 접근 방식은 SQL 서버가 달성 할 수있는 가장 빠른 페이지 매김입니다. 5 백만 개의 레코드에서 이것을 테스트했습니다. 이 접근 방식은 SQL Server에서 제공하는 “OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY”보다 훨씬 낫습니다.

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