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秒。