SQL Server 2005부터 row_number 함수가 제공됨으로써 오라클처럼 서브쿼리를 활용하여 게시판의 페이징 쿼리를
만들 수 있게 되었습니다.
row_number를 사용할 경우, 서브쿼리를 써야하기 때문에 복잡하게 보인다는 점인데요... 이번에 SQL Server 2011부터
order by 절에 새로운 기능이 추가되어짐으로써 좀 더 간단한 쿼리로 페이징 구현이 가능하게 되어 소개드립니다.
과거 row_number를 사용한 것과 성능차이는 없는지 테스트를 통해서 슬쩍 살펴보도록 하겠습니다.
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@ 페이징 테스트할 임시 테이블 생성
create table tab01 (
col01 int identity (1,1) not null
, col02 varchar(5000)
)
create clustered index idx_tab01_col01 on tab01 (col01)
-- 임시 테이블에 데이터 100만건 입력하기
set nocount on
declare @i int
set @i = 1
begin tran
while (@i < 1000000)
begin
insert into tab01 (col02)
select REPLICATE('ABC', 50);
set @i = @i + 1;
end
commit tran
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@ 변경된 로직의 쿼리 수행 (시작페이지를 지정한 경우)
declare @startArticleNo int
declare @PageNo int
declare @PageSize int
set @startArticleNo = 950001
set @PageNo = 5
set @PageSize = 5
set statistics profile on
set statistics io on
select *
from dbo.tab01 wtih (nolock)
where col01 >= @startArticleNo
order by col01
offset (@PageNo-1)*@PageSize rows
fetch next @PageSize rows only;
set statistics profile off
set statistics io off
Result>
950021
950022
950023
950024
950025
IO사용량>
Table 'tab01'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
실행계획>
Rows Executes StmtText
5 1 select * from dbo.tab01 wtih (nolock) where col01 >= @startArticleNo order by col01 offset (@PageNo-1)
5 1 |--Top(OFFSET EXPRESSION:(CONVERT_IMPLICIT(bigint,([@PageNo]-(1))*[@PageSize],0)),TOP EXPRESSION:
25 1 |--Clustered Index Seek(OBJECT:([master].[dbo].[tab01].[idx_tab01_col01] AS [wtih]), SEEK
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@ 이전 로직의 쿼리 수행 (시작페이지를 지정한 경우)
declare @startArticleNo int
declare @PageNo int
declare @PageSize int
set @startArticleNo = 950001
set @PageNo = 5
set @PageSize = 5
set statistics profile on
set statistics io on
select x.*
from (
select
top ( @PageNo * @PageSize)
col01
, col02
, ROW_NUMBER() over (order by col01 ) seqNo
from dbo.tab01 with (readuncommitted)
where col01 > @startArticleNo
order by col01 ) x
where x.seqNo between (@PageNo-1) * @PageSize and @PageNo * @PageSize -1
order by x.col01
Result>
950021
950022
950023
950024
950025
IO사용량>
Table 'tab01'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
실행계획>
Rows Executes StmtText
5 1 select x.* from ( select top ( @PageNo * @PageSize) col01 , col02 , ROW_NUMBER() over
5 1 |--Filter(WHERE:([Expr1004]>=CONVERT_IMPLICIT(bigint,([@PageNo]-(1))*[@PageSize],0) AND [Expr1004]
25 1 |--Top(TOP EXPRESSION:(CONVERT_IMPLICIT(bigint,[@PageNo]*[@PageSize],0)))
25 1 |--Sequence Project(DEFINE:([Expr1004]=row_number))
25 1 |--Segment
25 1 |--Clustered Index Seek(OBJECT:([master].[dbo].[tab01].[idx_tab01_col01]),
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[의견]
1. clustered 인덱스로 페이징 처리를 할 경우, row_number를 쓰는 방식에 비하여 읽는 page 수는 동일하나 로직이
단순화되어지면서 order by 로직을 사용하는 것이 조금이지만 효율적인 것으로 보입니다.
(단계가 축소되어짐을 실행계획을 보면 알 수 있음.)
2. nonclustered 인덱스로 페이징 처리를 할 경우, row_number 함수를 사용할 경우 서브쿼리에서는 Key값만 가지고
온 다음에, 최종적으로 화면에 보여줄 글에 대해서만 데이터페이지에 접근할 수 있기 때문에 이전 방식이 좀 더
효율적일 것으로 예상되니 활용시 참고하시면 좋을 듯 합니다.
* Reference
1. http://sqlserverpedia.com/blog/sql-server-bloggers/order-by-in-denali/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlserverpedia+%28SQLServerPedia%29&utm_content=Google+Reader
2. http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.110).aspx?appId=Dev10IDEF1&l=EN-US&k=k(SQL11.PORTAL.F1)&rd=true