T-SQL Black Belt - 페이징 구현하기
T-SQL Black Belt
사용자들이 인접한 페이지 또는 인접하지 않은 페이지로 이동할 수 있도록 하기.
Itzik Ben-Gan
클라이언트에게 쿼리 결과를 반환하고 그 결과를 화면에 디스플레이 하는 애플리케이션들을 작성할 때에는, 최종 사용자의 스크린 크기를 고려할 필요가 있다. 만약 어떤 쿼리의 결과 집합의 행 수가 너무 많아서 화면에 모두 디스플레이 할 수 없으면 결과 집합을 여러 개의 페이지들로 분할하는 로직을 도입해야 한다. 그런 다음에 버튼이나 다른 그래픽 요소들을 제공하여 사용자들이 결과 페이지들 사이를 이동할 수 있도록 해 주어야 한다. 데이터를 여러 개의 덩어리들로 분할하는 과정을 페이징이라고 하는데 웹 애플리케이션의 경우에는 매우 보편적으로 페이징이 발생한다. 페이징을 구현하는 몇 가지 효율적인 방법들을 살펴 본 다음에, 보편적으로 사용되는 기법이지만 여러분이 기대한 만큼 동작하지 않는 기법들을 고려해 보자.
주문 시나리오
애플리케이션에서 페이징을 구현하고자 할 때에는 세 가지 사항을 결정할 필요가 있다. 첫 번째로 결정해야 할 사항은 반환하고자 하는 행들에 대하여 적용할 정렬 순서이고, 두 번째는 페이지당 반환할 행의 수이며, 세 번째는 사용자들이 결과 집합들을 이동할 수 있도록 제공해 주는 이동 기법이다.
여러분이 선택하는 정렬 순서와 페이지 크기(하나의 페이지에 디스플레이할 행들의 수)에 따라, 어느 페이지에 어떤 행들이 디스플레이 될 것인지가 결정된다. 필자의 예제에서는 Northwind 데이터베이스의 Orders 테이블을 사용한다. 이 기사의 예제에서 필자는 OrderDare와 OrderID를 정렬 순서 기준으로 사용하고, 페이지 크기는 10행으로 사용한다. 값이 고유한 컬럼들을 기준으로 정렬하는 것이 중요하다는 점에 유의하기 바란다. 고유값에 대한 정렬이 없이는, 원본 행과 대상 페이지의 관계가 확정적이지 않으며, 그것은 곧 어떤 행이 어느 페이지에 디스플레이 될지를 확실하게 결정할 수 없다는 것을 의미한다. 연대순으로 정렬된 주문들을 반환하고자 한다고 하자. OrderDate 컬럼의 값들은 고유하지 않을 수 있기 때문에, 주문일자가 동일한 행들의 순서를 결정할 2차 정렬기준이 되는 "tiebreaker" 컬럼을 선택할 필요가 있다. OrderID 컬럼이 고유하므로 필자는 OrderID 컬럼을 tiebreaker로 선택했다.
페이지를 이동하기 위해 어떤 방법을 사용할 것인지는 애플리케이션 사용자들의 요구에 달려 있다. 만일 사용자들이 현재의 페이지에서 다음 페이지나 이전 페이지로만 이동할 필요가 있다면, 단순하고 효율적인 솔루션을 구현할 수 있다. 그러나 만일 사용자들이 특정 번호의 페이지로 이동하는 것과 같은, 보다 동적인 기능들을 필요로 한다면 페이지 크기와 페이지 번호의 두 가지 입력만을 기초로 하여 원하는 페이지의 행들을 필터링할 수 있도록 해 주는 솔루션을 구현할 필요가 있다.
먼저 인접한 페이지들 간의 이동에 대하여 살펴 보도록 하자.
인접한 페이지로 이동하기
인접한 페이지로 이동할 수 있도록 하기 위해서는, 첫 번째 페이지를 가져 오고 그 다음 페이지를 가져 오고 그 이전 페이지를 가져 오는 세 가지 과제를 수행하는 코드를 구현할 필요가 있다. 필자는 각 과제별로 별도의 저장 프로시저를 하나씩 작성할 것을 권고한다. 저장 프로시저를 사용하게 되면, 나중에 보다 효율적인 페이징 방법을 찾기만 한다면 클라이언트 애플리케이션을 변경하지 않고도 유연하게 구현을 변경할 수 있다.
Northwind 데이터베이스에서 [리스트 1]의 코드를 실행하면 Orders 테이블의 첫 번째 페이지를 반환하는 저장 프로시저가 만들어진다. 저장 프로시저의 코드는 단순하다. 쿼리는 주문일자(OrderDate)와 주문ID(OrderID)를 기준으로 오름차순으로 상위 10개의 행들을 반환한다. 다음 코드를 수행하면 저장 프로시저를 테스트할 수 있다.
EXEC dbo.usp_GetFirstPage;
[그림 1]에서 볼 수 있듯이 이 코드를 실행하면 Orders 테이블의 첫 번째 페이지가 반환된다.
현재의 페이지의 다음 페이지를 반환하는 저장 프로시저는 두 개의 입력 매개변수(이전에 반환된 페이지의 마지막 행의 주문일자와 주문ID)를 받는다. 클라이언트 애플리케이션에서는, 현재 페이지에 대한 호출을 처리하는 동안 로컬에 그 값들을 저장할 필요가 있다.
[리스트 2]의 코드를 실행하면, 현재 페이지의 다음 페이지를 반환하는 usp_GetNextPage 저장 프로시저가 만들어진다. 쿼리는 이전 페이지의 마지막 행의 주문일자보다 늦은 주문일자를 가지거나 또는 주문일자는 동일하면서 주문ID값이 더 큰 행들을 찾는다. 쿼리를 실행하면 주문일자와 주문ID를 기준으로 오름차순으로 상위 10개의 행들이 반환된다.
사용자가 이미 첫 번째 페이지를 요청했다고 가정하면, 클라이언트 애플리케이션은 반환된 첫 번째 페이지의 마지막 행의 주문일자와 주문ID의 값들을 로컬에 정렬하게 된다. 사용자가 두 번째 페이지를 요청하면, 클라이언트 애플리케이션이 다음과 같은 코드를 실행하여 usp_GetNextPage를 호출한다.
EXEC dbo.usp_GetNextPage @last_orderdate = '19960716', @last_orderid = 10257;
저장 프로시저는 [그림 2]의 결과와 같이 Orders의 두 번째 페이지를 반환한다. 클라이언트 애플리케이션은 이제 이 페이지 마지막 행의 주문일자와 주문ID를 로컬에 저장하게 되며, 만약 사용자가 또다시 다음 페이지를 요청하면 이 두 값을 입력 매개변수로 전달하게 된다. 이와 유사하게 클라이언트 애플리케이션에서는 이 페이지에서 반환된 첫 번째 행의 주문일자와 주문ID도 로컬에 저장한다. 이 두 값은 사용자가 이전 페이지를 요청하는 경우에 사용된다.
이전 페이지를 반환하는 저장 프로시저의 구현은 usp_GetNextPage를 약간 변형하면 된다. 크다(>) 연산자의 방향을 바꾸어서 작다(<) 연산자로 변경하고, 주문일자와 주문ID에 대하여 역순(내림차순)으로 정렬하면 된다. 이 두 가지 변경사항을 적용하면 제대로 된 페이지를 반환하기는 하지만, 내림차순으로 정렬된 행들이 반환된다. 다시 오름차순으로 정렬된 페이지들을 얻기 위해서는, 파생된 테이블을 사용하여 모든 행들을 쿼리하고, 그 행들을 오름차순으로 정렬하면 된다.
[리스트3]의 코드를 실행하면 usp_GetPrevPage 저장 프로시저가 만들어진다. 만들어진 저장 프로시저를 테스트하려면, 다음과 같이 두 번째 페이지의 첫 번째 행의 주문일자와 주문ID를 제공하여 저장 프로시저를 실행하면 된다.
EXEC dbo.usp_GetPrevPage @first_orderdate = '19960717', @first_orderid = 10258;
그러면 다시 첫 번째 페이지(그림 1)가 반환된다.
인접하지 않은 페이지로 이동하기
사용자가 마지막으로 요청한 페이지와 무관하게 특정 페이지를 요청할 수 있도록 해 주는 보다 유연한 이동 기능을 사용자들에게 제공할 필요가 있다면 (예를 들어, 페이지 3을 요청한 다음에 페이지 10을 요청하고, 페이지 10을 요청한 다음에 페이지 15를 요청하는 경우), 다른 솔루션을 구현해야 한다. 그 중 한 가지는 페이지 번호와 페이지 크기만 입력 받아서, 요청한 페이지의 행을 필터링할 수 있는 솔루션이다. 클라이언트 측 솔루션을 구현할 수도 있고, 서버 측 솔루션을 구현할 수도 있다. 클라이언트 측 솔루션에서는, 정렬된 모든 테이블의 행들을 가져 와서 카운터값을 증가시켜 가면서 행 번호를 계산한다. 테이블의 모든 행들을 캐싱한 다음에, 사용자가 원하는 행 번호의 범위를 계산함으로써 사용자가 요청한 페이지를 반환한다. 어떤 페이지의 첫 번째 행은 ((page number-1) x page size) + 1 로 계산하면 되고, 마지막 행은 page number x page size로 계산할 수 있다. 예를 들어, 페이지 크기가 10행인 경우에 3번 페이지를 원한다면, 행 번호 21부터 30까지가 해당된다. 그러나 클라이언트 측 솔루션은, 사용자가 단지 두세 개의 페이지만 필요로 하는 경우에도 클라이언트의 메모리 리소스가 많이 사용하게 되며 네트워크 트래픽도 많이 유발하는 단점이 있다.
서버 측 솔루션의 경우에도 클라이언트 측 솔루션에서와 마찬가지로, 행 번호(원하는 정렬 순서대로 매긴 연속적인 정수 값)를 계산하고, 임시 테이블에 행들의 Primary Key와 함께 행 번호들을 저장한다. 클라이언트 애플리케이션은 사용자가 요청하는 페이지의 행 번호의 범위를 계산한 다음에, 임시 테이블과 베이스 테이블을 조인하여 사용자가 요청한 속성들(예를 들어, 주문일자, 고객ID)을 얻게 된다. 임시 테이블에 행 번호와 Primary Key 뿐 아니라, 사용자가 원하는 모든 속성들을 저장할 수도 있다. 필요한 속성들을 모두 임시 테이블에 저장하는 방식을 택하면 조인을 제거할 수 있다. 그러나, 임시 테이블에 모든 속성들을 저장하면 tempdb 리소스를 많이 사용하게 된다.
먼저, 서로 다른 주문 정보들에 대하여 행 번호를 계산하고 그 행 번호를 주문ID와 함께 임시 테이블에 저장하는 서버 측 페이징 기법에 대한 솔루션을 찾아 보자. 많은 수의 개발자들이 기대한 대로 항상 동작하지 않는 기법을 사용하여 행 번호를 계산한다. 다음의 코드(실행 금지)는 각각의 주문ID에 대하여 주문일자와 주문ID 순으로 행 번호를 계산하는 전형적인 시도이다:
SELECT IDENTITY(int, 1, 1) AS RowNum, OrderID+0 AS OrderID INTO dbo.#OrdersRN FROM dbo.Orders ORDER BY OrderDate, OrderID;
이 기법은 항상 여러분이 기대한 대로 동작하지 않을 수 있다. Microsoft는 SQL Server가 정렬 작업이 수행된 다음에 IDENTITY값을 계산하는 것을 보장하지 않기 때문이다. 그런 보장이 없다면, SQL Server가 할당하는 IDENTITY 값이 항상 원하는 정렬 순서와 일치하지 않을 수 있다. 그러므로, 다른 기법으로 행 번호를 계산해야 한다. SQL 서버 2005를 사용하기 전까지는 어느 정도의 성능을 내면서 행 번호를 계산하기를 원하는 경우에는 커서를 사용해야 한다. [리스트 4]에 정렬된 주문정보들에 대하여 반복적으로 행 번호를 계산하고, 임시 테이블에 주문ID와 함께 행 번호들을 저장하는데 사용할 수 있는 커서 기반의 코드가 있다. [리스트 5]를 보면 임시 테이블을 만든 다음에 특정 페이지에 대한 요청에 응답하게 되는데, Orders 테이블과 [리스트 4]의 코드에서 만들어진 임시 테이블을 조인하고, 요청한 페이지에 있는 행들의 범위를 필터링 함으로써 특정 페이지의 주문정보들(예를 들어, 3번 페이지)을 반환한다.
제대로 페이징하기
페이징은, 쿼리의 결과 집합을 그룹 지어 클라이언트 애플리케이션에게 반환하기를 원하는 경우에 필요하다. 여러분은 이제 인접한 페이지간의 이동과 인접하지 않은 페이지간의 이동의 두 가지 페이지 이동 기법에 대한 솔루션을 제공할 수 있을 것이다. 저장 프로시저를 사용하면 클라이언트 애플리케이션에 영향을 주지 않으면서 솔루션의 구현을 변경할 수 있어서 편리하다. 또한 IDENTITY값이 쿼리의 ORDER BY 절에서 사용자가 지정하는 정렬 순서와 반드시 일치한다고 보장할 수 없기 때문에 어떤 순서에 기초하여 행 번호를 계산하는 기법에 대해서는 IDENTITY 기법을 사용하지 않는 것이 좋다는 것을 기억해 두기 바란다.
그림 1 Orders 테이블의 첫 번째 페이지
OrderID OrderDate CustomerID EmployeeID ShipVia ------- --------- ---------- ---------- ------- 10248 1996-07-04 VINET 5 3 10249 1996-07-05 TOMSP 6 1 10250 1996-07-08 HANAR 4 2 10251 1996-07-08 VICTE 3 1 10252 1996-07-09 SUPRD 4 2 10253 1996-07-10 HANAR 3 2 10254 1996-07-11 CHOPS 5 2 10255 1996-07-12 RICSU 9 3 10256 1996-07-15 WELLI 3 2 10257 1996-07-16 HILAA 4 3
그림 2 Orders 테이블의 두 번째 페이지
OrderID OrderDate CustomerID EmployeeID ShipVia ------- --------- ---------- ---------- ------- 10258 1996-07-17 ERNSH 1 1 10259 1996-07-18 CENTC 4 3 10260 1996-07-19 OTTIK 4 1 10261 1996-07-19 QUEDE 4 2 10262 1996-07-22 RATTC 8 3 10263 1996-07-23 ERNSH 9 3 10264 1996-07-24 FOLKO 6 3 10265 1996-07-25 BLONP 2 1 10266 1996-07-26 WARTH 3 3 10267 1996-07-29 FRANK 4 1
리스트 1: 첫 번째 주문 페이지를 반환하는 저장 프로시저
CREATE PROC dbo.usp_GetFirstPage AS SELECT TOP 10 OrderID, OrderDate, CustomerID, EmployeeID, ShipVia FROM dbo.Orders ORDER BY OrderDate, OrderID;
리스트 2: 주문 정보의 다음 페이지를 반환하는 저장 프로시저
CREATE PROC dbo.usp_GetNextPage
@last_orderdate AS datetime,
@last_orderid AS int
AS
SELECT TOP 10 OrderID, OrderDate, CustomerID, EmployeeID, ShipVia
FROM dbo.Orders
WHERE OrderDate > @last_orderdate
OR (OrderDate = @last_orderdate
AND OrderID > @last_orderid)
ORDER BY OrderDate, OrderID;
리스트 3: usp_GetPrevPage 프로시저를 생성하는 코드
CREATE PROC dbo.usp_GetPrevPage
@first_orderdate AS datetime,
@first_orderid AS int
AS
SELECT *
FROM (SELECT TOP 10 OrderID, OrderDate,
CustomerID, EmployeeID, ShipVia
FROM dbo.Orders
WHERE OrderDate < @first_orderdate
OR (OrderDate = @first_orderdate
AND OrderID < @first_orderid)
ORDER BY OrderDate DESC, OrderID DESC) AS D
ORDER BY OrderDate, OrderID;
리스트 4: 행 수를 계산하는 커서 기반의 코드
CREATE table dbo.#OrdersRN
( RowNum int NOT NULL,
OrderID int NOT NULL );
GO
DECLARE @OrderID AS int, @rownum AS int;
DECLARE orders_cursor CURSOR FAST_FORWARD FOR
SELECT OrderID
FROM Orders
ORDER BY OrderDate, OrderID;
OPEN orders_cursor;
SET @rownum = 1;
FETCH NEXT FROM orders_cursor INTO @OrderID;
WHILE @@fetch_status = 0
BEGIN
INSERT INTO dbo.#OrdersRN(OrderID, RowNum)
VALUES(@OrderID, @rownum);
SET @rownum = @rownum + 1;
FETCH NEXT FROM orders_cursor INTO @OrderID;
END
CLOSE orders_cursor;
DEALLOCATE orders_cursor;
CREATE UNIQUE CLUSTERED INDEX idx_rownum
ON dbo.#OrdersRN(RowNum);
GO
리스트 5: 특정 페이지 번호를 반환하는 코드
DECLARE @PageNum AS int, @PageSize AS int;
SET @PageNum = 3; /* 다른 페이지를 테스트하려면 PageNum값을 변경하면 됨.*/
SET @PageSize = 10;
SELECT O.OrderID, O.OrderDate, O.CustomerID,
O.EmployeeID, O.ShipVia
FROM dbo.#OrdersRN AS OP
JOIN dbo.Orders AS O
ON OP.OrderID = O.OrderID
WHERE OP.RowNum BETWEEN @PageSize*(@PageNum-1)+1
AND @PageSize*@PageNum
ORDER BY OP.RowNum;
최종 수정일 : 2005년 3월 16일