SQL Server批量插入数据的两种高效方法

在SQL Server 中插入一条数据使用Insert语句,但是如果想要批量插入一堆数据的话,循环使用Insert不仅效率低,而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法:Bulk和表值参数(Table-Valued Parameters)。

运行下面的脚本,建立测试数据库和表值参数。

  1. –Create DataBase
  2. create database BulkTestDB;
  3. go
  4. use BulkTestDB;
  5. go
  6. –Create Table
  7. Create table BulkTestTable(
  8. Id int primary key,
  9. UserName nvarchar(32),
  10. Pwd varchar(16))
  11. go
  12. –Create Table Valued
  13. CREATE TYPE BulkUdt AS TABLE
  14.   (Id int,
  15.    UserName nvarchar(32),
  16.    Pwd varchar(16))

下面我们使用最简单的Insert语句来插入100万条数据,代码如下:

  1. Stopwatch sw = new Stopwatch();
  2. SqlConnection sqlConn = new SqlConnection(
  3.     ConfigurationManager.ConnectionStrings[“ConnStr”].ConnectionString);//连接数据库
  4. SqlCommand sqlComm = new SqlCommand();
  5. sqlComm.CommandText = string.Format(“insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)”);//参数化SQL
  6. sqlComm.Parameters.Add(“@p0”, SqlDbType.Int);
  7. sqlComm.Parameters.Add(“@p1”, SqlDbType.NVarChar);
  8. sqlComm.Parameters.Add(“@p2”, SqlDbType.VarChar);
  9. sqlComm.CommandType = CommandType.Text;
  10. sqlComm.Connection = sqlConn;
  11. sqlConn.Open();
  12. try
  13. {
  14.     //循环插入100万条数据,每次插入10万条,插入10次。
  15.     for (int multiply = 0; multiply < 10; multiply++)
  16.     {
  17.         for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
  18.         {
  19.             sqlComm.Parameters[“@p0”].Value = count;
  20.             sqlComm.Parameters[“@p1”].Value = string.Format(“User-{0}”, count * multiply);
  21.             sqlComm.Parameters[“@p2”].Value = string.Format(“Pwd-{0}”, count * multiply);
  22.             sw.Start();
  23.             sqlComm.ExecuteNonQuery();
  24.             sw.Stop();
  25.         }
  26.         //每插入10万条数据后,显示此次插入所用时间
  27.         Console.WriteLine(string.Format(“Elapsed Time is {0} Milliseconds”, sw.ElapsedMilliseconds));
  28.     }
  29. }
  30. catch (Exception ex)
  31. {
  32.     throw ex;
  33. }
  34. finally
  35. {
  36.     sqlConn.Close();
  37. }
  38. Console.ReadLine();

耗时图如下:

使用Insert语句插入10万数据的耗时图

由于运行过慢,才插入10万条就耗时72390 milliseconds,所以我就手动强行停止了。

 

下面看一下使用Bulk插入的情况:

bulk方法主要思想是通过在客户端把数据都缓存在Table中,然后利用SqlBulkCopy一次性把Table中的数据插入到数据库

代码如下:

  1. public static void BulkToDB(DataTable dt)
  2. {
  3.     SqlConnection sqlConn = new SqlConnection(
  4.         ConfigurationManager.ConnectionStrings[“ConnStr”].ConnectionString);
  5.     SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
  6.     bulkCopy.DestinationTableName = “BulkTestTable”;
  7.     bulkCopy.BatchSize = dt.Rows.Count;
  8.     try
  9.     {
  10.         sqlConn.Open();
  11.     if (dt != null && dt.Rows.Count != 0)
  12.         bulkCopy.WriteToServer(dt);
  13.     }
  14.     catch (Exception ex)
  15.     {
  16.         throw ex;
  17.     }
  18.     finally
  19.     {
  20.         sqlConn.Close();
  21.         if (bulkCopy != null)
  22.             bulkCopy.Close();
  23.     }
  24. }
  25. public static DataTable GetTableSchema()
  26. {
  27.     DataTable dt = new DataTable();
  28.     dt.Columns.AddRange(new DataColumn[]{
  29.         new DataColumn(“Id”,typeof(int)),
  30.         new DataColumn(“UserName”,typeof(string)),
  31.     new DataColumn(“Pwd”,typeof(string))});
  32.     return dt;
  33. }
  34. static void Main(string[] args)
  35. {
  36.     Stopwatch sw = new Stopwatch();
  37.     for (int multiply = 0; multiply < 10; multiply++)
  38.     {
  39.         DataTable dt = Bulk.GetTableSchema();
  40.         for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
  41.         {
  42.             DataRow r = dt.NewRow();
  43.             r[0] = count;
  44.             r[1] = string.Format(“User-{0}”, count * multiply);
  45.             r[2] = string.Format(“Pwd-{0}”, count * multiply);
  46.             dt.Rows.Add(r);
  47.         }
  48.         sw.Start();
  49.         Bulk.BulkToDB(dt);
  50.         sw.Stop();
  51.         Console.WriteLine(string.Format(“Elapsed Time is {0} Milliseconds”, sw.ElapsedMilliseconds));
  52.     }
  53.     Console.ReadLine();
  54. }

耗时图如下:

使用Bulk插入100万数据的耗时图

可见,使用Bulk后,效率和性能明显上升。使用Insert插入10万数据耗时72390,而现在使用Bulk插入100万数据才耗时17583。

 

最后再看看使用表值参数的效率,会另你大为惊讶的。

 

表值参数是SQL Server 2008新特性,简称TVPs。对于表值参数不熟悉的朋友,可以参考最新的book online,我也会另外写一篇关于表值参数的博客,不过此次不对表值参数的概念做过多的介绍。言归正传,看代码:

  1. public static void TableValuedToDB(DataTable dt)
  2. {
  3.     SqlConnection sqlConn = new SqlConnection(
  4.       ConfigurationManager.ConnectionStrings[“ConnStr”].ConnectionString);
  5.     const string TSqlStatement =
  6.      “insert into BulkTestTable (Id,UserName,Pwd)” +
  7.      ” SELECT nc.Id, nc.UserName,nc.Pwd” +
  8.      ” FROM @NewBulkTestTvp AS nc”;
  9.     SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);
  10.     SqlParameter catParam = cmd.Parameters.AddWithValue(“@NewBulkTestTvp”, dt);
  11.     catParam.SqlDbType = SqlDbType.Structured;
  12.     //表值参数的名字叫BulkUdt,在上面的建立测试环境的SQL中有。
  13.     catParam.TypeName = “dbo.BulkUdt”;
  14.     try
  15.     {
  16.       sqlConn.Open();
  17.       if (dt != null && dt.Rows.Count != 0)
  18.       {
  19.           cmd.ExecuteNonQuery();
  20.       }
  21.     }
  22.     catch (Exception ex)
  23.     {
  24.       throw ex;
  25.     }
  26.     finally
  27.     {
  28.       sqlConn.Close();
  29.     }
  30. }
  31. public static DataTable GetTableSchema()
  32. {
  33.     DataTable dt = new DataTable();
  34.     dt.Columns.AddRange(new DataColumn[]{
  35.       new DataColumn(“Id”,typeof(int)),
  36.       new DataColumn(“UserName”,typeof(string)),
  37.       new DataColumn(“Pwd”,typeof(string))});
  38.     return dt;
  39. }
  40. static void Main(string[] args)
  41. {
  42.     Stopwatch sw = new Stopwatch();
  43.     for (int multiply = 0; multiply < 10; multiply++)
  44.     {
  45.         DataTable dt = TableValued.GetTableSchema();
  46.         for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
  47.         {
  48.             DataRow r = dt.NewRow();
  49.             r[0] = count;
  50.             r[1] = string.Format(“User-{0}”, count * multiply);
  51.             r[2] = string.Format(“Pwd-{0}”, count * multiply);
  52.             dt.Rows.Add(r);
  53.         }
  54.         sw.Start();
  55.         TableValued.TableValuedToDB(dt);
  56.         sw.Stop();
  57.         Console.WriteLine(string.Format(“Elapsed Time is {0} Milliseconds”, sw.ElapsedMilliseconds));
  58.     }
  59.     Console.ReadLine();
  60. }

耗时图如下:

使用表值参数插入100万数据的耗时图

比Bulk还快5秒。

 

如需转载,请注明此文原创自CSDN TJVictor专栏:http://blog.csdn.net/tjvictor/archive/2009/07/18/4360030.aspx


1 星2 星3 星4 星5 星 (1 人评价, 平均分: 5.00 / 5)
Loading...

发表评论