topcat 姍舞之間的極度凝聚

緣起：

為了讓系統可以很容易的參考氣象的相關資料，所以小喵預計要寫一個程式，每日定時的抓取Open Data，並將資料轉入資料庫，好讓其他的資料如果要參考天氣的狀況，可以有個簡單的方式可以關聯查詢。連到中央氣象局，抓取OpenData轉成物件，這個部分還蠻單純，也沒啥問題，只是資料稍微偏大，一次大約做個5分鐘內，資料量大約是7~8萬筆，就可以轉換完成，但，接著要放入資料庫時，就開始撞牆踩雷了~

雷雷相連

雷一：資料量大

其實這也還好，一次組合好 7~8萬筆的 Insert 的語法，再一次ExecuteNoneQuery，這樣應該就還好吧~但是這個再搭配第二個雷，就是噩夢的開始~

雷二：資料有重複

資料量大，再遇到資料重複，那麼，我要

1. 產生SQL與法之前，就先用LINQ在物件放入集合前，判斷是否有重複，將重複性的資料跳掉
2. 一筆一筆新增，然後在新增前先下SQL與法查詢是否有資料

方式1.其實是不好的，一開始還好，但隨著筆數不斷的增加，他其實是跑黑圈來逐筆比對因此到後來越來越慢→失敗
方式2.也沒快到哪裡去，迴圈中不斷的Select...，Insert....資料庫來來回回多趟

於是小喵心想，那麼，如果我用另外一個暫時的資料表(Tmp)，把Key拿掉，先讓資料不必管Key來將資料放入，接著，才透過 Insert ...Select ... Group By，將重複性的資料跳掉，放入真正想要存放的資料表，這樣，我只要迴圈產生8萬條Insert語法，再一次執行SQL，這樣應該會好些吧

(PS.由於小喵最近電腦壞掉，目前用一台舊的電腦，速度慢的狀況更是顯而易見~)

於是，小喵這樣做後，執行的時間，從原來的約80分鐘，減少到50分鐘左右，但，這樣依舊太久，無論哪個方式，至少一次的8萬筆回圈避不掉，所以小喵又開始再思考，還有什麼方式是可以一次大量匯入資料的?

Bulk Insert for MS SQL

Bulk Insert可以大量快速的轉入文字型的資料，於是小喵尋找相關的說明文件，找到了這一篇

https://msdn.microsoft.com/zh-tw/library/ms191184(v=sql.120).aspx

小喵思考，目前已經組出來的是物件集合List (Of XXXX)，如果可以將物件集合轉為XML檔案，並且存起來那麼應該可以加快速度吧~

有了方向，就開始動手，首先，先將物件轉為XML(請參考：[筆記] Object 物件(集合) XML 互轉 公用程式)並存在檔案系統中，這部分還好，很快的，已經處理好這段。

接著，研究如何用Bulk Insert 來將資料轉移到資料庫，細看了一下目前找到的文章所提供的範例：

USE AdventureWorks2012;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO

發現，問題在【C:\xxxxx】，如果要用SQL語法執行這一段，那麼，就必須把xml檔案，產生或複製到SQL Server所在的主機上，才能這樣玩。但，這樣似乎不太好。

於是，小喵又找了找是否有其他方式，可以透過元件來執行 Bulk Insert，小喵找到了以下這篇：

如何使用 XML 大量載入元件將 XML 匯入 SQL Server

這篇是使用VBScript來運作，使用的是【SQLXMLBulkLoad.SQLXMLBulkLoad】這樣的一個元件，因此，如果可以找到此元件，加入參考，那樣大約就可以搞定在App Server上，直接運作存檔、匯入，不必把xml檔案搞到SQL Server上執行。

接著，就來看這部分如何處理~

透過元件執行 Bulk Insert 處理 xml 檔案

首先，元件【SQLXMLBulkLoad.SQLXMLBulkLoad】必須安裝【SqlXml 4.0】，可以到以下的網址進行下載安裝：

SqlXml 4.0 Service Pack 1 (SP1)

加入參考

VBScript使用的方式是屬於Late Binding的方式，在Visual Studio中，小喵撰寫WCF，當然希望可以用Early Binding的方式，所以，首先要將元件加入參考，請選擇【COM】 元件，找到【Microsoft XML Bulkload for SQL Server 4.0 Type Library】

Imports NameSpace

接著，要Imports(VB.NET) / using (C#) 命名空間【SQLXMLBULKLOADLib】

Connection String

使用SqlXml有個地方要特別的注意，他的Connection String與我們熟知的SqlClient方式不同，他主要是用OLEDB的方式，建置的方法，可以參考以下的步驟：

1. 開啟檔案總管，在硬碟的任意位置(例如：D:\Temp)→右鍵→新增→文字文件
2. 檔名任意，附檔名改為【UDL】
3. 雙擊剛新增的檔案
4. 點選【提供者】→選擇【Microsoft OLE DB Provider for SQL Server】→下一步
5. 分別輸入【伺服器名稱】、【登錄資訊】、【資料庫】→之後按下【測試連線】，如果沒問題，按下【確定】
6. 用【記事本】開啟剛剛的UDL檔
7. 最後一行，即為OLEBD的Connection String設定

Code

相關程式碼的部分，還算單純，大致如下：

Dim objBL As New SQLXMLBulkLoad4
Try
	Dim FilePath As String = "D:\Tmp"
	Dim XmlFileName As String = "Weather20160503.xml"
	Dim ConnStr2 As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NorthWind;Data Source=.\SQLExpress"
	objBL.ConnectionString = ConnStr2
	objBL.ErrorLogFile = FilePath & "\error.log"
	objBL.Execute(FilePath & "\WeatherMapping.xml", FilePath & "\" & XmlFileName)

Catch ex As Exception
	Throw New Exception(ex.Message)
Finally
	objBL = Nothing

End Try

這裡面，需要有另外一個檔案(xml)，用來定義XML檔與資料表的Table欄位的對照，這邊就不細講，詳細可以參考VBScript那一篇

結果

實際執行結果，效果十分驚人，原本需要超過1小時，或者 50幾分的動作，在20秒內，就全部完成了資料匯入SQL的部分。大致上的時間比較，如果下表表示：

方式	時間
LINQ to Object 判斷是否重複	> 60 分
SQL 逐筆 Insert, 比對是否重複	> 60 分
SQL 一次產生 8萬筆， Insert到無Key Table 再Insert ... Select ...Group	約 50 分
BULK Insert到無KeyTable 再Insert ... Select ... Group	約 0.4 分(20秒)

 

末記

大量的匯入資料，SQL提供了高效率的 BULK Insert 的方式，而本篇則是針對 Xml 的檔案格式，透過SqlXml提供的元件來進行，小喵特別筆記下來，也提供大家參考。

 

 

 

 

 

 

 

---

以下是簽名：

• 歡迎轉貼本站的文章，不過請在貼文主旨上加上【轉貼】，並在文章中附上本篇的超連結與站名【topcat姍舞之間的極度凝聚】，感恩大家的配合。
• 小喵大部分的文章會以小喵熟悉的語言VB.NET撰寫，如果您需要C#的Code，也許您可以試著用線上的工具進行轉換，這裡提供幾個參考
  • http://converter.telerik.com/
  • http://www.carlosag.net/tools/codetranslator/
  • http://www.developerfusion.com/tools/convert/vb-to-csharp/

---

Microsoft MVP Visual Studio and Development Technologies (2005~2019/6)

topcat Blog:http://www.dotblogs.com.tw/topcat