快速批量导入庞大数据到SQL SERVER数据库（ADO.NET)-白红宇

快速批量导入庞大数据到SQL SERVER数据库（ADO.NET)

阅读量：5988 次

发布时间：2019-06-20

本文共 6906 字，大约阅读时间需要 23 分钟。

原文地址：

如果你需要在程序中批量插入成千上万行的数据，你会怎么编写代码呢？最近在帮朋友调优这个的时候，总结了几种方法，并对其进行比较。

大概的界面如下，我模拟了一个客户资料表.

数据我是放在一个XML文件的，大约6734行。类似下面的格式


     
     
      
      
       ALFKI
       
      
       Sina
       
      
       Maria Anders
       
      
       Sales Representative
       
      Obere Str. 57
 
      
       Berlin
       
      
       12209
       
      
       Germany
       
      
       030-0074321
       
      
       030-0076545
       
      
      
      
       ANATR
       
      
       Ana Trujillo Emparedados y helados
       
      
       Ana Trujillo
       
      
       Owner
       
      Avda. de la Constitución 2222
 
      
       México D.F.
       
      
       05021
       
      
       Mexico
       
      
       (5) 555-4729
       
      
       (5) 555-3745
       
      
      
      
       ANTON
       
      
       Antonio Moreno Taquería
       
      
       Antonio Moreno
       
      
       Owner
       
      Mataderos 2312
 
      
       México D.F.
       
      
       05023
       
      
       Mexico
       
      
       (5) 555-3932

下面首先在服务器稍微准备一下环境

USE [tempdb]GOSET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customers]( [CustomerID] [nchar](5) NOT NULL, [CompanyName] [nvarchar](40) NOT NULL, [ContactName] [nvarchar](30) NULL, [ContactTitle] [nvarchar](30) NULL, [Address] [nvarchar](60) NULL, [City] [nvarchar](15) NULL, [Region] [nvarchar](15) NULL, [PostalCode] [nvarchar](10) NULL, [Country] [nvarchar](15) NULL, [Phone] [nvarchar](24) NULL, [Fax] [nvarchar](24) NULL ) ON [PRIMARY] CREATE PROCEDURE [dbo].[usp_InsertCustomer] @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24) AS SET NOCOUNT ON INSERT INTO [dbo].[Customers] ( [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] ) VALUES ( @CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax )

我们在tempdb中创建了一个表和一个存储过程

首先，我们把数据加载到一个DataSet

DataSet ds = new DataSet();        private void btLoadData_Click(object sender, EventArgs e)        {            string dataFile = "CustomersData.xml"; ds.ReadXml(dataFile); bindingSource1.DataSource = ds; bindingSource1.DataMember = "Customers"; dataGridView1.DataSource = bindingSource1; }

然后，我们第一个测试代码是遍历这个DataSet，每一行提交一次

private string GetConnectionString()        {            return "server=(local);database=tempdb;integrated security=true;";        }        ///  /// 直接遍历，一个一个的提交给服务器。时间为265毫秒左右 /// 每一行都需要写日志 /// 
 ///  ///  private void btOneByOne_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(GetConnectionString()); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "usp_InsertCustomer"; cmd.CommandType = CommandType.StoredProcedure; conn.Open(); TimeSpan startTime = System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime; foreach (DataRow row in ds.Tables[0].Rows) { cmd.Parameters.Clear(); SqlParameter[] param = new SqlParameter[]{ new SqlParameter("@CustomerID",row[0].ToString()), new SqlParameter("@CompanyName",row[1].ToString()), new SqlParameter("@ContactName",row[2].ToString()), new SqlParameter("@ContactTitle",row[3].ToString()), new SqlParameter("@Address",row[4].ToString()), new SqlParameter("@City",row[5].ToString()), new SqlParameter("@Region",row[6].ToString()), new SqlParameter("@PostalCode",row[7].ToString()), new SqlParameter("@Country",row[8].ToString()), new SqlParameter("@Phone",row[9].ToString()), new SqlParameter("@Fax",row[10].ToString()) }; cmd.Parameters.AddRange(param); cmd.ExecuteNonQuery(); } conn.Close(); TimeSpan duration = System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime.Subtract(startTime); MessageBox.Show("已经全部插入成功，所用时间为" + duration.Milliseconds.ToString() + "毫秒"); }

接下来，我们使用ADO.NET内置的一个DataAdapter来提交

///         /// 这是使用Adapter的方式，其实还是遍历，而且语法也没有简单        /// 同时，速度甚至更慢。时间为650毫秒左右        /// 每一行都需要写日志        /// 
 ///  ///  private void btUseAdapter_Click(object sender, EventArgs e) { SqlDataAdapter adapter = new SqlDataAdapter(); adapter.AcceptChangesDuringUpdate = false;//为了演示目的，把这个开关关掉，以免它在更新完成后把数据集标记为未更改 adapter.UpdateBatchSize = 10;//这个好像也没有什么用 SqlConnection conn = new SqlConnection(GetConnectionString()); SqlCommand insertCommand = conn.CreateCommand(); insertCommand.CommandText = "usp_InsertCustomer"; insertCommand.CommandType = CommandType.StoredProcedure; insertCommand.UpdatedRowSource = UpdateRowSource.None; insertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); insertCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); insertCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30, "ContactName"); insertCommand.Parameters.Add("@ContactTitle", SqlDbType.NVarChar, 30, "ContactTitle"); insertCommand.Parameters.Add("@Address", SqlDbType.NVarChar, 60, "Address"); insertCommand.Parameters.Add("@City", SqlDbType.NVarChar, 15, "City"); insertCommand.Parameters.Add("@Region", SqlDbType.NVarChar, 15, "Region"); insertCommand.Parameters.Add("@PostalCode", SqlDbType.NVarChar, 10, "PostalCode"); insertCommand.Parameters.Add("@Country", SqlDbType.NVarChar, 15, "Country"); insertCommand.Parameters.Add("@Phone", SqlDbType.NVarChar, 24, "Phone"); insertCommand.Parameters.Add("@Fax", SqlDbType.NVarChar, 24, "Fax"); adapter.InsertCommand = insertCommand; TimeSpan startTime = System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime; adapter.Update(ds,"Customers"); TimeSpan duration = System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime.Subtract(startTime); MessageBox.Show("已经全部插入成功，所用时间为" + duration.Milliseconds.ToString() + "毫秒"); }

最后，我们找到了最快的方法

///         /// 使用新的API，批量导入,这个速度很快，大约26毫秒，很显然，这种方式只写一次日志，不会为每一行写日志        /// 
        ///         ///  private void btBCP_Click(object sender, EventArgs e) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { SqlBulkCopy bcp = new SqlBulkCopy(conn); bcp.DestinationTableName = "Customers"; bcp.BatchSize = 100;//这是批尺寸可以调整 for (int i = 0; i < 11; i++) { bcp.ColumnMappings.Add(i, i); } TimeSpan startTime = System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime; conn.Open(); bcp.WriteToServer(ds.Tables[0]); TimeSpan duration = System.Diagnostics.Process.GetCurrentProcess().UserProcessorTime.Subtract(startTime); MessageBox.Show("已经全部插入成功，所用时间为" + duration.Milliseconds.ToString() + "毫秒"); } }

还有一种办法是通过在服务器OPENXML，因为XML反复处理效率很差，所以就没有测试了，可以断定它肯定比其他几种还要慢。

另外提示一下，如果不用编程的方式，那么有其他三个可能的途径去做这个事情

1. BCP工具（这是一个命令行，可以做导入和导出，不过来源文件如果不规范，那么可能很费劲）

2. BULK INSERT语句（这是一个T-SQL语句，只能做导入，我们上面使用的SQLBULKCopy应该和他很类似）

3. XML Bulk Load（这是一套COM的对象模型，适合导入XML文档）

还有，在做大量的数据导入和导出时，可以考虑微软为SQL Server配套的SSIS（Integration Service)

转载地址：http://xbnlx.baihongyu.com/

你可能感兴趣的文章