Bulk Insert 옵션에 따른 성능 비교

SQL Server 2008. 9. 19. 17:50

출처 : http://www.dbguide.net/dbqa/dbqa120001.jsp?mode=view&pg=3&idx=873

SSIS의 FastLoadOptions 를 확인하다 발견한 테스트 자료.
많은 시간을 들여 테스트 한 자료를 공유해 주신 한대성님께 감사드립니다~ ^^

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

Bulk Insert 옵션에 따른 성능 비교

한대성

MS SQL Server MVP

에이디컨설팅 | SQLLeader.com 운영자

이전에 SQL Server 2005에서의 대량의 데이터를 로딩한 방법 별 수행 성능을 비교한 글을 올린 적이 있습니다. OPENROWSET, BCP, BULK INSERT, SSIS를 이용하여 데이터를 로딩하는 속도 비교를 측정한 글입니다. (http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005TSQL&intSeq=351)

이번 글은 BULK INSERT 명령을 이용할 때 여러 경우에 대한 성능 비교 결과를 정리한 것입니다.

다음과 같은 형태의 10,000,000 건의 텍스트 파일을 Bulk Insert 명령을 이용하여 테이블로 로딩할 때 옵션에 따른 수행 시간 비교입니다. 각 경우에 대해 3회 반복 수행을 한 후, 평균 소요 시간을 이용하여 비교하였습니다.

[테스트 파일 - TestFile.txt]

Int형 순번 열, 100자리 문자 열 : 10,000,000 건

[저장 테이블 - TESTTABLE]

USE TEMPDB

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

--TEST TABLE

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

CREATE TABLE TESTTABLE

(

SEQ INT,

COL CHAR(100)

)

※ DB File Size 증가로 인한 지연을 막기 위해 DB Size를 충분히 크게 늘린 후 수행함.

TEST 1) 인덱스가 없는 테이블에 로딩

--인덱스가 없는 테이블에 로딩

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n'

)

처리 시간

1회 - 00:01:48

2회 - 00:01:50

3회 - 00:01:49

평균 - 00:01:49 (109초)

TEST 2) 인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 적용

--인덱스가 없는 테이블에 로딩 + TABLOCK 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n',

TABLOCK

)

처리 시간

1회 - 00:00:34

2회 - 00:00:33

3회 - 00:00:34

평균 - 00:00:34 (34초)

TEST 3) 인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 적용 + 일괄 처리 크기 100,000으로 설정

--인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 + RPB 100,000

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n',

TABLOCK,

ROWS_PER_BATCH = 100000

)

처리 시간

1회 - 00:00:33

2회 - 00:00:33

3회 - 00:00:34

평균 - 00:00:33 (33초)

[Clustered Index 생성]

--Seq 컬럼에 Clustered Index 생성

CREATE CLUSTERED INDEX CIX_TESTTABLE ON TESTTABLE(SEQ)

TEST 4) Clustered Index가 있는 테이블에 로딩

--Clustered Index가 있는 테이블에 로딩

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n'

)

처리 시간

1회 - 00:03:40

2회 - 00:03:39

3회 - 00:03:39

평균 - 00:03:39 (219초)

TEST 5) Clustered Index가 있는 테이블에 로딩 + 일괄 처리 크기 100,000

-- Clustered Index가 있는 테이블에 로딩

-- RPB 100,000

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n',

ROWS_PER_BATCH = 100000

)

처리 시간

1회 - 00:02:49

2회 - 00:02:47

3회 - 00:02:48

평균 - 00:02:48 (168초)

TEST 6) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션

-- Clustered Index가 있는 테이블에 로딩

-- ORDER 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n',

ORDER(SEQ ASC)

)

처리 시간

1회 - 00:02:21

2회 - 00:02:21

3회 - 00:02:20

평균 - 00:02:21 (141초)

TEST 7) Clustered Index가 있는 테이블에 로딩 + 일괄 처리 크기 100,000 + ORDER 옵션

-- Clustered Index가 있는 테이블에 로딩

-- RPB 100,000 + ORDER 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n',

ROWS_PER_BATCH = 100000,

ORDER(SEQ ASC)

)

처리 시간

1회 - 00:02:20

2회 - 00:02:21

3회 - 00:02:21

평균 - 00:02:21 (141초)

TEST 8) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션 + TABLOCK 옵션

-- Clustered Index가 있는 테이블에 로딩

-- ORDER 옵션 + TABLOCK 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n',

ORDER(SEQ ASC),

TABLOCK

)

처리 시간

1회 - 00:00:37

2회 - 00:00:37

3회 - 00:00:37

평균 - 00:00:37 (37초)

TEST 9) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션 + TABLOCK 옵션 + 일괄 처리 크기 100,000

-- Clustered Index가 있는 테이블에 로딩

-- RPB 100,000 + ORDER 옵션 + TABLOCK 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n',

ROWS_PER_BATCH = 100000,

ORDER(SEQ ASC),

TABLOCK

)

처리 시간

1회 - 00:00:36

2회 - 00:00:37

3회 - 00:00:37

평균 - 00:00:37 (37초)

Clustered Index가 없는 힙(Heap) 테이블에 Bulk Insert 명령을 이용하여 로딩할 경우, TABLOCK의 옵션 설정에 따라 3배 정도의 속도 차이가 있습니다. 하지만, 일괄 처리 크기(Rows Per Batch) 설정은 성능에 별 영향을 미치지 않습니다. (34초 : 33초)

TABLOCK 옵션은 Bulk Insert 작업 수행 시 해당 테이블에 테이블 수준의 잠금을 설정함으로써, 하위 수준의 잠금 사용 시 발생하는 잠금 경합(Escalation)을 줄일 수 있을 뿐만 아니라 잠금을 설정하고 해제하는 단계를 줄여주기 때문에 처리 성능이 크게 향상될 수 있습니다. (옵션 적용 전 - 109초, 옵션 적용 - 34초) 하지만, 이 옵션을 적용하여 로딩할 경우에는 WITH (NOLOCK) 옵션 또는 WITH (READUNCOMMITTED) 옵션을 사용하더라도 로딩 중에 해당 테이블의 데이터를 읽을 수 없습니다. 따라서 로딩 중에 다른 부분에서 사용되는 테이블인 경우에는 이 옵션을 적용할 경우 블로킹을 발생시킬 수 있기 때문에 주의해야 합니다.

Clustered Index가 설정되어 있는 테이블인 경우에는 Heap 테이블과는 달리 몇 가지 옵션을 더 설정할 수 있습니다.

우선 옵션 없이 단순히 Bulk Insert를 수행할 경우, 입력된 데이터에 대해 인덱스 구성 작업을 수행해야 하기 때문에 힙 테이블의 로딩과 비교했을 때 훨씬 더 많은 시간이 소요됩니다. (Heap - 109초, Clustered Index - 219초)

일괄 처리 크기(Rows Per Batch)를 100,000건으로 설정하게 되면 전체 건(10,000,000건)의 인덱스를 한 번에 구성하는 대신 100,000건씩 나누어서 구성하기 때문에 처리 시간이 단축될 수 있습니다. (일괄 처리 - 219초, RPB 100,000건 168초)

만약 입력되는 데이터가 적재될 테이블의 Clustered 열과 동일한 순서로 정렬된 데이터인 경우에는 ORDER(열 이름 [ASC | DESC]) 옵션을 이용하여 처리 시간을 단축 시킬 수 있습니다. 본 테스트에서는 텍스트 파일의 첫 번째 열로 정렬된 데이터이며, 이 데이터가 테이블의 SEQ열로 입력되기 때문에 ORDER 옵션을 적용할 경우, 처리 시간이 단축됩니다. (옵션 미 적용 - 219초, ORDER 옵션 적용 - 141초)

또한 Heap에서와 마찬가지로 TABLOCK 옵션을 설정하여 처리할 경우, 처리 시간이 크게 단축됩니다. 10,000,000건의 텍스트 파일에 대해 아무런 옵션 없이 Bulk Insert 작업을 수행한 경우에 비해 ORDER, TABLOCK 옵션을 이용하여 처리한 수행 시간이 6배 정도 빠르게 나타났습니다. (219초 : 37초)