SqlBulkCopy批量插入資料

利用SqlBulkCopy快速大批量導入資料

protected

void

Button1_Click(

object

sender, EventArgs e)

{ 

DateTime beginTime = DateTime.Now;

Response.Write(

"開始時間："

+ beginTime.ToString(

"yyyy年MM月dd日：HH:mm:ss:fff"

)); 

//構造一個Datatable存儲将要批量導入的資料

DataTable dt =

new

DataTable();

dt.Columns.Add(

"id"

,

typeof

(

string

));

dt.Columns.Add(

"name"

,

typeof

(

string

)); 

// 見識下SqlBulkCopy強悍之處，來個十萬條數資料試驗

int

i;

for

(i = 0; i < 100000; i++)

{

DataRow dr = dt.NewRow();

dr[

"name"

] = i.ToString();

dt.Rows.Add(dr);

} 

string

str = ConfigurationManager.ConnectionStrings[

"connStr"

].ConnectionString.ToString();

//聲明資料庫連接配接

SqlConnection conn =

new

SqlConnection(str); 

conn.Open();

//聲明SqlBulkCopy ,using釋放非托管資源

using

(SqlBulkCopy sqlBC =

new

SqlBulkCopy(conn))

{

//一次批量的插入的資料量

sqlBC.BatchSize = 1000;

//逾時之前操作完成所允許的秒數，如果逾時則事務不會送出 ，資料将復原，所有已複制的行都會從目标表中移除

sqlBC.BulkCopyTimeout = 60; 

//設定 NotifyAfter 屬性，以便在每插入10000 條資料時，呼叫相應事件。  

sqlBC.NotifyAfter = 10000;

sqlBC.SqlRowsCopied +=

new

SqlRowsCopiedEventHandler(OnSqlRowsCopied); 

//設定要批量寫入的表

sqlBC.DestinationTableName =

"dbo.text"

; 

//自定義的datatable和資料庫的字段進行對應

sqlBC.ColumnMappings.Add(

"id"

,

"tel"

);

sqlBC.ColumnMappings.Add(

"name"

,

"neirong"

); 

//批量寫入

sqlBC.WriteToServer(dt);

}

conn.Dispose();

Response.Write(

"<br/>"

); 

DateTime endTime = DateTime.Now;

Response.Write(

"結束時間："

+ endTime.ToString(

"yyyy年MM月dd日：HH:mm:ss:fff"

));

TimeSpan useTime = endTime-beginTime;

//使用時間

Response.Write(

"<br/>插入時間："

+ useTime.TotalSeconds.ToString()+

"秒"

); 

}

//響應時事件

void

OnSqlRowsCopied(

object

sender, SqlRowsCopiedEventArgs e)

{

Response.Write(

"<br/> OK! "

);

}

----------------------------------------------------------------

在做大批量資料插入的時候，如果用Insert into ... values (...)這種方式的話效率極低，這裡介紹兩種性能比較好的批量插入方法。

1. 使用SqlBulkCopy

private static long SqlBulkCopyInsert() 

{ 

Stopwatch stopwatch = new Stopwatch(); 

stopwatch.Start(); 

DataTable dataTable = GetTableSchema(); 

string passportKey; 

for (int i = 0; i < count; i++) 

{ 

passportKey = Guid.NewGuid().ToString(); 

DataRow dataRow = dataTable.NewRow(); 

dataRow[0] = passportKey; 

dataTable.Rows.Add(dataRow); 

} 

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString); 

sqlBulkCopy.DestinationTableName = "Passport"; 

sqlBulkCopy.BatchSize = dataTable.Rows.Count; 

SqlConnection sqlConnection = new SqlConnection(connectionString); 

sqlConnection.Open(); 

if (dataTable!=null && dataTable.Rows.Count!=0) 

{ 

sqlBulkCopy.WriteToServer(dataTable); 

} 

sqlBulkCopy.Close(); 

sqlConnection.Close(); 

stopwatch.Stop(); 

return stopwatch.ElapsedMilliseconds; 

}  

　　使用SqlBulkCopy類進行資料插入其原理是采用了SQL Server的BCP協定進行資料的批量複制。這裡我們先要建好一個DataTable（最好是通過DataAdapter來灌資料得到，因為這樣出來的DataTable就已經有跟資料表相同的列定義，可以免去之後Mapping Column的步驟），把要插入的資料加進這個DataTable中，然後用SqlBulkCopy的執行個體來插入到資料庫中。經過測試，SqlBulkCopy方法比直接用Sql語句插入資料的效率高出将近25倍。

　　另外批量導入SQL、MYSQL等資料是同樣的for循環，使用拼出來的sql或者使用參數的方式傳遞或者使用事務等不同方式的傳遞效率都不同。如果不使用SqlBulkCopy的方式的話，我測試下來做快遞是用一次事務來操作為最快。因為10000次的循環如果是每次送出，那麼都有連結和停止資料庫的操作，或者說他包含了1000次的小事務處理。如果外面就一個事務的話效率肯定會高。

#1樓 絕代恭敬      2010-07-12 20:14

using(SqlBulkCopy sbc = new SqlBulkCopy(connString))

{

try

{

sbc.DestinationTableName =ObjectName;

sbc.WriteToServer(dt);

}

catch(Exception ex)

{

throw new DataAccessException("BulkInsert調用異常", ex);

}

}

-----------------------------------------------------

SqlBulkCopy使用心得

最近做的項目由于之前的設計人員懶省事，不按照範式來，将一張表的擴充資訊存到了一個“鍵-值”表中。如下圖：

對于主表中的每一條資訊，大約有60個“key”，也就是說主表中每插入1條記錄，子表中必須要插入60條。

通過預估我們确定主表中最終的資料量大約是20萬，也就是說，子表中會有20x60=1200萬條記錄。同樣類型的“主-子”表我們一共有4對，且不說這些表的查詢效率，單是每天一次的資料導入對于我們來說就是一項巨大的挑戰。

Technorati 标簽: SqlBulkCopy

在此我吐槽一下，本來一個十萬級的資料庫，就是讓這種垃圾“設計師”生生給搞成了個千萬級的。而且最初他提出的資料插入方案是将每一條資料都生成一條Insert語句，然後逐條調用ExecuteNoQuery執行，後果就是測試用的3000條主表記錄，共生成3000x60x4=72萬條資料，花費7小時執行完畢，性能30條/秒。後來他辭職了，換了個人，然後第二任也辭職了，第三任就是在下。

項目到我手裡之後，在我的堅持下重新進行了軟體結構設計，由于資料庫系統已經被另一個子系統使用，是以沒辦法更改了，隻好去尋找一種高效的插入方式。

最開始我使用多線程，開10個線程，使性能提升到300條/秒，測試用記錄花費大約40分鐘插入完畢，對于多60倍的正式資料來說，40小時執行完畢顯然不能滿足我們每天一次資料導入工作的要求。

通過Google大神，我找到了SqlBulkCopy。

經過測試，性能我很滿意，4000條/秒，那就先用它吧，下一階段的工作重點就是幹掉“鍵-值”表。

在使用中，我也碰到了一些“莫名其妙”的問題，在此記下，以備查詢。

1. SqlBulkCopy可以将一個DataTable對象插入到指定名稱的資料表中，但是，這個DataTable的架構必須和資料庫表一樣，尤其要注意一點，DataTable中列的順序必須和資料庫表一樣，而且不允許間隔。比如：資料庫表中有A、B、C三列，其中B列有預設值，這時用于插入的DataTable不能隻有A、C兩列，如果隻有這兩列，就會把DataTable裡C列的資料插入資料庫表B列裡，而把DBNull插入C列裡。如果是C列有預設值，DataTable可以隻有A、B兩列。
2. SqlBulkCopu的預設逾時時間是30秒，30x4000=1.2萬，無法滿足我一次性插入單張表的需要，改成3600秒。