대용량 로드를 위한 BULK INSERT 옵션

대용량 로드를 위한 BULK INSERT 옵션

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

 

대용량 데이터를 로드하는 방법은 다양하게 있다. 그 중 많이 사용하는 BULK INSERT의 다양한 옵션을 통해서 더 빠르게 데이터를 삽입하는 방법에 대해서 알아 본다.

• SQL Server로 데이터 가져오기 : http://sqlmvp.kr/140179374125
• BULK INSERT : http://msdn.microsoft.com/ko-kr/library/ms188365.aspx

 

BULK INSERT를 위한 샘플 데이터 생성(데이터 생성 시간이 오래 걸립니다.)

-- Create sample table and indexes CREATE TABLE testtable ( [col1] [int] NOT NULL primary key clustered, [col2] [int] NULL, [col3] [int] NULL, [col4] [varchar](50) NULL);   CREATE INDEX idx_testtable_col2 on testtable (col2 asc); CREATE INDEX idx_testtable_col3 on testtable (col3 asc);   -- Load sample data into table DECLARE @val INT SELECT @val=1 WHILE @val < 5000000 BEGIN INSERT INTO testtable (col1, col2, col3, col4) VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR)) SELECT @val=@val+1 END GO

 

BULK INSERT에서 사용할 수 있도록 포맷 파일을 생성한다.

-- Create a format file bcp sw_test.dbo.testtable format nul -c -t, -f c:\testtable_formatfile.fmt -T

 

 

 

 

BULK INSERT에 사용할 데이터 파일을 생성한다.

bcp sw_test.dbo.testtable out c:\testtable.dat -c -t, -f c:\testtable_formatfile.fmt -T -S

 

 

다음 4가지 시나리오르 BULK INSERT를 실행 한다. 그리고 각 실행 결과에 대한 성능을 측정한다. 성능 측정 방법은 SQL Profiler 또는 SET STATISTICS 를 사용 한다.

1. BULK Load
2. BULK Load with TabLock
3. BULK Load with Tablock and drop/create indexes
4. BULK Load with Tablock and drop/recreate indexes andchage recovery model

 

--1. BULK load truncate table testtable   BULK INSERT dbo.testtable FROM 'C:\testtable.dat' WITH (FORMATFILE = 'C:\testtable_formatfile.fmt');     --2. BULK load with tablock truncate table testtable   BULK INSERT dbo.testtable FROM 'C:\testtable.dat' WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);     --3. BULK load with tablock and drop/recreate indexes truncate table testtable   DROP INDEX testtable.idx_testtable_col2; DROP INDEX testtable.idx_testtable_col3;   BULK INSERT dbo.testtable FROM 'C:\testtable.dat' WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);   CREATE INDEX idx_testtable_col2 on testtable (col2 asc); CREATE INDEX idx_testtable_col3 on testtable (col3 asc);     --4. BULK load with tablock and drop/recreate indexes and change recovery model truncate table testtable   ALTER DATABASE SW_TEST SET RECOVERY SIMPLE;   DROP INDEX testtable.idx_testtable_col2; DROP INDEX testtable.idx_testtable_col3;   BULK INSERT dbo.testtable FROM 'C:\testtable.dat' WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);   CREATE INDEX idx_testtable_col2 on testtable (col2 asc); CREATE INDEX idx_testtable_col3 on testtable (col3 asc);   ALTER DATABASE SW_TEST SET RECOVERY FULL;

 

 

결과를 살펴보면 3번의 경우가 가장 빠르게 나타난 것을 확인 할 수 있다. 하지만 일부 실행 결과에서는 4번이 더 빠르게 나타나기도 하였다. 좀 더 정확한 정보를 얻기 위해서는 다양한 환경에서 많은 테스트를 통하여 평균치를 산정하여야 할 것이다.

 

BULK INSERT 사용시 일반적인 사용보다 환경에 따라 다양한 옵션을 사용하여 대량으로 로드하는 것이 많은 시간을 단축 할 수 있는 것을 확인 할 수 있다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3434/options-to-improve-sql-server-bulk-load-performance/

 

 

2015-01-14 / 강성욱 / http://sqlmvp.kr

 

SQL Server, BCP, BULK INSERT, 대량 로드, 벌크인서트, 대량 삽입, 대용량 데이터 처리, TABLOCK, BULK LOAD, DBA, mssql, 데이터베이스