批量插入 SqlBulkCopy的测试

关于SqlBulkCopy的测试

最近要做.net关于sql大量插入，找到了sqlbulkcopy(自己google下，应该很多说明了)这个好东西，于是测试下性能，用了三个方法对比：

1)直接用ado.net,for循环N次进行单条插入

2)把N条插入语句拼在一个sql，进行插入

3)直接使用sqlbulkcopy进行插入

代码如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using System.Diagnostics;   namespace SQLTEST {      class Program      {          static void Main( string [] args)          {                //int time = 200;              test(20);              test(200);              test(2000);              test(10000);              test(50000);              Console.ReadLine();          }            public static void test( int time){              Stopwatch sp = new Stopwatch();              Console.WriteLine(time + "条数据插入测试" );                //测试方法1              {                  using (SqlConnection sqlcon = new SqlConnection( "Data Source=.;Initial Catalog=Test;User ID=sa;Password=??" ))                  {                      sqlcon.Open();                      string singesql = "INSERT INTO [student] ([name],[age])VALUES('abc',3);" ;                      SqlCommand sqlcommand = new SqlCommand(singesql, sqlcon);                      //计时开始                      sp.Restart();                      for ( int i = 0; i < time; i++)                      {                          sqlcommand.ExecuteNonQuery();                      }                      sp.Stop();                  }              }              Console.WriteLine( "方法1：" + sp.ElapsedMilliseconds + "毫秒" );                    //测试方法2              {                  using (SqlConnection sqlcon = new SqlConnection( "Data Source=.;Initial Catalog=Test;User ID=sa;Password=??" ))                  {                      sqlcon.Open();                      string singesql = "INSERT INTO [student] ([name],[age])VALUES('abc',3);" ;                      string execsql = "" ;                      for ( int i = 0; i < time; i++)                      {                          execsql = execsql + singesql;                      }                      SqlCommand sqlcommand = new SqlCommand(execsql, sqlcon);                        //计时开始                      sp.Restart();                      sqlcommand.ExecuteNonQuery();                      sp.Stop();                  }              }              Console.WriteLine( "方法2：" + sp.ElapsedMilliseconds + "毫秒" );                  //测试方法3              {                  using (SqlConnection sqlcon = new SqlConnection( "Data Source=.;Initial Catalog=Test;User ID=sa;Password=??" ))                  {                      sqlcon.Open();                      SqlBulkCopy sqlc = new SqlBulkCopy(sqlcon);                      DataTable dt = new DataTable();                      dt.Columns.Add( "id" );                      dt.Columns.Add( "name" );                      dt.Columns.Add( "age" );                        for ( int i = 0; i < time; i++)                      {                          dt.Rows.Add(38009, "nemw" , 123);                      }                      sqlc.DestinationTableName = "student" ;                        //计时开始                      sp.Restart();                      sqlc.WriteToServer(dt);                      sp.Stop();                  }              }              Console.WriteLine( "方法3：" + sp.ElapsedMilliseconds + "毫秒" );              Console.WriteLine();                    }      } }

　　

插入N条数据的测试结果如下：

效率相差还是很夸张的，大家大量数据插入还是有sqlbulkcopy吧，原理还的高手告知~~

转载于:https://www.cnblogs.com/efreer/p/5507211.html