你的位置:首页 > ASP.net教程

[ASP.net教程]关于SqlBulkCopy的测试


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

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

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

3)直接使用sqlbulkcopy进行插入

代码如下:

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吧,原理还的高手告知~~