在工作上如遇到大量匯入資料的需求,我常會建議客戶使用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)處理 參考 最佳化大量匯入效能 使用資料表層級鎖定平行匯入資料 BULK INSERT (Transact-SQL) 使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料 控制大量匯入的鎖定行為 最佳化大量匯入效能 bcp 公用程式

评论