BULK INSERT Performance
9 | 40,552 |
一直以為BULK INSERT就不會記Log,結果今天跌了一跤...
我下的語法如下(錯誤示範,小朋友不要學)
TRUNCATE TABLE RESD
BULK INSERT RESDE
FROM 'C:\DataProc\Output\RESD.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
RESD.txt約1.4G,結果BULK INSERT花了30分鐘還沒做完,Log檔就長到20G,把HD空間給吃光。急忙Cancel,但依DB的運作,此時SQL會將剛才未做完的INSERT Rollback回去,20G的Log! 等了40分鐘還等不到Cancel完成。
請教了DBA,DBA認為剛才有HD空間不足的變數,SQL Server 2005可能已經不正常,建議我重新啟動SQL試試。重新啟動SQL後,順道見識了SQL 2005的新功能,SQL還是會將剛才沒做完的Rollback繼續做完,此時正在Rollback的DB會顯示(in recovery)而暫時不能用,但其他的DB則Ready了,花了約兩分鐘,in recovery的DB也回到可用狀態。(SQL 2000需要等Rollback做完,整台Server才能用)
在苦等DB Rollback的過程裡,我Google了一下,發現幾件事:
- Nonlogged BULK INSERT只有在一些條件下才成立(我一直以為BULK INSERT==No Log):
- DB選項必須開啟SELECT INTO/BULK INSERT
- 不可設Index;如果有,開始BULK INSERT時,TABLE必須是空的
- 需加上TABLOCK提示
- 資料表沒有設定複寫(Replication)
http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm - 由於誤認BULK INSERT不會有Log Issue、加上將DB設成Simple Recovery Mode(Truncate Log At Checkpoint),我沒意會到讓2.5億筆資料變成一個Transaction是多可怕的事。因此才會爆出可怕的Log量,其實我只要透過BATCHSIZE=1000,讓BULK INSERT過程中每1000筆Commit一次,Log量就會小很多了。不過資料匯入後,不過2G,Index也只有一組,我還是無法理解為什麼可以產生10倍大小的Log?
綜合以上,BULK INSERT SQL可以改成:
BULK INSERT RESDE
FROM 'C:\DataProc\Output\RESD.txt'
WITH
(
BATCHSIZE = 1000,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
TABLOCK
)
Comments
# by LSK
黑大問一下,如果遇到欄位有自動編號的怎麼辦? 用FORMAT FILE一直過不了,你有沒有這方面的經驗?
# by Jeffrey
to LSK, 過去倒沒有處理過自動編號欄位的BULK INSERT,但我想可用MSDN文件(http://msdn.microsoft.com/zh-tw/library/ms188365.aspx) 所提的KEEPIDENTITY來處理。
# by TACO
請教前輩 可否透過bcp或bulk insert來做兩個SQl server的大量資料交換呢? 感謝
# by Jeffrey
to TACO, 先用bcp將SQL A的資料匯出成文字檔,再用bcp將其匯入到SQL B,應可達到目的。不過,如果兩台SQL有網路相通,可以用SSIS直接複製資料會更方便。
# by taco
可否請教前輩: 透過下列語法 EXEC master..xp_cmdshell bcp '[eip].[customer]' out c:\DT.txt -c -LGH\SQLEXPRESS -sa -915 為什麼會出現下列訊息呢? 訊息 102,層級 15,狀態 1,行 1 接近 '[eip].[customer]' 之處的語法不正確。 感謝
# by taco
請教前輩 透過下列語法 EXEC master..xp_cmdshell bcp '[eip].[customer]' out c:\DT.txt -c -LGH\SQLEXPRESS -sa -135 但為什麼出現下列錯誤訊息呢? 訊息 102,層級 15,狀態 1,行 1 接近 '[eip].[customer]' 之處的語法不正確。 感謝
# by Jeffrey
to taco, 感覺語法有誤,可以參考看看其他人的寫法: http://bit.ly/1cX1cry http://bit.ly/1cX1leH
# by JIMMY
請教黑暗大大 設BATCHSIZE 會降低BULK INSERT的速度嗎?
# by Jeffrey
to JIMMY, BatchSize縮小,Commit批次變多會增加網路往返,但我個人認為不致形成明顯的差距。倒是要留意分批Commit可能產生匯入前半部分的情況是否會可被接受,我自己的習慣會先Bulk Insert進暫存資料表,確定完整匯入後再做下一步。