728x90
SQLLeader.com / 한대성 (dshan@adconsulting.co.kr)

 

 

SQL Server 2005의 Bulk Insert 성능 비교를 다음과 같이 수행해 보았습니다.

임의로 10,000,000건의 텍스트 데이터를 만든 후, 각각의 방법을 이용하여 MSSQL Server 2005의 테이블에 넣는 방식입니다.

 

 

1. Source Text File 생성 스크립트 (VB.net Script : SSIS 내의 스크립트 작업을 이용하여 실행)

Imports System

Imports System.Data

Imports System.Math

Imports System.IO

Imports Microsoft.SqlServer.Dts.Runtime

 

Public Class ScriptMain

                  Public Sub Main()                     '

                   Dim str As String

                   Dim StrWriter As StreamWriter

                   Dim i As Integer

                   StrWriter = File.CreateText("D:\test.txt")

                   For i = 1 To 10000000

                    str = i.ToString + "|" + Convert.ToString(i * 2) + "|" + Convert.ToString(i * 3) + "|" + (Convert.ToInt32(i / 2)).ToString

                    StrWriter.WriteLine(str)

                   Next

                   StrWriter.Close()

 

                   Dts.TaskResult = Dts.Results.Success

                  End Sub

End Class

 

 

2. Text File Sample (d:\text.txt   : 10,000,000행, 321 MB)

1|2|3|0

2|4|6|1

3|6|9|2

4|8|12|2

5|10|15|2

6|12|18|3

7|14|21|4

8|16|24|4

9|18|27|4

10|20|30|5

… … … …

9999995|19999990|29999985|4999998

9999996|19999992|29999988|4999998

9999997|19999994|29999991|4999998

9999998|19999996|29999994|4999999

9999999|19999998|29999997|5000000

10000000|20000000|30000000|5000000

 

3. Format File (d:\testImport-f-n.Fmt)

9.0

4

1       SQLBIGINT        0       8       "|"   1     c1       ""

2       SQLBIGINT        0       8       "|"   2     c2       ""

3       SQLBIGINT        0       8       "|"   3     c3       ""

4       SQLBIGINT        0       8       "\r\n"   4     c4       ""

 

 

4. 성능 비교

             1) OPENROWSET 이용

                - 수행 스크립트

CREATE TABLE testOpenRowset(c1 bigint, c2 bigint, c3 bigint, c4 bigint)

GO

DBCC DROPCLEANBUFFERS

DECLARE @start DATETIME

SET @start = getdate()

INSERT INTO testOpenRowset(c1, c2, c3, c4)

SELECT    t1.c1, t1.c2, t1.c3, t1.c4

FROM    OPENROWSET( BULK 'd:\test.txt',

        FORMATFILE = 'd:\testImport-f-n.Fmt') AS t1(c1, c2, c3, c4);

SELECT  getdate() - @start as ElapsedTime

DROP TABLE testOpenRowset

GO

 

                - 테스트 결과    (반복해서 12회 수행 후, 최대치 및 최소치를 제외한 10개의 값으로 평균을 계산)

순번

걸린 시간

데이터 유효성

1

00:01:50.203

 

2

00:01:55.187

 

3

00:01:52.377

 

4

00:01:58.030

 

5

00:01:52.237

 

6

00:01:58.937

 

7

00:02:08.953

최대치 - 제외

8

00:01:46.610

 

9

00:02:00.343

 

10

00:01:49.203

 

11

00:01:58.203

 

12

00:01:45.673

최소치 - 제외

평균

00:01:54.133

 

 

 

2) BULK INSERT 이용

                - 수행 스크립트

CREATE TABLE testBulkInsert(c1 bigint, c2 bigint, c3 bigint, c4 bigint)

GO

DBCC DROPCLEANBUFFERS

DECLARE @start DATETIME

SET @start = getdate()

BULK INSERT testBulkInsert

   FROM 'd:\test.txt'

   WITH (FORMATFILE='d:\testImport-f-n.Fmt')

SELECT  getdate() - @start as ElapsedTime

DROP TABLE testBulkInsert

GO

 

                - 테스트 결과    (반복해서 12회 수행 후, 최대치 및 최소치를 제외한 10개의 값으로 평균을 계산)

순번

걸린 시간

데이터 유효성

1

00:01:49.627

 

2

00:01:46.983

 

3

00:01:47.843

 

4

00:01:47.077

 

5

00:01:54.343

최대치 - 제외

6

00:01:45.967

 

7

00:01:49.500

 

8

00:01:48.457

 

9

00:01:44.953

 

10

00:01:52.687

 

11

00:01:45.737

 

12

00:01:44.153

최소치 - 제외

평균

00:01:47.883

 

 

 

3) BCP 이용

                - 수행 스크립트

CREATE TABLE testBCP(c1 bigint, c2 bigint, c3 bigint, c4 bigint)

GO

DBCC DROPCLEANBUFFERS

DECLARE @start datetime

SET @start = getdate()

EXEC master..xp_cmdshell 'bcp test.dbo.testBCP in d:\test.txt -T -b1000000 -fd:\testImport-f-n.Fmt'

SELECT  getdate() - @start as ElapsedTime

DROP TABLE testBCP

GO

 

                - 테스트 결과    (반복해서 12회 수행 후, 최대치 및 최소치를 제외한 10개의 값으로 평균을 계산)

순번

걸린 시간

데이터 유효성

1

00:02:10.530

최대치 - 제외

2

00:01:56.143

 

3

00:01:54.253

 

4

00:01:57.893

 

5

00:01:57.343

 

6

00:01:55.597

 

7

00:01:57.483

 

8

00:01:57.140

 

9

00:01:57.357

 

10

00:01:53.610

 

11

00:01:52.280

최소치 - 제외

12

00:01:58.437

 

평균

00:01:56.525

 

 

 

4) SSIS 이용 - FastParse = off (기본값)

- 수행 패키지

제어 흐름

데이터 흐름

 

                - 테스트 결과    (반복해서 12회 수행 후, 최대치 및 최소치를 제외한 10개의 값으로 평균을 계산)

순번

걸린 시간

데이터 유효성

1

00:01:05.938

최대치 - 제외

2

00:01:04.453

 

3

00:01:02.812

 

4

00:01:02.703

최소치 - 제외

5

00:01:04.672

 

6

00:01:03.328

 

7

00:01:04.359

 

8

00:01:02.922

 

9

00:01:03.234

 

10

00:01:04.219

 

11

00:01:05.891

 

12

00:01:04.562

 

평균

00:01:04.045

 

 

 

5) SSIS 이용 - FastParse = on        

                - 수행 패키지

제어 흐름

데이터 흐름

 

                - 테스트 결과    (반복해서 12회 수행 후, 최대치 및 최소치를 제외한 10개의 값으로 평균을 계산)

순번

걸린 시간

데이터 유효성

1

00:00:58.187

 

2

00:00:56.860

 

3

00:00:56.812

최소치 - 제외

4

00:00:57.141

 

5

00:00:57.344

 

6

00:00:56.907

 

7

00:00:56.453

 

8

00:00:58.953

최대치 - 제외

9

00:00:56.734

 

10

00:00:57.562

 

11

00:00:56.891

 

12

00:00:57.547

 

평균

00:00:57.163

 

 

 

5. 성능 비교 요약

데이터 처리 방식

걸린 시간

순위

OPENROWSET

00:01:54.133

4

BULK INSERT

00:01:47.883

3

BCP

00:01:56.525

5

SSIS ? FastParse=off

00:01:04.045

2

SSIS ? FastParse=on

00:00:57.163

1

 

            

             SSIS를 이용하는 방식이 BCP, OPENROWSET, BULK INSERT 등에 비해 약 48%정도 빠른 수행 결과를 나타냄. 

테스트 수행 참고 사이트 : http://weblogs.sqlteam.com/mladenp/articles/10631.aspx

[출처] SQL Server 2005의 Bulk Insert 성능 비교 |작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

table 내의 모든 제약조건, index 이름, 해당에 index가 걸린 column 조회  (0) 2008.04.28
MSSQL 에서 XML 데이터 입력, 출력, 수정하는 방법  (0) 2008.04.28
mssql txt or csv 파일 데이터 Insert  (0) 2008.04.28
MSSQL CSV Bulk Insert  (0) 2008.04.28
MSSQL2005에서 2G이상 메모리 사용하기  (0) 2008.04.28
728x90

txt 파일이나 csv 파일을 INSERT 하기 위해서는 다음과 같이 세 가지 유형의 방법이 있습니다.


1. BCP.exe 유틸리티를 사용한 방법


BCP.exe 유틸리티를 활용하면 csv나 txt 파일로 된 자료를 테이블에 일괄로 INSERT 하고 있습니다.

(자세한 내용은 온라인 설명서의 BCP 유틸리티 부분을 참조하십시오)


예제)

bcp tempdb.dbo.temp in c:\temp.txt -c -t;


사용법: bcp {dbtable | query} {in | out | queryout | format} 데이터 파일
  [-m 최대 오류 수]            [-f 서식 파일]          [-e 오류 파일]
  [-F 첫 행]             [-L 마지막 행]             [-b 일괄 처리 크기]
  [-n 원시 유형]          [-c 문자 유형]      [-w 와이드 문자 유형]
  [-N 텍스트가 아닌 원시 형식 유지] [-V 파일 서식 버전] [-q 따옴표 붙은 식별자]
  [-C 코드 페이지 지정자]  [-t 필드 종료 문자]    [-r 행 종료 문자]
  [-i 입력 파일]            [-o 출력 파일]             [-a 패킷 크기]
  [-S 서버 이름]          [-U 사용자 이름]            [-P 암호]
  [-T 트러스트된 연결]   [-v 버전]             [-R 국가별 설정 사용]
  [-k null 값 유지]     [-E ID 값 유지]
  [-h "참고 로드"]


2. BULK INSERT를 이용한 방법


예제)

BULK INSERT tempdb.dbo.temp

   FROM 'c:\temp.txt'
   WITH
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )

사용법)

BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM 'data_file' }
    [ WITH
        (              [ BATCHSIZE [ = batch_size ] ]
             [ [ , ] CHECK_CONSTRAINTS ]
             [ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]
             [ [ , ] DATAFILETYPE [ =
                 { 'char' | 'native'| 'widechar' | 'widenative' } ] ]
             [ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]
             [ [ , ] FIRSTROW [ = first_row ] ]
             [ [ , ] FIRE_TRIGGERS ]
             [ [ , ] FORMATFILE = 'format_file_path' ]
             [ [ , ] KEEPIDENTITY ]
             [ [ , ] KEEPNULLS ]
             [ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]
             [ [ , ] LASTROW [ = last_row ] ]
            [ [ , ] MAXERRORS [ = max_errors ] ]
             [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
             [ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]             [ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]
             [ [ , ] TABLOCK ]
        )
    ]


3. DTS(Data Transformation Services) 서비스를 이용하는 방법


데이터 원본 중에서 텍스트 파일 또는 csv로 지정하여 데이터를 가져오는 방법



이러한 방법을 사용할 수 있습니다.

감사합니다.


-펌-

[출처] mssql txt or csv 파일 데이터 Insert|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

MSSQL 에서 XML 데이터 입력, 출력, 수정하는 방법  (0) 2008.04.28
SQL Server 2005의 Bulk Insert 성능 비교  (0) 2008.04.28
MSSQL CSV Bulk Insert  (0) 2008.04.28
MSSQL2005에서 2G이상 메모리 사용하기  (0) 2008.04.28
Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환  (0) 2008.04.28

+ Recent posts

티스토리툴바