BULK INSERT Performance

一直以為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了一下,發現幾件事:

  1. 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
  2. 由於誤認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
)

歡迎推文分享:
Published 12 January 2008 01:57 AM 由 Jeffrey
Filed under: , ,
Views: 30,747



意見

# LSK said on 24 May, 2011 03:49 AM

黑大問一下,如果遇到欄位有自動編號的怎麼辦?

用FORMAT FILE一直過不了,你有沒有這方面的經驗?

# Jeffrey said on 24 May, 2011 05:32 AM

to LSK, 過去倒沒有處理過自動編號欄位的BULK INSERT,但我想可用MSDN文件(msdn.microsoft.com/.../ms188365.aspx) 所提的KEEPIDENTITY來處理。

# TACO said on 02 July, 2013 09:55 AM

請教前輩

可否透過bcp或bulk insert來做兩個SQl server的大量資料交換呢?

感謝

# Jeffrey said on 02 July, 2013 08:57 PM

to TACO, 先用bcp將SQL A的資料匯出成文字檔,再用bcp將其匯入到SQL B,應可達到目的。不過,如果兩台SQL有網路相通,可以用SSIS直接複製資料會更方便。

# taco said on 04 July, 2013 06:47 AM

可否請教前輩:

透過下列語法

EXEC master..xp_cmdshell bcp '[eip].[customer]' out c:\DT.txt -c -LGH\SQLEXPRESS -sa -915

為什麼會出現下列訊息呢?

訊息 102,層級 15,狀態 1,行 1

接近 '[eip].[customer]' 之處的語法不正確。

感謝

# taco said on 04 July, 2013 06:49 AM

請教前輩

透過下列語法

EXEC master..xp_cmdshell bcp '[eip].[customer]' out c:\DT.txt -c -LGH\SQLEXPRESS -sa -135

但為什麼出現下列錯誤訊息呢?

訊息 102,層級 15,狀態 1,行 1

接近 '[eip].[customer]' 之處的語法不正確。

感謝

# Jeffrey said on 04 July, 2013 10:06 PM

to taco, 感覺語法有誤,可以參考看看其他人的寫法: http://bit.ly/1cX1cry http://bit.ly/1cX1leH

# JIMMY said on 19 April, 2018 02:42 AM

請教黑暗大大

設BATCHSIZE 會降低BULK INSERT的速度嗎?

# Jeffrey said on 19 April, 2018 08:37 AM

to JIMMY, BatchSize縮小,Commit批次變多會增加網路往返,但我個人認為不致形成明顯的差距。倒是要留意分批Commit可能產生匯入前半部分的情況是否會可被接受,我自己的習慣會先Bulk Insert進暫存資料表,確定完整匯入後再做下一步。

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<January 2008>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
272829303112
3456789
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication