최근 포토로그


[MS-SQL] 속도 향상을 위한 테이블 분할에 대한 고찰(조회에 대한 고찰)(3) MSSQL과 ORACLE

그냥 데이터를 가지고 하니 체감이 좀 덜해서 예전에 쓰던 사이트의 DB로 테이블 분할에 대한 속도 향상을 체감해 보겠다.


OMM_OUT_HIST -> 출고를 담당하는 테이블인데 갯수가 3606539개이다.

대략 3백6십만건 쯤 된다고 생각하시면 되겠다.

들어가 있는 칼럼은 61개 짜리이다.
물론 REMARK부터 시작해서 온갖 잡 칼럼들이 다 모여 있다.


이 테이블로 아주 간단한 칼럼 몇개를 추출해서 테이블 OMM_OUT_HIST_MAIN 을 만들어 보겠다.

========================================================================
SELECT COM_CD, DIV_CD, BILL_SHEET_NO, ITEM_ID, STORE_CD, OUT_DT, CREATE_CLASS, SLIP_CLASS_CD
  INTO OMM_OUT_HIST_MAIN  
  FROM OMM_OUT_HIST
========================================================================


그리고 조건을 주어 검색해 보겠다.

========================================================================
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT COM_CD, DIV_CD, BILL_SHEET_NO, ITEM_ID, STORE_CD, OUT_DT, CREATE_CLASS, SLIP_CLASS_CD 
  FROM OMM_OUT_HIST 
 WHERE OUT_DT >= '20140101' AND OUT_DT <= '20140131'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
========================================================================
테이블 'Workfile'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'omm_out_hist'. 검색 수 3, 논리적 읽기 수 76495, 물리적 읽기 수 6, 미리 읽기 수 76252, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 CPU 시간 = 8549ms, 경과 시간 = 14333ms
========================================================================

-> 10만건에 14초의 시간이 걸렸다.




========================================================================
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT COM_CD, DIV_CD, BILL_SHEET_NO, ITEM_ID, STORE_CD, OUT_DT, CREATE_CLASS, SLIP_CLASS_CD 
  FROM OMM_OUT_HIST_MAIN 
 WHERE OUT_DT >= '20140101' AND OUT_DT <= '20140131'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
========================================================================
SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 0ms.

(215332개 행이 영향을 받음)
테이블 'OMM_OUT_HIST_MAIN'. 검색 수 1, 논리적 읽기 수 40189, 물리적 읽기 수 0, 미리 읽기 수 40122, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 CPU 시간 = 1186ms, 경과 시간 = 6977ms

 SQL Server 실행 시간: 
 CPU 시간 = 0ms, 경과 시간 = 0ms
========================================================================

-> 같은 데이터로 10만건에 7초의 시간이 걸렸다.



즉, 테이블이 커지면, 즉 칼럼의 갯수가 커지면 물리적 데이터가 적은 테이블이 속도가 더 빠르다는 결론이다.

이것은 확실히 유의미한 데이터 이다.



자 그럼 거의 풀 스캔해보자. 
총 3백6시만건 쯤 되는데, 3백만건 조회해 보자.

========================================================================
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT COM_CD, DIV_CD, BILL_SHEET_NO, ITEM_ID, STORE_CD, OUT_DT, CREATE_CLASS, SLIP_CLASS_CD 
  FROM OMM_OUT_HIST 
 WHERE OUT_DT >= '20100101' AND OUT_DT <= '20140131'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
========================================================================
SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 0ms.

(2993323개 행이 영향을 받음)
테이블 'omm_out_hist'. 검색 수 1, 논리적 읽기 수 236928, 물리적 읽기 수 0, 미리 읽기 수 233244, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 CPU 시간 = 7348ms, 경과 시간 = 81681ms

 SQL Server 실행 시간: 
 CPU 시간 = 0ms, 경과 시간 = 0ms
========================================================================

-> 약 1분 21초 걸렸다.



그럼 캄럼을 줄인 테이블을 조회해보자.

========================================================================
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT COM_CD, DIV_CD, BILL_SHEET_NO, ITEM_ID, STORE_CD, OUT_DT, CREATE_CLASS, SLIP_CLASS_CD 
  FROM OMM_OUT_HIST_MAIN 
 WHERE OUT_DT >= '20100101' AND OUT_DT <= '20140131'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
========================================================================
SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 0ms.

(2993323개 행이 영향을 받음)
테이블 'OMM_OUT_HIST_MAIN'. 검색 수 1, 논리적 읽기 수 40189, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 CPU 시간 = 6412ms, 경과 시간 = 79694ms

 SQL Server 실행 시간: 
 CPU 시간 = 0ms, 경과 시간 = 0ms
========================================================================

-> 약 1분 19초 걸렸다.


즉, 풀데이터를 스캔한다면 결과값이 유의미하지 않다는 결론이다.






또한, 데이터의 양이 어느 정도이냐에 따라서 속도가 빨라지기도 느려지기도 한다.


13만건 정도의 데이터를 조회한다고 가정하면,
테이블 'omm_out_hist'. 검색 수 3, 논리적 읽기 수 75550, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
 SQL Server 실행 시간: 
 CPU 시간 = 7192ms, 경과 시간 = 10377ms

VS

테이블 'OMM_OUT_HIST_MAIN'. 검색 수 1, 논리적 읽기 수 40189, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 CPU 시간 = 1092ms, 경과 시간 = 4620ms


즉, 10대 4.5초 정도로, 유의미한 속도 향상이 있지만,



행의 수를 극단적으로 낮추면, 즉, 3만건 이내를 조회한다고 가정하고 쿼리를 날리면

테이블 'omm_out_hist'. 검색 수 1, 논리적 읽기 수 115776, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 CPU 시간 = 530ms, 경과 시간 = 978ms

VS 

(28412개 행이 영향을 받음)
테이블 'OMM_OUT_HIST_MAIN'. 검색 수 1, 논리적 읽기 수 40189, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 CPU 시간 = 858ms, 경과 시간 = 1672ms

로 오히려 반대의 경우도 발생한다.



즉, 데이터 정규화를 거친 테이블이 반드시 속도를 향상시켜주는 것은 아니라는 것이다.





덧글

댓글 입력 영역