ASP.NET에서 대규모 결과 집합 페이징 구현에 대한 토론

웹 애플리케이션에서 대규모 데이터베이스 결과 세트에 페이지를 매기는 것은 잘 알려진 문제입니다. 간단히 말해서 모든 쿼리 데이터가 단일 페이지에 표시되는 것을 원하지 않으므로 페이징을 사용하여 표시하는 것이 더 적합합니다. 이는 기존 ASP에서는 간단한 작업이 아니지만 ASP.NET에서는 DataGrid 컨트롤이 이 프로세스를 단 몇 줄의 코드로 단순화합니다. 따라서 asp.net에서는 페이징이 매우 간단하지만 기본 DataGrid 페이징 이벤트는 데이터베이스의 모든 레코드를 읽고 이를 asp.net 웹 애플리케이션에 저장합니다. 100만 개 이상의 데이터가 있을 경우 심각한 성능 문제가 발생할 수 있습니다(이것을 믿지 않는다면 응용 프로그램에서 쿼리를 실행하고 작업 관리자 상황에서 aspnet_wp.exe의 메모리 소비를 확인할 수 있습니다). 현재 페이지에 필요한 데이터 레코드만 가져오도록 페이징 동작을 사용자 정의해야 합니다.

인터넷에는 이 문제에 대한 많은 기사와 게시물이 있으며 일부 성숙한 솔루션도 있습니다. 이 기사를 작성하는 목적은 모든 문제를 해결하는 저장 프로시저를 보여주는 것이 아니라 기존 방법을 최적화하고 개발에 따라 수행할 수 있도록 테스트할 애플리케이션을 제공하는 것입니다.

하지만 현재 온라인에 소개된 방식은 그다지 만족스럽지 않습니다. 첫째, "고대" ASP용으로 작성된 기존 ADO가 사용됩니다. 나머지 방법은 SQL Server 저장 프로시저이며, 그 중 일부는 기사 마지막에 나오는 성능 결과에서 볼 수 있듯이 너무 느린 응답 시간으로 인해 사용할 수 없지만, 내 관심을 끌었던 몇 가지가 있습니다.

일반화

현재 일반적으로 사용되는 세 가지 방법인 임시 테이블(TempTable), 동적 SQL(DynamicSQL) 및 행 개수(Rowcount)를 주의 깊게 분석하고 싶습니다. 다음에서는 두 번째 방법을 (오름차순-내림차순) Asc-Desc 방법이라고 부르는 것을 선호합니다. 동적 SQL은 다른 방법으로 동적 SQL 논리를 적용할 수도 있기 때문에 좋은 이름은 아니라고 생각합니다. 이러한 모든 저장 프로시저의 일반적인 문제는 기본 키 열(PK 열)뿐만 아니라 정렬할 열을 추정해야 한다는 것입니다. 이로 인해 일련의 문제가 발생할 수 있습니다. 페이징을 통해 표시합니다. 즉, 서로 다른 정렬 열마다 다양한 페이징 쿼리가 있어야 합니다. 즉, 각 정렬 열에 대해 서로 다른 저장 프로시저를 수행하거나(사용되는 페이징 방법에 관계없이) 다음을 수행해야 합니다. 동적 SQL을 사용하여 이 기능을 저장 프로시저에 추가합니다. 이 두 가지 방법은 성능에 약간의 영향을 미치지만 특히 이 방법을 사용하여 다른 쿼리를 표시해야 하는 경우 유지 관리성이 향상됩니다. 따라서 이 기사에서는 동적 SQL을 사용하여 모든 저장 프로시저를 요약하려고 시도하지만 어떤 이유로 인해 부분적인 보편성만 달성할 수 있으므로 복잡한 쿼리에 대해서는 여전히 독립적인 저장 프로시저를 작성해야 합니다.

기본 키 열을 포함하여 모든 정렬 필드를 허용할 때의 두 번째 문제는 해당 열이 제대로 인덱싱되지 않은 경우 이러한 방법 중 어느 것도 도움이 되지 않는다는 것입니다. 이러한 모든 방법에서는 페이징 소스를 먼저 정렬해야 합니다. 대규모 데이터 테이블의 경우 인덱스가 아닌 열을 사용하여 정렬하는 비용은 무시할 수 있습니다. 이 경우 응답 시간이 길어 실제 상황에서는 모든 저장 프로시저를 사용할 수 없습니다. (해당 시간은 테이블의 크기와 획득할 첫 번째 레코드에 따라 몇 초에서 몇 분까지 다양합니다.) 다른 열의 인덱스는 바람직하지 않은 추가 성능 문제를 일으킬 수 있습니다. 예를 들어 매일 많은 양의 데이터를 가져오면 속도가 매우 느려질 수 있습니다.

임시 테이블

먼저 임시 테이블 방법에 대해 이야기하겠습니다. 이것은 내 프로젝트에서 여러 번 접했던 널리 권장되는 솔루션입니다. 이 메소드의 본질을 살펴보겠습니다:

CREATE TABLE #Temp(
ID int IDENTITY 기본 키,
PK /*heregoesPKtype*/
)

INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn

SELECT FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow

모든 행을 임시 테이블에 복사하여 테이블을 사용하면 쿼리를 더욱 최적화할 수 있지만(SELECT TOP EndRow...) 핵심은 최악의 시나리오입니다. 100만 개의 레코드가 포함된 테이블은 100만 개의 레코드가 포함된 임시 테이블을 생성합니다.

테스트에서

오름차순 정렬

이 방법은 하위 쿼리에서는 기본 정렬을 사용하고 기본 쿼리에서는 역 정렬을 사용합니다.

@temp 테이블 선언(
PK /* PK 유형 */
NULL이 아님 기본
)

@temp에 삽입 @PageSize PK FROM TOP 선택
(
TOP 선택(@StartRow + @PageSize)
PK,
SortColumn /* 정렬 열이 PK와 다른 경우 SortColumn은 다음을 수행해야 합니다.
그렇지 않으면 PK만 필요합니다.
*/

ORDER BY SortColumn
/*
기본 순서 – 일반적으로ASC
*/
)

ORDER BY SortColumn
/*
역방향 기본 순서 - 일반적으로 DESC
*/

SELECT FROM Table JOIN @Temp temp ON 테이블 .PK= temp .PK
정렬 기준 정렬
/*
기본 순서
*/

행 계산

SQL의 SET ROWCOUNT 표현식을 사용하므로 불필요한 행을 건너뛰고 필요한 행 레코드를 얻을 수 있습니다.

DECLARE @Sort /* 정렬 열 유형 */

SET ROWCOUNT @ StartRow

SELECT @Sort=SortColumn FROM 테이블 ORDER BY SortColumn
SET ROWCOUNT @PageSize

SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

하위 쿼리 방법에는

해당 소스는 다릅니다. 첫 번째는 잘 알려진 Triple Query 또는 자체 쿼리 방법입니다. 이 기사에서는 다른 모든 저장 프로시저를 포괄하는 유사한 공통 논리도 사용합니다. 여기서의 아이디어는 전체 프로세스에 연결하는 것입니다. 테스트에서는 레코드 개수가 필요하지 않기 때문에 원본 코드를 일부 줄였습니다.)

SELECT FROM Table WHERE PK IN(
PK가 없는 테이블에서 TOP @PageSize PK를 선택하세요.
(
SELECT TOP @StartRow PK FROM 테이블 ORDER BY SortColumn)
정렬 기준 정렬)
ORDER BY SortColumn

Cursor

구글 토론방을 보다가 마지막 방법을 찾았습니다. 이 방법은 서버측 동적 커서를 사용합니다. 많은 사람들은 커서의 질서성 때문에 관련성이 없고 비효율적이라는 이유로 사용을 피하려고 합니다. 그러나 돌이켜보면 페이징은 실제로 어떤 방법을 사용하든 질서 있는 작업입니다. 기록하려면 출발선으로 돌아가야 합니다. 이전 방법에서는 녹화 시작 전 먼저 모든 행을 선택하고 녹화에 필요한 행을 추가한 후 이전 행을 모두 삭제합니다. 동적 커서에는 마법 점프를 수행하는 FETCH RELATIVE 옵션이 있습니다. 기본 논리는 다음과 같습니다.

DECLARE @PK /* PKType */

DECLARE @tblPK

TABLE(
PK /*PKType*/ NULL이 아닌 기본 키
)

PagingCursor CURSOR DYNAMICREAD_ONLY를 선언하십시오.
SELECT @PK FROM 테이블 ORDER BY SortColumn

OPEN PagingCursor

@PageSize>0 AND @@FETCH_STATUS =0인 동안

PagingCursor에서 @PK로 상대 @StartRow를 가져옵니다.

시작하다
@tblPK(PK) 값(@PK) 삽입
PagingCursor에서 다음 항목을 @PK로 가져옵니다.
SET @PageSize = @PageSize - 1
종료

종료
페이징 커서
할당 해제
PagingCursor

SELECT FROM Table JOIN @tblPK 임시 ON 테이블 .PK= 임시 .PK
ORDER BY SortColumn의

복잡한 쿼리 일반화

앞서 모든 저장 프로시저가 일반화를 위해 동적 SQL을 사용하므로 이론상으로는 모든 종류의 복잡한 쿼리를 사용할 수 있다는 점을 지적했습니다. 다음은 Northwind 데이터베이스를 기반으로 하는 복잡한 쿼리의 예입니다.

SELECT Customers.ContactName AS 고객, Customers.Address + ' , ' + Customers.City + ', '+ Customers.Country
AS 주소, SUM([OrderDetails].UnitPrice*[OrderDetails] .Quantity)
AS [Totalmoneyspent]

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID

WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'

GROUP BY 고객.연락처 이름,고객.주소,고객.시, 고객.국가
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000

ORDER BY Customer DESC ,Address DESC는

다음과 같이 두 번째 페이지의 페이징 저장소 호출을 반환합니다.

EXEC ProcedureName

/*Tables */

'
고객
INNER JOIN 주문 ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [OrderDetails] ON Orders.OrderID=[OrderDetails].OrderID
'
,
/* PK */

'
고객.고객ID
'
,
/* 주문순서 */

'
고객.연락처 이름 DESC,고객.주소DESC
'
,

/*페이지 번호 */

2
,

/*페이지 크기 */

10
,

/*필드 */

'
고객.연락처 이름 AS 고객,
Customers.Address+'' , '' +Customers.City+ '' , '' +Customers.Country ASAddress, SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
,

/*필터 */

'
Customers.Country<>'' 미국 '' ANDCustomers.Country<> '' 멕시코 ''' ,

/*GroupBy */

'
고객.고객 ID,고객.연락처 이름,고객.주소,
고객.도시,고객.국가
HAVING(SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity))>1000
'

원래 쿼리의 ORDER BY 문에서 별칭을 사용했다는 점은 주목할 가치가 있지만, 기록을 시작하기 전에 행을 건너뛰는 것은 시간이 많이 걸리기 때문에 페이징 저장 프로시저에서는 이 작업을 수행하지 않는 것이 좋습니다. 실제로 구현 방법은 여러 가지가 있지만 처음에 모든 필드를 포함하는 것이 아니라 기본 키 열(RowCount 메서드의 정렬 열과 동일)만 포함하는 것이 원칙이므로 완료 속도를 높일 수 있습니다. 일. 요청 페이지에서만 모든 필수 필드를 얻을 수 있습니다. 또한 최종 쿼리에는 필드 별칭이 없으며 행 건너뛰기 쿼리에서는 인덱스 열을 미리 사용해야 합니다.

RowCount 저장 프로시저에는 또 다른 문제가 있습니다. 일반화를 위해서는 ORDER BY 문에 하나의 열만 허용됩니다. 이는 여러 열을 정렬할 수 있지만 오름차순 및 커서 메서드에도 문제가 있습니다. 기본 키에는 필드가 하나만 있는지 확인해야 합니다. 좀 더 동적인 SQL을 사용하면 이 문제를 해결할 수 있을 것 같지만, 제 생각에는 그럴 가치가 없습니다. 이러한 상황이 발생할 수는 있지만 자주 발생하지는 않습니다. 일반적으로 위의 원칙을 사용하여 저장 프로시저를 독립적으로 페이징할 수도 있습니다.

성능 테스트

테스트에서는 네 가지 방법을 사용했습니다. 더 좋은 방법이 있으면 알고 싶습니다. 어쨌든, 이 방법들을 비교하고 성능을 평가해야 합니다. 우선, 첫 번째 아이디어는 페이징 DataGrid가 포함된 asp.net 테스트 응용 프로그램을 작성한 다음 페이지 결과를 테스트하는 것입니다. 물론 이는 저장 프로시저의 실제 응답 시간을 반영하지 않으므로 콘솔 애플리케이션이 더 적합합니다. 또한 성능 테스트용이 아니라 DataGrid 사용자 정의 페이지 매김 및 저장 프로시저가 함께 작동하는 예로서 웹 애플리케이션도 포함했습니다.

테스트에서는 자동으로 생성된 대용량 데이터 테이블을 사용하여 약 50만 개의 데이터를 삽입했습니다. 실험할 테이블이 없는 경우 여기를 클릭하여 데이터 생성을 위한 테이블 디자인 및 저장 프로시저 스크립트를 다운로드할 수 있습니다. 자동 증가 기본 키 열을 사용하는 대신 고유 식별자를 사용하여 레코드를 식별했습니다. 위에서 언급한 스크립트를 사용하는 경우 테이블을 생성한 후 자동 증가 열을 추가하는 것을 고려할 수 있습니다. 자동 증가 데이터는 기본 키를 기준으로 숫자별로 정렬됩니다. 이는 페이지가 매겨진 저장 프로시저를 사용한다는 의미이기도 합니다. 기본 키 정렬을 사용하여 현재 페이지의 데이터를 가져옵니다.

성능 테스트를 구현하기 위해 루프를 통해 특정 저장 프로시저를 여러 번 호출한 후 평균 응답 시간을 계산했습니다. 캐싱 이유를 고려하면 실제 상황을 보다 정확하게 모델링하기 위해 동일한 페이지에서 저장 프로시저에 대한 여러 호출에 대한 데이터를 얻는 데 걸리는 시간은 일반적으로 평가에 적합하지 않습니다. 각 호출에 대해 요청되는 페이지 번호는 무작위여야 합니다. 물론, 페이지 수는 10~20페이지로 고정되어 있고, 서로 다른 페이지 번호의 데이터를 여러 번 얻을 수 있지만 무작위로 얻을 수 있다고 가정해야 합니다.

우리가 쉽게 알 수 있는 것은 응답 시간은 결과 세트의 시작 위치를 기준으로 얻어지는 페이지 데이터의 거리에 따라 결정된다는 것입니다. 결과 세트의 시작 위치에서 멀어질수록 더 많은 레코드가 발생합니다. 건너뛰었습니다. 이것이 제가 무작위 순서에 상위 20개를 포함하지 않는 이유이기도 합니다. 대안으로, 2^n 페이지를 사용하고 루프의 크기는 필요한 여러 페이지 수 * 1000이므로 각 페이지는 거의 1000번 가져옵니다(임의의 이유로 편차가 분명히 있을 것입니다).

결과는

다음과 같습니다

내 테스트 결과는 다음과 같습니다.






결론

테스트는 행 개수, 커서, 오름차순-내림차순, 하위 쿼리 등 최고 성능부터 최악 성능 순으로 수행되었습니다. 한 가지 흥미로운 점은 일반적으로 사람들이 처음 5페이지 이후에는 페이지를 거의 방문하지 않으므로 이 경우 결과 집합의 크기와 페이지 발생 빈도를 예측하는 방법에 따라 하위 쿼리 방법이 사용자의 요구에 적합할 수 있다는 것입니다. , 이러한 방법을 조합하여 사용할 수도 있습니다. 저라면 어쨌든 행 개수 방법을 선호합니다. 꽤 잘 작동합니다. 첫 번째 페이지에서도 여기에서 "모든 경우"는 일반화가 어려운 경우를 나타냅니다. 이 경우에는 다음을 사용합니다. 커서. (아마도 처음 두 개에는 하위 쿼리 방법을 사용하고 그 이후에는 커서 방법을 사용할 것입니다)