mssql에서 paging query 하기

paging query - 페이징 쿼리라는 것은, 일반적으로 게시판 같은 곳에서 한 페이지당 보여줄 목록 같은 것을 가져올때 화면에 뿌려줄 목록만 가져오는 쿼리를 말한다. 보통 mysql 같은 것에서는 limit을 변형해서 쓰더라구. (이를테면 limit 80, 10 하면 80번째부터 10개를 가져오는 식이다)

참고 사이트 : http://roqkffhwk.tistory.com/entry/MySQL-LIMIT%EB%A5%BC-%EC%82%AC%EC%9A%A9%ED%95%98%EC%97%AC-%ED%8E%98%EC%9D%B4%EC%A7%95%EC%BF%BC%EB%A6%AC%EB%A5%BC-%EB%8F%99%EC%9E%91%EC%8B%9C%EC%BC%9C%EB%B3%B4%EC%9E%90 (mysql에서의 페이징 쿼리 예제다)

mssql에서는 저리 간단하게 되지는 않는데,

참고 사이트 : http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/ (mssql에서의 페이징 쿼리 예제다)

요약하면 아래와 같다. (아래 쿼리 예제들은 위 사이트에서 가져온거다.)

SQL Server 2012 용:

DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6


SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET (@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO

SQL Server 2005/2008(R2) 용:

DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6


SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
GO

SQL Server 2000 용:

DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6


SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM
(
SELECT TOP (@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM
(
SELECT TOP ((@PageNumber)*@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
) AS SOD
ORDER BY SalesOrderDetailID DESC
) AS SOD2
ORDER BY SalesOrderDetailID ASC
GO

나중 버전으로 갈수록 select 개수가 하나씩 줄어드네.

OFFSET과 FETCH를 쓴 버전(2012용)과 ROW_NUMBER()를 쓴 버전 (2005/2008(R2)용) 과의 성능 차이를 비교해 본 사이트가 있어서 붙인다.

참고 사이트 : http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx (쿼리 예제와 성능 비교가 있다)

OFFSET / FETCH 버전은 5 스탭으로 동작하고 (단순 select가 4스탭), 클라이언트에 전송된 데이터는 21K byte, 최단시간은 130ms (평균 180ms 가량) 이고,

ROW_NUMBER() 버전은 9 스탭으로 동작하고, 클라이언트에 전송된 데이터는 35K byte, 최단 시간은 156ms (평균 262ms 가량) 이다.

이 결과만 가지고 변수가 많은 최초 실행 결과를 제외하면 OFFSET / FETCH 142ms vs ROW_NUMBER() 170ms 로 대략 20% 정도 효율이 좋다 하겠다.

이상.