使用SqlBulkCopy將資料批次寫入資料庫

前demo有介紹利用SqlDataSoure和手動撰寫ADO.NET的方式大量新增資料的方法,雖然已經有效的改善了寫入的速度,但在發現了SqlBulkCopy以後,發現它更是威力強大,現在就來介紹SqlBulkCopy的猛。

3

SHARES

FacebookGoogle+TwitterMore

★注意事項:本文是延續將資料大量寫入資料庫時的優化寫法的更優化如果對於文中有不清楚之意,可利用本文最下方相關的文章進入觀看資料大量寫入資料庫時的優化寫法一文

 

▲說明都在程式註解中啦

  //一開始我們先產生一個DataTable來裝我們要寫入的資料
 DataTable dt = new DataTable();
 dt.Columns.Add("id", typeof(int));
 dt.Columns.Add("name", typeof(string));
 
 //因為SqlBulkCopy的猛就是大量的一次寫入,所以我們也來跑10萬筆吧
 int i;
 for (i = 0; i < 100000; i++)
 {
     DataRow dr = dt.NewRow();
     dr["name"] = i.ToString();
     dt.Rows.Add(dr);
 }
 
 //宣告連結字串
 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString);
 
 conn.Open();
 //宣告SqlBulkCopy
 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.Table1";
              
             //對應資料行
             sqlBC.ColumnMappings.Add("id", "id");
             sqlBC.ColumnMappings.Add("name", "name");
 
             //開始寫入
             sqlBC.WriteToServer(dt);
         }
         conn.Dispose();
 }
 void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
 {
     Response.Write("---<br/>");
 } 
  • 測試環境:SQL2005 Express
  • 測試資料量:10萬筆
  • 測試次數:10次
  • 平均秒數:2.3532秒

?小鋪廢言:太可怕啦,之前的寫法如果真的要寫10萬筆這種大量的資料都需花費一分鐘左右,但使用了SqlBulkCopy卻只要短短的兩秒鐘,下表看的出來如果資料筆數很少就沒必要使用SqlBulkCopy了。

 

  • 寫入十萬筆資料10次的平均秒數
    • 使用SqlBulkCopy:2.2051
    • 使用AddWithValue:63.418
  • 寫入一萬筆資料10次的平均秒數
    • 使用SqlBulkCopy:0.2188
    • 使用AddWithValue:6.3856
  • 寫入一千筆資料10次的平均秒數
    • 使用SqlBulkCopy:0.0187
    • 使用AddWithValue:0.5805
  • 寫入一百筆資料10次的平均秒數
    • 使用SqlBulkCopy:0.0062
    • 使用AddWithValue:0.0353
  • 寫入十筆資料10次的平均秒數
    • 使用SqlBulkCopy:0.004
    • 使用AddWithValue:0.004

 


http://bibby.be/2008/04/blog-post_25.html
http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlbulkcopy_members(VS.80).aspx

Read more

How to migrate Raspberry Pi 5 OS from micro SD to NVME m.2 SSD

首先我買了Raspberry Pi CM5後來買了Raspberry Pi CM5 I/O board來當個人電腦使用,系統是安裝在256GB SD卡上運行的很好。用久了在開啟較肥的程式像Web Browser或LiberOffice會有慢半拍的反應,而有了升級NVME m.2 SSD念頭。 因為Raspberry Pi 5支援的最快PCIe gen3 x 4就不去考慮快的Gen4 or Gen5 m.2 SSD。找了ADATA出的 LEGEND 710入門級的產品,會利用HMB(Host Memory Buffer)來加速I/O速度,因為是Raspberry Pi OS kernel會認不得而無法正常使用 事先在SD卡的/boot/firmware/cmdline.txt 加入 kernel command line參數如下,然後重開機m.

By Phillips Hsieh

How to document Home Lab and Network

運維機房和跨域的網路,會遇到各式需求與問題,用對工具才能分析問題,個人覺得最重要的是使用能處理問題的工具。 推薦目前想學和正在使用的平台與軟體,協助將公司/家用機房文件化 佈告欄任務管理 Focalboard 白板可管理任務指派 網路架構文件編寫 netbox 精細管理網路設備與連接線路 IP 資源管理 phpipam 專注網路IP分配 邏輯塊文件編寫 draw.io 視覺化概念圖 機房設備管理 ITDB 管理設備生命週期與使用者

By Phillips Hsieh

如何在Raspberry Pi4上安裝Proxmox for ARM64

第一步 準備好Raspberry Pi 4 / CM4 4GB RAM,這裡要留意CM4如果是買有內建eMMC storage會限制不能使用SD卡開機而限制本地空間容量,如果沒有NAS外接空間或使用USB開機的話,建議買CM4 Lite插上大容量SD卡 第二步 去Armbian官網下載最小化Debian bookworm image https://www.armbian.com/rpi4b/ Armbian 25.2.2 Bookworm Minimal / IOT 然後寫入SD/USB開機碟,寫入方法參考官方文件 https://github.com/raspberrypi/usbboot/blob/master/Readme.md Note: 官方提供的預先設定系統方法,可以在Armbian初次啟動自動化完成系統設定。連結在此 https://docs.armbian.com/User-Guide_Autoconfig/

By Phillips Hsieh

世界越快心越慢

在晚飯後的休息時間,我特別享受在客廳瀏灠youtube上各樣各式創作者的影音作品。很大不同於傳統媒體,節目多是針對大多數族群喜好挑選的,在youtube上我會依心情看無腦的動畫、一些旅拍記錄、新聞時事談論。 尤其在看了大量的Youtube的分享後,我真的感受到會限制我的是我的無知,特別是那些我想都沒想過的實際應用,在學習後大大幫助到我的生活和工作層面。 休息在家時,我喜歡想一些沒做過的菜,動手去設計生活和工作上的解決方案,自己是真的很難閒著沒事做。 如創作文章,陪養新的習慣都能感覺到成長的喜悅,是不同於吃喝玩樂的快樂的。 創作不去限制固定的形式,文字是創作、影像聲音也是創作,記錄生活也是創作,我想留下的就是創造—》實現—》回憶,這樣子的循環過程,在留下的足跡面看到自己一路上的成長、失敗、絕望、重新再來。 雖然大部份的時候去做這些創作也不明白有什麼特別的意義,但不去做也不會留下什麼,所以呀不如反事都去試試看,也許能有不一樣的水花也許有意想不到的結果,投資自己永遠不會是失敗的決定,不是嗎?先問問自己再開始計畫下一步,未來沒人說得準。 像最近看youtube仍大一群人在為DOS開

By Phillips Hsieh