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
728x90

1. Data Fragmentation 정리 작업 이유 및 효과: Index Rebuild를 하게 되면 분산된 데이터를 모을 수 있습니다. 따라서, 적은 수의 IO로 동일한 데이터를 가져오게 됨에 따라 성능이 개선이 됩니다.

방법: 각 데이터베이스내에서 테이블 혹은 개별적인 Index에 대하여 수행할 수 있으며 Index Rebuid란 Index를 생성하는 작업이므로 데이터 량에 따라 수행시간이 오래 걸릴수 있습니다. 또한 Index의 종류에 따라서 Select만 허용하거나 모든 요청을 거부할 수 있으므로 사용자가 Access하지 않는 시간에 수행을 하시기 바랍니다.

ex 1. 테이블에 있는 모든 Index를 Rebuild하고자 하는 경우

DBCC DBREINDEX(table 명)

ex 2. 테이블에 있는 특정 Index를 Rebuild 하고자 하는 경우

DBCC DBREINDEX(table 명, index 명)

수행 주기: DBCC Showcontig를 수행하였을 때 Scan Density가 100%로 표시되면 데이터가 연속적으로 저장되었다는 의미이므로 이 수치가 낮을 경우 성능이 저하가 됩니다. 따라서, 이 문장을 수시로 수행하여서 해당 값이 낮을 경우 수행합니다.


2. Data Consistency Check 작업 이유 및 효과: SQL Server가 자체적으로 데이터 관리를 하기는 가끔 데이터의 일관성에 문제가 발생할 수 있습니다. 이러한 문제가 발생하면 성능이 저하되거나 timeout 오류가 발생하게 되므로 정기적으로 일관성 체크를 해야 됩니다.DBCC (Database Consistency Checker)를 사용하여, Database와 Object의 integrity를 monitoring 하여 오류를 점검합니다. DBCC를 수행하지 않고 DB backup을 받게 되면, 문제가 발생한 (예: table corrupt, …) 데이터를 그대로 backup 받을 수 있는 위험성을 내포하고 있고, 제대로 backup이 되지 않아서 문제 발생시에 복구가 불가능해 질 수 있습니다.

방법: 각 데이터베이스에 대해 수행하며, 데이터베이스 전체 혹은 테이블 단위로 수행할 수있습니다.

ex 1. 임의의 데이터베이스 전체에 대하여 수행하고자 하는 경우

DBCC CHECKDB(db 명)

ex2. 임의의 데이터베이스 내의 한 테이블에 대하여 수행하고자 하는 경우

DBCC CHECKTABLE( table 명)

위 수행 결과를 반드시 확인하시어 오류가 발생할 경우 오류 메시지에 지시된 대로 작업을 하시기 바랍니다.

또는 마이크로소프트 고객기술지원부로 연락 주시기 바랍니다.

수행 주기: 위 DBCC 작업은 최소한 주 1회 수행을 해주시기 바랍니다


3. SQL Server Errorlog 내용 확인

- 매일 errorlog file의 내용을 확인하여, 문제를 조기에 발견하고 조치를 취해야 합니다.

- Errorlog file 위치 : Microsoft SQL Server 가 설치되어 있는 위치가 C:\MSSQL 이라고 가정하면 C:\MSSQL\LOG 의 ERRORLOG 라는 이름의 파일이 존재하게 됩니다. - ERRORLOG file 은 마지막으로 SQL Server가 start된 이후부터 지금까지의 errorlog내용을 가지고 있으며, 이외에 ERRORLOG.1, ERRORLOG.2, ... 등의 파일이 존재하는데, 이 파일들은 그 이전의 errorlog file들을 history관리를 위해 backup해 두는 파일입니다. - 문제 발생시에 문제발생시점 및 원인을 정확하게 판단하는데 있어서, 이전의 errorlog file이 필요한 경우도 있으므로 errorlog.* 파일들도 가능한 한 삭제하지 말고 유지하시기 바랍니다.

- Errorlog file에는 심각한 error, SQL Server관리와 관련되는 작업에 대한 내용 등이 기록되므로, 가능한 한 자주 Monitoring해야 하며 일반적인 Database 문제 발생시에도 일차적으로 errorlog 를 확인하는 것이 도움이 됩니다.

- ERRORLOG file에는 Error에 대한 Error Number, Severity Level, Error Message 등이 기록됩니다


4. 계획적인 Database Full Backup 및 Transaction Backup

DB 에 문제가 발생하는 경우에 복구가 가능하도록, backup을 받아야 합니다.

- 주기적으로 아래의 데이터베이스들을 backup해야 합니다.

Master database, Msdb database, 모든 user database들, Distribution database

- Database backup 전략을 수립하여, scripts를 작성하고, SQL Server 7.0 에서 제공되는 "Scheduled Tasks" 기능을 이용하여 주기적으로 수행되도록 작업할 수 있습니다.

- Backup 방법은 아래 두가지로 나누어 집니다.

1) Database 전체를 backup 하는 작업 (Full Backup)

Data 와 Transaction log를 모두 backup 받습니다.

이 방법은 Transaction log를 삭제하지는 않기 때문에, 추가적으로 Database backup 하기 전에 Transaction log를 삭제하는 작업을 수행시켜 주는 것이 필요.

2) Transaction log 만 backup 하는 작업 (Transaction Log Backup)

Transaction log를 backup받고 삭제해 줍니다.

- 데이터베이스를 사용자들이 사용하는 동안에도 backup이 가능합니다.

Backup을 수행하면 SQL Server가 다소 느려질 수 있으므로, SQL Server를 많이 사용하지 않을 때 작업하시는 것이 좋습니다.

- Backup 에 대한 책임은 기본적으로 SA 와 Database Owner 에게 부여되며, Database Owner가 다른 사용자(user)에게 권한을 부여할 수도 있다.

- <주의> 다른 Character set과 Sort order를 사용하는 SQL Server에서 backup(dump database) 받은 데이터베이스는 Restore (Load Database)할 수 없습니다.

- 정기적으로 데이터베이스전체를 backup 받습니다.

backup database <DB명> to <backup_DEVICE>

<backup_DEVICE> 는 Disk, Tape 모두 가능합니다.

- Transaction Log full이 발생하지 않도록 짧은 기간 단위로 Log를 backup 받습니다.

(매우 중요합니다. 소홀히 하면 Log가 가득 차게 되어 그 데이터베이스에 대해서는 데이터에 대한변경작업을 할 수가 없게 됩니다.)

backup transaction <DB명> to <DUMP_DEVICE>

- DB 의 Log 가 full 된 경우에는, 다음과 같이 Log 를 비워 줍니다.

backup transaction <DB명> with truncate_only

위 작업 수행후에도 Log 가 회수되지 않으면, 아래와 같이 수행해 줍니다.

backup transaction <DB명> with no_log

- Master DB 및 주요 System Table backup

- master database 도 주기적으로 backup (dump database)을 받아야 하며, 특히, master database 에 변경이 발생하는 작업 (create database, sp_addlogin, sp_addserver, ...)을 수행한 후에는 즉시 Backup을 받아야 합니다.

<예> backup database master to disk='C:\MSSQL\BACKUP\master_0820.bak'


6. 정기적인 Bulk-copy로 SAM File 확보

- 모든 DB, Obj- master database의 주요 system table들에 대해서는 주기적으로 bcp out을 받습니다. (sysdatabases, sysdevices, sysusages table은 데이터베이스들에 대한 정보를 관리하므로 특히 주의해서 변동이 발생할 때마다 bcp out 을 받아 두시는 것이 좋습니다.) 명령프롬프트에서 bcp Utility를 사용하여 파일로 받으시면 됩니다.

Bcp master..<Table명> out <Output_File> -Usa -P<sa_password> -S<Sqlserver명> -c

<예> C:\MSSQL\BACKUP> mkdir bcpout C:\MSSQL\BACKUP> cd bcpout C:\MSSQL\BACKUP\bcpout> bcp master..sysdatabases out sysdatabases.csv -Usa -P -c

C:\MSSQL\BACKUP\bcpout> bcp master..sysusages out sysusages.csv -Usa -P -c C:\MSSQL\BACKUP\bcpout> bcp master..sysdevices out sysdevices.csv -Usa -P -c - 이 작업을 batch file (*.bat) 로 작성해서 필요할 때 그 batch file을 수행하면 편리합니다.

- 주요 데이터를 저장하고 있는 Table인 경우에는, bcp 를 사용하여 주기적으로 backup 을 받아 두는 것이 좋습니다.


7. 모든 DB, Objects에 대한 Scripts 유지, 관리

- 모든 데이터베이스구축과정에서 작성한 모든 Scripts들에 대하여 최신Version을 관리합니다.

- 주기적으로 DB관리자는 SQL Enterprise Manager 를 사용하여 Scripts 들을 파일로 받아서 관리합니다.

- SQL Enterprise Manager 에서 SQL Server 를 선택하고 Databases에서 데이터베이스를 선택한 다음 Object 메뉴를 선택하고 Generate SQL Scripts 를 선택하면 Scripts를 파일로 받을 수 있습니다.


8. DB free space 확인

- 주기적으로 데이터베이스별로 free space를 확인해서 여유공간이 부족한 경우에는, 미리 영역을 확보해줍니다.

- 데이터영역에 free space가 없는 경우 : 데이터베이스의 Data영역을 확장하거나 불필요한 데이터를 삭제하는 작업을 통해 free space를 확보합니다.

(SQL Enterprise Manager 또는 ALTER DATABASE command를 사용함.) Alter database <DB명> on <DEVICE명>=<Size>

- Log영역에 free space가 없는 경우 : Transaction Log 정보를 backup 받거나 Transaction Log 를 삭제합니다.

backup tran <DB명> to <backup_DEVICE> 또는 backup tran <DB명> with truncate_only 또는 backup tran <DB명> with no_log go

[출처] MSSQL CSV Bulk Insert|작성자 필립박

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

SQL Server 2005의 Bulk Insert 성능 비교  (0) 2008.04.28
mssql txt or csv 파일 데이터 Insert  (0) 2008.04.28
MSSQL2005에서 2G이상 메모리 사용하기  (0) 2008.04.28
Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환  (0) 2008.04.28
MSSQL2005 사용자 계정추가 방법  (0) 2008.04.28

+ Recent posts

티스토리툴바