C# 筆記（七）–SqlServer海量資料的插入效率初探

說明：本測試是由非專業人士做的非專業測試，不保證測試的合理性以及結果的正確性，僅供娛樂。

 

沒事想親自試一下sqlserver的效率問題，動手插入100萬條記錄，具體情況如下：

硬體設定：
  lenovo 系列電腦
  Intel Pentium(R) Dual E2180 @2.00GHz
  2.00GHz,2.00GB記憶體

軟體環境：
Windows XP Professional SP2
  VS2005
  SQL Server 2005 Express (VS2005內建學習版)
   

測試代碼：
資料庫連接已經處理好，在這裡僅負責進行插入和刪除資料操作.
private void TestHugeData_Write()//測試插入大量資料
{
  string test_sql;
  DateTime begin = DateTime.Now;
  int i = 0;
  for (; i < 1000000; i++)
  {
  test_sql = "insert into 員工資訊 values (" + i + ",'王大帥','男',333,'國際青年政治學院','博士','人類改造學',2004-4-3,'涉及機密，未便透漏','芸芸萬物，何足道哉','我未成名君未嫁，可能俱是不如人？')";
  //ExeSQL(test_sql); 
  //不用調用函數，將主要時間開銷放在執行SQL語句上以提高效率。
  //經實踐證明，這樣可以節省絕大部分時間。當用ExeSQL函數執行時，效率極為低下，估計幾個小時都未必能完成。直接執行的話，不到一分鐘就完成。
  cmd.CommandText = test_sql;
  cmd.Connection = connection;
  cmd.ExecuteNonQuery(); 
  }
  DateTime end = DateTime.Now;
  //顯示資料  
  ViewData(showAllString);
  DateTime show = DateTime.Now;
  MessageBox.Show("共插入" + i + "條記錄,用時 " + (end - begin)+", \r\n 顯示用時"+(show-end), "執行完畢!"); 
  }
 
 private void DelAllData()//刪除所有資料
  {
  DateTime begin = DateTime.Now;
  string strdelAll = "delete from 員工資訊 where e_id between -2147483648 and 2147483647";
//int類型欄位表示範圍，即刪除所有記錄。從效率考慮這樣可能比讀取一條記錄再刪除要好
  //ExeSQL(strdelAll);
  cmd.CommandText = strdelAll;
  cmd.CommandTimeout = 99999999;
//當有大量資料要刪除時，如果預設的30秒逾時時間不夠，就有可能會導致刪除失敗
  cmd.Connection = connection;
  cmd.ExecuteNonQuery();
  DateTime end = DateTime.Now;
  MessageBox.Show("所有記錄刪除完畢,用時 " + (end - begin), "執行完畢!"); 
  }

資料庫中表格設計如下：
e_id int
e_name varchar(12)
e_sex char(2)
e_age smallint
e_school varchar(50)
e_education varchar(10)
e_major varchar(30)
e_workdate datetime
e_studyExp text
e_workExp text

e_skill text

在執行SQL語句時，原來是調用另外一個專門執行SQL的函數ExeSQL 來執行，但是發現這樣的話效率極為底下，因為大部分的時間都浪費在執行ExeSQL裡的try catch上了，其代碼如下：

try
{
  //MessageBox.Show(strSql);
  cmd.CommandText = strSql;
  cmd.Connection = connection;
  int count=cmd.ExecuteNonQuery();  
  ViewData(showAllString);
  return true;
 }
catch (SqlException sqlExcep)
{
MessageBox.Show(sqlExcep.Message);
return false;
}
而且執行一次SQL語句都要重新調用ViewData進行顯示，這也耗費了大量時間，加上函數的調用，值返回等，最終結果是插入一條記錄都要好幾秒—這是不可忍受的。於是直接執行SQL語句，將主要時間開銷放在執行SQL語句上，效率明顯提高。最後程式執行的結果如下：
 
可以看到，在這裡程式插入100萬條記錄用了11分33秒，還算是比較高效的。但是在顯示資料時卻用了將近25分鐘，比插入過程還多了一倍，這是用datagridview進行顯示的結果，可能目前還沒有辦法改進。
在插入過程中，在工作管理員中可以看到，前一半時間主要是servr進程消耗資源，“員工管理”進程基本上沒有消耗什麼資源，而到插入完畢進行顯示時，情況就完全反過來。

上面TestHugeData_Write裡的代碼還可改進：
for (; i < 1000000; i++)
{
  ……
cmd.CommandText = test_sql;
  cmd.Connection = connection;
  cmd.ExecuteNonQuery(); 
}
cmd.Connection = connection;一句可以移到迴圈的外面,且看效率如何：
 
看來沒什麼提高。

刪除時情況如下（刪除上面的100萬條記錄）：
 
可見，刪除比插入要快多了。

最後是查詢的情況：
cmd.Connection = connection;
DateTime begin = DateTime.Now;
adapter.SelectCommand = cmd;
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
table.Clear();//清除原有資料，準備裝入新資料。如果不做此處理，要顯示線資料時會在原來的資料之上繼續添加
adapter.Fill(table);
DateTime end = DateTime.Now;
MessageBox.Show("查詢用時" + (end - begin), "執行完畢！"); 
bindingsource.DataSource = table;
dataGridView1.DataSource = bindingsource;

select * from 員工資訊 where e_id=823154
 
影響效率的，可能還有SQLServer版本本身，下面是SQL Server的產品規格

SQL Server 2005 版本               支援的最大記憶體（32 位）     支援的最大記憶體（64 位元） 

Enterprise Edition                          作業系統最大記憶體               作業系統最大記憶體
Developer Edition                          作業系統最大記憶體                32 TB
Standard Edition                           作業系統最大記憶體                32 TB
Workgroup Edition                        3 GB                               不適用
SQL Server Express Edition           1 GB                                不適用
Evaluation Edition                         作業系統最大記憶體                 作業系統最大記憶體

SQL Server 2005 版本                支援的處理器數（32 位）    支援的處理器數（64 位元） 
Enterprise Edition                         作業系統最大記憶體1               作業系統最大記憶體1
Developer Edition                          32                                    64
Standard Edition                           4                                     4
Workgroup Edition                        2                                     不適用
SQL Server Express Edition            1                                     不適用
Evaluation Edition                         作業系統最大記憶體                  作業系統最大記憶體

可以看到Express Edition的不支援多處理器（當然，雙核和多處理器可能不是一回事），只支援1G記憶體，導致硬體並沒有完全發揮作用。

上面是100萬條記錄集的測試，心有不足，繼續測試：插入1000萬條記錄。

經過漫長的等待，插入雖然沒有問題，可是datagridview顯示卻失敗了，具體如下：

所有代碼及環境都和上面相同（除了將插入100萬改為插入1000萬），最後得到的資料庫檔案為3.7G,

log檔案為5.3G,如下：

 

但在顯示時，將資料集傳給datagridview時，因為記憶體配置失敗而導致最終失敗：

try
{
  cmd.CommandText = strView;
  cmd.Connection = connection;
  cmd.CommandTimeout = 99999999;
  DateTime begin = DateTime.Now;
  adapter.SelectCommand = cmd;
  table.Locale = System.Globalization.CultureInfo.InvariantCulture;  
  table.Clear();//清除原有資料，準備裝入新資料。如果不做此處理，要顯示線資料時會在原來的資料之上繼續添加
  adapter.Fill(table);
  DateTime end = DateTime.Now;
  //MessageBox.Show("查詢用時" + (end - begin), "執行完畢！"); 
  bindingsource.DataSource = table;
  dataGridView1.DataSource = bindingsource;
}
具體是執行到adapter.Fill(table);一句因為記憶體配置失敗導致OutOfMemoryException異常：
 
 

 

 

 

同時程式所耗費的記憶體已超過1.5G:

詳細資料如下：

未處理 System.OutOfMemoryException
  Message="引發類型為“System.OutOfMemoryException”的異常。"
  Source="System.Windows.Forms"
  StackTrace:
  在 System.Windows.Forms.FeatureSupport.IsPresent(Object feature)
  在 System.Windows.Forms.Application.get_VisualStyleState()
  在 System.Windows.Forms.VisualStyles.VisualStyleRenderer.get_IsSupported()
  在 System.Windows.Forms.VisualStyles.VisualStyleRenderer.get_Handle()
  在 System.Windows.Forms.VisualStyles.VisualStyleRenderer.DrawBackground(IDeviceContext dc, Rectangle bounds, Rectangle clipRectangle)
  在 System.Windows.Forms.DataGridViewColumnHeaderCell.DataGridViewColumnHeaderCellRenderer.DrawHeader(Graphics g, Rectangle bounds, Int32 headerState)
  在 System.Windows.Forms.DataGridViewColumnHeaderCell.PaintPrivate(Graphics g, Rectangle clipBounds, Rectangle cellBounds, Int32 rowIndex, DataGridViewElementStates dataGridViewElementState, Object formattedValue, DataGridViewCellStyle cellStyle, DataGridViewAdvancedBorderStyle advancedBorderStyle, DataGridViewPaintParts paintParts, Boolean paint)
  在 System.Windows.Forms.DataGridViewColumnHeaderCell.Paint(Graphics graphics, Rectangle clipBounds, Rectangle cellBounds, Int32 rowIndex, DataGridViewElementStates dataGridViewElementState, Object value, Object formattedValue, String errorText, DataGridViewCellStyle cellStyle, DataGridViewAdvancedBorderStyle advancedBorderStyle, DataGridViewPaintParts paintParts)
  在 System.Windows.Forms.DataGridViewCell.PaintWork(Graphics graphics, Rectangle clipBounds, Rectangle cellBounds, Int32 rowIndex, DataGridViewElementStates cellState, DataGridViewCellStyle cellStyle, DataGridViewAdvancedBorderStyle advancedBorderStyle, DataGridViewPaintParts paintParts)
  在 System.Windows.Forms.DataGridView.PaintColumnHeaders(Graphics g, Rectangle clipBounds, Boolean singleBorderAdded)
  在 System.Windows.Forms.DataGridView.PaintGrid(Graphics g, Rectangle gridBounds, Rectangle clipRect, Boolean singleVerticalBorderAdded, Boolean singleHorizontalBorderAdded)
  在 System.Windows.Forms.DataGridView.OnPaint(PaintEventArgs e)
  在 System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs e, Int16 layer, Boolean disposeEventArgs)
  在 System.Windows.Forms.Control.WmPaint(Message& m)
  在 System.Windows.Forms.Control.WndProc(Message& m)
  在 System.Windows.Forms.DataGridView.WndProc(Message& m)
  在 System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
  在 System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
  在 System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  在 System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
  在 System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
  在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
  在 System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
  在 System.Windows.Forms.Application.Run(Form mainForm)
  在 員工管理.Program.Main() 位置 D:\作業\員工管理\員工管理1.3.1\員工管理\Program.cs:行號 17
  在 System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
  在 System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
  在 Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
  在 System.Threading.ThreadHelper.ThreadStart_Context(Object state)
  在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
  在 System.Threading.ThreadHelper.ThreadStart()
 
關於System.data.comman.dbdataapter.fill函數有如下說明：

Fill 方法使用關聯的 SelectCommand 屬性所指定的 SELECT 語句從資料來源中檢索行。與 SELECT 語句關聯的連線物件必須有效，但不需要將其開啟。如果調用 Fill 之前串連已關閉，則將其開啟以檢索資料，然後再將其關閉。如果調用 Fill 之前串連已開啟，它將保持開啟狀態。

然後，Fill 操作將行添加到 DataSet 中的目標 DataTable 對象，如果 DataTable 對象不存在，則建立這些對象。當建立 DataTable 對象時，Fill 操作通常只建立列名中繼資料。但是，如果 MissingSchemaAction 屬性設定為 AddWithKey，則還會建立適當的主鍵和約束。
如果在填充 DataTable 時遇到 DbDataAdapter 重複列，則以“columnname1”、“columnname2”、“columnname3”這樣依次排序的模式命名後面的列。如果傳入資料包含未命名的列，它們將按“Column1”、“Column2”的模式放在 DataSet 中。向 DataSet 添加多個結果集時，每個結果集都放在一個單獨的表中。
採用 DataTable 作為參數的 Fill 的重載僅擷取第一個結果。使用以 DataSet 作為參數的 Fill 重載將擷取多個結果。
Fill 方法支援以下情況：DataSet 包含多個 DataTable 對象，而這些對象的名稱只有大小寫不同。在這種情況下，Fill 執行區分大小寫比較以尋找相應的表，如果不存在完全符合的表，則建立一個。

估計效能也就到此為上限了。
雖然無法顯示全部，倒可以進行查詢，刪除，且看效率如何：
select * from 員工資訊 where e_id=8888888
 
看來還是挺快的。
刪除：

 

基本就是100萬條的10倍。

另外，網上搜尋得知大資料讀入時，通常都應該分頁讀入，可惜沒時間再仔細研究，就到這裡了。