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

另外，网上搜寻得知大数据读入时，通常都应该分页读入，可惜没时间再仔细研究，就到这里了。