반응형

/*******************************************************************************************************************
-- Title : [SQL2017] COMPRESS/DECOMPRESS를 이용한 데이터 압축 비교
-- Reference : www.mssqltips.com
-- Key word : data compression compress decompress fulltext full text max
*******************************************************************************************************************/

■ Scripts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
/*
-- Create DB
*/
create database TestDB;
go
 
use testdb;
go
 
 
/*
-- Drop table
*/
drop table if exists ttt;
drop table if exists ttt_comp;
 
 
/*
-- Create table
*/
 
create table ttt
( id int
, sect nvarchar(10)
, aa nvarchar(10)
, bb nvarchar(max)  -- for uncompressing column
);
 
create table ttt_comp
( id int
, sect nvarchar(10)
, aa nvarchar(10)
, bb varbinary(max)  -- for compressing column
);
 
 
/*
-- 1. Insert 비교
   replicate는 8000자까지 암시적으로 잘라버리기에 max길이로 넣을려면 명시적으로 지정해야 함.
*/
set statistics io on;
set statistics time on;
 
--▶▶ 1-1. ttt 저장
insert into ttt values
(1'AA''a11111', replicate(cast('b1' as nvarchar(max)), 10000000)),
(2'AA''a22222', replicate(cast('b2' as nvarchar(max)), 10000000)),
(3'AA''a33333', replicate(cast('b3' as nvarchar(max)), 10000000)),
(4'BB''a44444'NULL),
(5'BB'NULL,     replicate(cast('b5' as nvarchar(max)), 10000000)),
(6'BB''a66666', replicate(cast('b6' as nvarchar(max)), 10000000)),
(7'CC''a77777', replicate(cast('b6' as nvarchar(max)), 10000000)),
(8'DD''a88888'NULL),
(9'DD''a99999'NULL);
 
--▶▶ 1-2. ttt_comp 저장
insert into ttt_comp values
(1'AA''a11111', compress(replicate(cast('b1' as nvarchar(max)), 10000000))),
(2'AA''a22222', compress(replicate(cast('b2' as nvarchar(max)), 10000000))),
(3'AA''a33333', compress(replicate(cast('b3' as nvarchar(max)), 10000000))),
(4'BB''a44444'NULL),
(5'BB'NULL,     compress(replicate(cast('b5' as nvarchar(max)), 10000000))),
(6'BB''a66666', compress(replicate(cast('b6' as nvarchar(max)), 10000000))),
(7'CC''a77777', compress(replicate(cast('b6' as nvarchar(max)), 10000000))),
(8'DD''a88888'NULL),
(9'DD''a99999'NULL);
 
 
/*
-- 2. Select 비교
*/
--▶▶ 2-1. ttt 읽기
select *, len(bb) "len_bb" from ttt;
 
--▶▶ 2-2. ttt_comp 읽기(varbinary 상태)
select *, len(bb) "len_bb" from ttt_comp;
 
--▶▶ 2-3. ttt_comp 읽기(decompress 적용)
select id, sect, aa, cast(decompress(bb) as nvarchar(max)) "bb" from ttt_comp;
 
 
/*
-- 3. Select ~ where 비교
*/
--▶▶ 3-1. ttt 읽기
select * from ttt where bb like '%b1%';
 
 --▶▶ 3-2. ttt_comp 읽기(decompress 적용)
select id, sect, aa, cast(decompress(bb) as nvarchar(max)) "bb" from ttt_comp 
where decompress(bb) like '%b1%';
 
 
/*
-- 4. 용량 비교
*/
 
exec sp_spaceused 'ttt';
exec sp_spaceused 'ttt_comp';
cs



■ Statistics

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

-- 1. Insert 비교

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


--▶▶ 1-1. ttt 저장

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 1ms.

테이블 'ttt'. 검색 수 0, 논리적 읽기 수 9, 물리적 읽기 수 0, 미리 읽기 수 0,

            LOB 논리적 읽기 수 125232, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 49, 물리적 읽기 수 0, 미리 읽기 수 0, 

            LOB 논리적 읽기 수 225858, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 101472.


(9개 행이 영향을 받음)


(1개 행 적용됨)


 SQL Server 실행 시간: 

 CPU 시간 = 797ms, 경과 시간 = 2120ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms



--▶▶ 1-2. ttt_comp 저장

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 1ms.

테이블 'ttt_comp'. 검색 수 0, 논리적 읽기 수 9, 물리적 읽기 수 0, 미리 읽기 수 0, 

            LOB 논리적 읽기 수 24, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 14689, 물리적 읽기 수 0, 미리 읽기 수 0, 

            LOB 논리적 읽기 수 255120, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 125544.


(9개 행이 영향을 받음)


(1개 행 적용됨)


 SQL Server 실행 시간: 

 CPU 시간 = 2718ms, 경과 시간 = 2955ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

 

 

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

-- 2. Select 비교

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


--▶▶ 2-1. ttt 읽기

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


(9개 행이 영향을 받음)

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

            LOB 논리적 읽기 수 265887, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 291312.


(1개 행 적용됨)


 SQL Server 실행 시간: 

 CPU 시간 = 250ms, 경과 시간 = 2671ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms


 

--▶▶ 2-2. ttt_comp 읽기(varbinary 상태)

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


(9개 행이 영향을 받음)

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

            LOB 논리적 읽기 수 573, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 408.


(1개 행 적용됨)


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 3ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

 

 

--▶▶ 2-3. ttt_comp 읽기(decompress 적용)

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


(9개 행이 영향을 받음)

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

            LOB 논리적 읽기 수 54, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 14545, 물리적 읽기 수 0, 미리 읽기 수 0, 

            LOB 논리적 읽기 수 768378, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 386300.


(1개 행 적용됨)


 SQL Server 실행 시간: 

 CPU 시간 = 2125ms, 경과 시간 = 4543ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms


 

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

-- 3. Select ~ where 비교

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


--▶▶ 3-1. ttt 읽기

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 3ms.


(1개 행 적용됨)

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

            LOB 논리적 읽기 수 265293, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 295772.


(1개 행 적용됨)


 SQL Server 실행 시간: 

 CPU 시간 = 8328ms, 경과 시간 = 8700ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

 

--▶▶ 3-2. ttt_comp 읽기(decompress 적용)

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


(1개 행 적용됨)

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

            LOB 논리적 읽기 수 63, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 16959, 물리적 읽기 수 0, 미리 읽기 수 0, 

            LOB 논리적 읽기 수 1061037, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 693656.


(1개 행 적용됨)


 SQL Server 실행 시간: 

 CPU 시간 = 16954ms, 경과 시간 = 17317ms

SQL Server 구문 분석 및 컴파일 시간: 

   CPU 시간 = 0ms, 경과 시간 = 0ms.


 SQL Server 실행 시간: 

 CPU 시간 = 0ms, 경과 시간 = 0ms 



■ Results
    * GZIP 알고리즘 사용
    * Varbniary(max) 유형의 바이너리 데이터 반환
    * Decompress시 GZIP 알고리즘으로 압축해제하고 Varbinary(max) 유형의 데이터 반환
      -> 필요시 CAST(~NVARCHAR()) 필요
    * 조회나 저장은 압축으로 인해 성능 저하
    * 단, 저장 용량 축소에는 효과적.
           DB에서 빨리 가져가고 Client에서 압축풀어 사용하는 방법도 유용.

      



반응형

+ Recent posts