MS-SQL 페이징 쿼리 종류별 성능 비교

출처 :  http://www.mkexdev.net/Article/Content.aspx?parentCategoryID=2&categoryID=24&ID=304 

mkex.pe.kr 에 '허동석' 님이 작성해 주신 글을 옮겨 옵니다


페이징 쿼리 종류별로 성능을 비교한 좋은 자료가 있어서 상당부분 인용했습니다.

"ex)"에 들어가는 샘플 쿼리는 바투 락커룸 DB에 파일첨부(TB_AttachFile) 테이블을 대상으로 페이징 쿼리를 작성해 봤습니다.

5번에 표시된 내용이 데이터 건수에 상관없이 실질적으로 가장 빠르지만 우리 시스템에 적용할 수 있는지는 의문이 갑니다. 테이블 별로 인덱스 생성이 필요할 수 있고 중간에 데이터가 삭제되거나 어떤 반응이 생길 때 문제가 될 소지가 있는지도 조사해야 할 것 같습니다.

참고로 웹젠 빌링에서 사용하던 페이징 쿼리는 4번을 이용했었습니다.
그리고 2번에 샘플 쿼리를 보면 TB_AttachFile 테이블에 FileGuid가 None Clustered Index로 걸려 있어서 서브쿼리 내에서도 ORDER BY FileGuid DESC를 해줘야합니다.(즉 ORDER BY 를 2번 해야한다는..)
대부분의 기본키들이 None Clustered Index 라 이 점에 대한 이슈도 있습니다.

1. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]  

  레코드셋의 AbsolutePage를 이용해서 페이징을 했습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 



2. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호필드] NOT IN (SELECT TOP [제거할 게시물수] [글번호필드] FROM [테이블명])

   예전에 태요 사이트에서 보았던 쿼리 구문입니다.. NOT IN 때문에.. 문제시 되었던 쿼리구문이죠.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid NOT IN 
(
 SELECT TOP 0 FileGuid 
 FROM TB_AttachFile
 ORDER BY FileGuid DESC
) 
ORDER BY FileGuid DESC 


3. SELECT TOP [불러올 총 게시물수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] IN (SELECT TOP [페이지출력 갯수] [글번호] FROM
   (SELECT TOP [불러올 총 게시물수] [글번호] FROM [테이블 명]) AS A ORDER BY [글번호])
   ORDER BY [글번호] DESC

   이 쿼리 구문은 2번의 쿼리 구문의 문제점을 보완한 구문입니다. NOT IN 대신에 IN을 사용 했습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid IN 
(
 SELECT TOP 123 FileGuid -- 총 데이터수 - ( (페이지수 - 1) * 10) // 1페이지 : 123 - 0, 2페이지 : 123 - 10 ...
 FROM 
 (
  SELECT FileGuid 
  FROM TB_AttachFile 
 )AS A 
 ORDER BY FileGuid
) 
ORDER BY FileGuid DESC 


4. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블 명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호] FROM [테이블명]) AS A)

   4번째 쿼리 구문은 IN, NOT IN이 아닌 출력할 마지막 글번호 바로 앞이 글번호를 찾아서 처리를 해주는
   쿼리 구문입니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid <=
(
 SELECT MIN(FileGuid)
 FROM 
 (
  SELECT TOP 11 FileGuid -- (현재페이지수-1) * 10 + 1  
  FROM TB_AttachFile
  ORDER BY FileGuid DESC
 )AS A 
) 
ORDER BY FileGuid DESC 


5. SELECT TOP [페이지 출력갯수] [출력 필드명] FROM [테이블명]
   WHERE [글번호] <= (SELECT MIN([글번호])
   FROM (SELECT TOP [제거할 게시물수] + 1 [글번호]
   FROM [테이블명] WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]) AS A
   WHERE [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호])
   AND [인덱스] = [시작인덱스번호] AND [인덱스] = [끝인덱스번호]

   5번재 쿼리는.. 글에 인덱스(가칭)라는 필드를 하나 더 추가 해서.. 글 기본 2000개마다 (가변적입니다)
   인덱스를 증가 시켰습니다. 즉 2000개를 하나의 묶음으로 만든것입니다.
   그 인덱스를 기준으로 처리를 해주었습니다.

ex)

SELECT TOP 10 FileGuid, FileName, Capacity, RegDate 
FROM TB_AttachFile 
WHERE FileGuid <=
(
 SELECT MIN(FileGuid)
 FROM 
 (
  SELECT TOP 11 FileGuid -- (현재페이지수-1) * 10 + 1  
  FROM TB_AttachFile
  WHERE IDX_FileGuid > 350  -- 350은 이전 페이지의 끝 게시물 번호. (350보다 큰 10개를 얻음.)
  ORDER BY FileGuid DESC
 )AS A 
) 
ORDER BY FileGuid DESC 


결과.

게시물은 100만개를 넣고 테스트를 했습니다
서버정보 : CPU : p4-1.8, RAM : 768Mb, 컴팩 프리자리오 2820AP, 환경 : 윈도우2003 MSSQL2000
처음페이지(1), 마지막 페이지(50000) 처리 시간이 아래와 같습니다.(단위 ms) 
 첫페이지 실행    끝페이지 실행
1 :  273                 11476.56
2 :  289                 4406.25
3 :  289                 2695.31
4 :  289                 1218.75
5 :  7.81                23.44