在工作上如遇到大量匯入資料的需求,我常會建議客戶使用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)處理
參考
评论