SqlBulkCopy使用心得 （大量數(shù)據(jù)導(dǎo)入）

文章轉(zhuǎn)載原地址：http://m.survivalescaperooms.com/mobydick/archive/2011/08/28/2155983.html

最近做的項目由于之前的設(shè)計人員懶省事，不按照范式來，將一張表的擴展信息存到了一個“鍵-值”表中。如下圖：

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

通過預(yù)估我們確定主表中最終的數(shù)據(jù)量大約是20萬，也就是說，子表中會有20x60=1200萬條記錄。同樣類型的“主-子”表我們一共有4對，且不說這些表的查詢效率，單是每天一次的數(shù)據(jù)導(dǎo)入對于我們來說就是一項巨大的挑戰(zhàn)。

Technorati 標簽:SqlBulkCopy

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

項目到我手里之后，在我的堅持下重新進行了軟件結(jié)構(gòu)設(shè)計，由于數(shù)據(jù)庫系統(tǒng)已經(jīng)被另一個子系統(tǒng)使用，所以沒辦法更改了，只好去尋找一種高效的插入方式。

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

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

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

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

1. 1.SqlBulkCopy 可以將一個DataTable對象插入到指定名稱的數(shù)據(jù)表中，但是，這個DataTable的架構(gòu)必須和數(shù)據(jù)庫表一樣，尤其要注意一 點，DataTable中列的順序必須和數(shù)據(jù)庫表一樣，而且不允許間隔。比如：數(shù)據(jù)庫表中有A、B、C三列，其中B列有默認值，這時用于插入的 DataTable不能只有A、C兩列，如果只有這兩列，就會把DataTable里C列的數(shù)據(jù)插入數(shù)據(jù)庫表B列里，而把DBNull插入C列里。如果是 C列有默認值，DataTable可以只有A、B兩列。

• 2.SqlBulkCopu的默認超時時間是30秒，30x4000=1.2萬，無法滿足我一次性插入單張表的需要，改成3600秒。