正文

[SQL SERVER][Performance]bcp vs BulkInse2013-01-26 14:13:00

【评论】 【打印】 【字体: 】 本文链接:http://blog.pfan.cn/edwardguo/54103.html

分享到:

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

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

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

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

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

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


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

image


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

image


第二次:627ms

image


第三次:1263ms

image


開始匯入資料(command line)

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

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


先清除資料表(truncate table)


第一次:1422ms

image


第二次:1312ms

image


第三次:1453ms

image


結果:

image

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

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

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





參考

最佳化大量匯入效能

使用資料表層級鎖定平行匯入資料

BULK INSERT (Transact-SQL)

使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料

控制大量匯入的鎖定行為

最佳化大量匯入效能

bcp 公用程式

阅读(2098) | 评论(2)


版权声明:编程爱好者网站为此博客服务提供商,如本文牵涉到版权问题,编程爱好者网站不承担相关责任,如有版权问题请直接与本文作者联系解决。谢谢!

评论

loading...
您需要登录后才能评论,请 登录 或者 注册