TB_A 의 데이터는 약 500만건
조건 테스트시 사용한 대상 건수는 10만건~60만건 이었습니다.
테스트 DB 용량은 5GB입니다.
대상 DB는 SQL2005 STD SP4 입니다.

결과는 보시다시피 RANK사용시는 미세한 차이를 보이고
ROW_NUMBER 사용시는 10배 가얀 차이를 보입니다.
가장 최근 결과가 필요하기에 ID DESC를 넣어야 했기에
ID ASC 시 ROW_NUMBER가 효율이 좋지만
RANK로 처리했습니다.
또한 현재는 rank_num을 매김에도 불구하고 bottom 값을 추가로 가져올 방법이 적당히 안보여
카운트를 추가로 처리하고 있습니다.(실제 카운트를 넣어도 0.1초 정도만 추가되는걸 확인 했습니다만..)
상황에 따라 행 번호가 필요한 부분 에서는 rank_num 매기는 서브쿼리 부분을 테이블 x로 처리해서
rank_num을 넘겨 처리하고 있습니다.

기본 쿼리 형태

select 데이터
FROM TB_A  a, tb_B b
where a.id in (
 select a.id from
  (select a.ID,ROW_NUMBER() OVER (ORDER BY a.ID) as rowsnum
   FROM TB_A a
   where 검색조건 ) a
 where a.rowsnum between 10 and 20)
 and 테이블조인조건

위의 상태에서 정렬 방법 변경 테스트

1번 ROW_NUMBER() OVER (ORDER BY a.ID)

(11개 행이 영향을 받음)
테이블 'TB_B'. 검색 수 0, 논리적 읽기 수 22, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'TB_A'. 검색 수 1, 논리적 읽기 수 14829, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

(12개 행이 영향을 받음)

SQL Server 실행 시간:
   CPU 시간 = 422ms, 경과 시간 = 435ms.

2번 ROW_NUMBER() OVER (ORDER BY a.ID DESC)

(11개 행이 영향을 받음)
테이블 'TB_B'. 검색 수 0, 논리적 읽기 수 22, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'TB_A'. 검색 수 1, 논리적 읽기 수 143214, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

(12개 행이 영향을 받음)

SQL Server 실행 시간:
   CPU 시간 = 5219ms, 경과 시간 = 5209ms.

3번 RANK() OVER (ORDER BY a.ID)

(11개 행이 영향을 받음)
테이블 'TB_B'. 검색 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'TB_A'. 검색 수 5, 논리적 읽기 수 3091, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

(16개 행이 영향을 받음)

SQL Server 실행 시간:
   CPU 시간 = 1281ms, 경과 시간 = 819ms.

4번 RANK() OVER (ORDER BY a.ID DESC)

(11개 행이 영향을 받음)
테이블 'TB_B'. 검색 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'TB_A'. 검색 수 5, 논리적 읽기 수 3091, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

(16개 행이 영향을 받음)

SQL Server 실행 시간:
   CPU 시간 = 1343ms, 경과 시간 = 848ms.

현재 사용 쿼리

SELECT 데이터,전체카운트  
FROM tb_A a (NOLOCK) LEFT OUTER JOIN tb_E e (NOLOCK) on (a.E_id = e.E_id ) 
,tb_C c (NOLOCK)  ,tb_B b (NOLOCK),tb_D d (NOLOCK) ,
( SELECT count(*) as cnt   FROM TB_A
WHERE 조건) y 
WHERE a.id in (
 SELECT x.id
 FROM  (
  SELECT ID,RANK() OVER (ORDER BY ID DESC) as rank_num 
  FROM TB_A
  WHERE 조건
 ) x 
 WHERE x.rank_num BETWEEN (20*0)+1 AND (20*1) -- 페이징 부분

AND 조인조건
ORDER BY a.id DESC

트위터 @ 성대중(SQL SERVER MVP) 님의 조언을 받아 TOP TOP 방식으로 변경해 보았다.

변경 쿼리

SELECT 데이터

FROM 대상테이블s , 대상카운트 ,

(SELECT TOP 표시수 x.id

         FROM  (

                  SELECT TOP 표시수*(페이지번호+1) ID 

                  FROM 대상테이블

                  WHERE 조건

                  ORDER BY ID DESC

         ) x  ORDER BY ID) z

WHERE a.id = z.id

AND 조인조건

ORDER BY a.id DESC

비용 비교
RANK
CPU 시간 = 63ms, 경과 시간 = 76ms.
CPU 시간 = 63ms, 경과 시간 = 76ms.
CPU 시간 = 62ms, 경과 시간 = 76ms.

CPU 시간 = 672ms, 경과 시간 = 500ms.
CPU 시간 = 703ms, 경과 시간 = 457ms.
CPU 시간 = 751ms, 경과 시간 = 477ms.

TOP TOP
CPU 시간 = 15ms, 경과 시간 = 19ms.
CPU 시간 = 15ms, 경과 시간 = 18ms
CPU 시간 = 16ms, 경과 시간 = 17ms.


CPU 시간 = 297ms, 경과 시간 = 475ms.
CPU 시간 = 375ms, 경과 시간 = 468ms.
CPU 시간 = 343ms, 경과 시간 = 486ms.

 ..서브트리 코스트에선 36대 0.8로 1/50로 줄어 든것을 확인 했는데..10만건 테스트 시 시피유,I/O 타임이 생각보다 줄지 않았다.
왜 일까..한번 고민해봐야 할듯..그리고 대상건을 30만건으로 늘려 비교시엔 타임이 오히려 RANK가 더 좋게
나오는 현상도 원인을 찾아 봐야 할듯..

by Tomoya 2011.06.08 09:44
| 1 2 3 4 5 6 7 8 ··· 11 |