[SQL SERVER][Performance]bcp vs BulkInsert(tsql)

2011-06-10

在工作上如遇到大量匯入資料的需求,我常會建議客戶使用SSIS來處理,

但有些客戶偏偏就不喜歡使用SSIS(應該是懶的去熟悉新東西...XD),

anyway~SQL Server中,有bcp、BulkInsert 和OPENROWSET(BULK...) Statement這三種方法都支援平行匯入資料,

但人多時,往往就會有不同的聲音出現,一票人認為bcp比BulkInsert還快,

另一票人則認為BulkInsert比bcp快,我為了在command line和 tsql兩者之間取一,

便做了兩者匯入效能測試比較,結果!出乎我意料……

我先使用bcp匯出15萬筆資料,等等使用該資料來測試匯入效能(檔案編碼建議使用UTF-8)

bcp "select top 150000 ';'+case_chop from table"

queryout d:\srcdata.txt -c -U<loginid> -P<password> -S<server\instance>

建立目地資料表

create table mydata

serial int identity(1,1),

name varchar(50)

開始匯入資料(tsql)

declare @starttime time(7),

@endtime time(7),

@elaspedtime numeric(20,7);

set @starttime=getdate();

BULK INSERT mydata

FROM 'D:\srcdata.txt'

WITH

    batchsize=1000,

    FIELDTERMINATOR = ';',

    FIRSTROW =1,

    CODEPAGE='RAW',

    KEEPNULLS,

    TABLOCK

) ;

set @endtime=getdate();

SET @elaspedtime = convert(integer, datediff(ms, @starttime, @endtime));

print @starttime

print @endtime

print @elaspedtime

第一次:1013ms

第二次:627ms

第三次:1263ms

開始匯入資料(command line)

bcp db.schema.table in "d:\srcdata.txt" -c -t";" -b1000 -U<loginid> -P<password> -S<server\instance>

(指定分隔符號和批次筆數)

先清除資料表(truncate table)

第一次:1422ms

第二次:1312ms

第三次:1453ms

結果:

可以看到使用TSQL執行大量匯入作業比Command Line快了約428ms,

在我還沒做這個測試前,我一直以為Command Line會比較快,

看來事實證明,如需要匯入大量資料時,請優先考慮使用TSQL(BulkInsert)處理

RiCo技術農場