최근 포토로그


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

MS SQL 에서 속도 향상을 위하여 테이블을 쪼개야 할 필요가 있는가?
에 대한 여러가지 문서를 찾을 일이 있었다.

정말 어렵고도 난해한 일이었다.

여기서 필요한 내용은

같은 사이즈의 테이블을 Row 갯수와 Column 갯수에 대하여 성능 저하와 향상을 기대할 수 있는가에 대한 문제다.

1. columun 수나 Row 수가 입력에 대한 속도 영향을 미치냐 하는 것.
2. columun 수나 Row 수가 조회에 대한 속도 영향을 미치냐 하는 것.


1번의 경우는 앞에서 보았다.

2번 조회는 어떻게 될까?





일단, mssql 에서 제공하는 파티션(partition) 에 대한 설명을 좀 하자.
파티션이란? MSSQL 엔터프라이즈 버전 이상에서 지원하는 테이블을 쪼개주는 기능이다.
이것을 "수평분할"이라고 명하겠다.


그리고 "수직분할"이라고 명칭할 수 있는 단어가 있는데,

그것은, 

수직 테이블 파티셔닝
수직 분할의 예


요로코롬 테이블을 쪼개주는 것이다.
데이터베이스 정규화라고도 말할 수 있겠죠?


자. 그럼 수평분할을 하면 속도가 향상되는 가?

쿼리로 실험해 보자

EmployeeReports 라는 테이블을 만들어 보자. 100만건 짜리로


---------------------------------------------
CREATE TABLE EmployeeReports
(
ReportID int IDENTITY (1,1) NOT NULL,
ReportName varchar (100),
ReportNumber varchar (20),
ReportDescription varchar (max)
CONSTRAINT EReport_PK PRIMARY KEY CLUSTERED (ReportID)
)

DECLARE @i int
SET @i = 1

BEGIN TRAN
WHILE @i<= 1000000
BEGIN
INSERT INTO EmployeeReports
(
ReportName,
ReportNumber,
ReportDescription
)
VALUES
(
'ReportName',
CONVERT (varchar (20), @i),
REPLICATE ('Report', 1000)
)
SET @i=@i+1
END
COMMIT TRAN
GO
---------------------------------------------

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT er.ReportID, er.ReportName, er.ReportNumber
  FROM dbo.EmployeeReports er
 WHERE er.ReportNumber LIKE '%33%'
 

---------------------------------------------

라고 쿼리를 날려보자



---------------------------------------------
테이블 'EmployeeReports'. 검색 수 9, 논리적 읽기 수 113291, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 1차 : CPU 시간 = 4493ms, 경과 시간 = 28965ms
 2차 : CPU 시간 = 4773ms, 경과 시간 = 28904ms
 3차 : CPU 시간 = 4665ms, 경과 시간 = 28907ms

---------------------------------------------

참고로 내 화면에서는 이런 결과가 떴다. 4493밀리초.


자 그럼 수직분할 해보자.

---------------------------------------------
CREATE TABLE ReportsDesc
( ReportID int not null,
  ReportDescription varchar(max)
  CONSTRAINT PK_ReportDesc PRIMARY KEY CLUSTERED (ReportID)
)
 
CREATE TABLE ReportsData
(
ReportID int NOT NULL,
ReportName varchar (100),
ReportNumber varchar (20),
CONSTRAINT DReport_PK PRIMARY KEY CLUSTERED (ReportID)
)
INSERT INTO dbo.ReportsData
(
    ReportID,
    ReportName,
    ReportNumber
)
SELECT er.ReportID,
er.ReportName,
er.ReportNumber
FROM dbo.EmployeeReports er

---------------------------------------------


테이블 ReportsData 는  단지, 3개의 칼럼만 가지는 아주 작은 테이블로 구성되어 있다.
물론 PRIMERY KEY는 동일하다.

---------------------------------------------
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT er.ReportID, er.ReportName, er.ReportNumber
  FROM ReportsData er
 WHERE er.ReportNumber LIKE '%33%'

---------------------------------------------

조회를 해보았다.

결과는?

---------------------------------------------
테이블 'ReportsData'. 검색 수 1, 논리적 읽기 수 421, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
  1차 : CPU 시간 = 452ms, 경과 시간 = 955ms
  2차 : CPU 시간 = 422ms, 경과 시간 = 955ms
  3차 : CPU 시간 = 390ms, 경과 시간 = 970ms


---------------------------------------------

 참고로 내 화면에서는 이런 결과가 떴다. 452밀리초.

어라?
같은 칼럼을 조회했는데, 어째서 10배 차이가 나는 값으로 속도가 나왔을까?




좋다 그럼  PRIMERY KEY를 조회조건으로 주고 검색해보자. LIKE문은 속도상 문제가 있을 수 있으니,
라고 생각하고 조회해 보았다.

---------------------------------------------
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT er.ReportID, er.ReportName, er.ReportNumber
  FROM dbo.EmployeeReports er
 WHERE er.ReportID >= 10000 and er.ReportID <= 500000
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
---------------------------------------------
SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 0ms.
테이블 'EmployeeReports'. 검색 수 1, 논리적 읽기 수 99370, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 1차 : CPU 시간 = 2870ms, 경과 시간 = 14569ms
 2차 : CPU 시간 = 2948ms, 경과 시간 = 14561ms
 3차 : CPU 시간 = 2855ms, 경과 시간 = 14572ms


---------------------------------------------

2870밀리초다.

---------------------------------------------
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT er.ReportID, er.ReportName, er.ReportNumber
  FROM ReportsData er
 WHERE er.ReportID >= 10000 and er.ReportID <= 500000
SET STATISTICS IO OFF
SET STATISTICS TIME OFF 
---------------------------------------------

SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 0ms.
테이블 'ReportsData'. 검색 수 1, 논리적 읽기 수 418, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
  1차 : CPU 시간 = 250ms, 경과 시간 = 9400ms
  2차 : CPU 시간 = 328ms, 경과 시간 = 9514ms
  3차 : CPU 시간 = 343ms, 경과 시간 = 9528ms


---------------------------------------------

250밀리초다.



7배 이상의 차이를 보여주고 있다.


뭐 이런 결과물이 항상 나오지는 않을 것이다.
여러가지 변수에 의해 수직분할이 안하는 것보다 나은 결과를 보여줄 것이라고 생각할 수 있는 타당한 근거가 되는 것이다.



머 수평분할은 다음에 쓰겠다.
너무 쓸게 많어.





참조 :
http://www.sqlshack.com/database-table-partitioning-sql-server/





덧글

  • Cene 2015/05/15 12:28 # 답글

    SQLD 과정에서였던가, 정규화를 할수록 좋아지는 게 있는 반면 조인 명령은 느려진다는 내용을 들었네요
  • 나기드 2015/05/18 21:34 #

    반드시 정규화가 답은 아니죠.

    모든 건 케이스 바이 케이스죠.

    확인할 필요는 있죠.
댓글 입력 영역