페이징 쿼리 종류별로 성능을 비교한 좋은 자료가 있어서 상당부분 인용했습니다.
"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
<출처> http://tit99hds.egloos.com/888201
