데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

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

 

 

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

 

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/

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
2010 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2279
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3108
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2145
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2317
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3260
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4037
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2442
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3021
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3707
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1289
» 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5178
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1608
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1000
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1420
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1355
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3623
1994 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1659
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1584
1992 Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2569
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 2986





XE Login