今天早上reader 上收到cnblogs的订阅里看到一个关于SQL语句快速插入的文章,提到SqlBulkCopy,感觉不错,按他的测试SqlBulkCopy要比普通插入快近30倍,
按这个来算,我们那个发水票的时间就会由 10分钟-->20秒,这可太神奇了。
于是乎,下demo,测试,改成自己一般使用的方法测试,NND,还真可以说是极速。
在此贴上我的Demo:UploadFiles//SqlBulkCopy.rar
- using System;
- using System.Diagnostics;
- using System.Data;
- using System.Data.SqlClient;
- using Microsoft.ApplicationBlocks.Data;
- using System.Text;
-
- namespace ConsoleAppInsertTest
- {
- class Program
- {
- static int count = 100000;
- static void Main(string[] args)
- {
- long runTime = 0;
-
- SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
- runTime = SqlBulkCopyInsert();
- Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", runTime, count));
-
- SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
- runTime = TransactionInsert2();
- Console.WriteLine(string.Format(" 使用事务插入{1}条数据所用的时间是{0}毫秒 --使用事务插入数据,分组执行", runTime, count));
-
- SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
- runTime = TransactionInsert1();
- Console.WriteLine(string.Format(" 使用事务插入{1}条数据所用的时间是{0}毫秒 --先存到string再一次性执行", runTime, count));
-
- SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
- runTime = TransactionInsert();
- Console.WriteLine(string.Format(" 使用事务插入{1}条数据所用的时间是{0}毫秒 --在事务里一条一条执行", runTime, count));
-
-
- SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
- runTime = CommonInsert1();
- Console.WriteLine(string.Format(" 普通方式插入{1}条数据所用的时间是{0}毫秒 --保持SqlConnection", runTime, count));
-
- SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "TRUNCATE TABLE dbo.Passport");
- runTime = CommonInsert();
- Console.WriteLine(string.Format(" 普通方式插入{1}条数据所用的时间是{0}毫秒 --一条一条插入", runTime, count));
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- Console.ReadLine();
-
- }
-
-
-
-
-
- private static long CommonInsert()
- {
- Stopwatch stopwatch = new Stopwatch();
- stopwatch.Start();
- for (int i = 0; i < count; i++)
- {
- SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
- }
- stopwatch.Stop();
- return stopwatch.ElapsedMilliseconds;
- }
-
-
-
-
-
-
- private static long CommonInsert1()
- {
- Stopwatch stopwatch = new Stopwatch();
- stopwatch.Start();
- SqlConnection sqlconn = new SqlConnection(SqlHelper.SqlConnection);
- for (int i = 0; i < count; i++)
- {
- SqlHelper.ExecuteNonQuery(sqlconn, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
- }
- stopwatch.Stop();
- return stopwatch.ElapsedMilliseconds;
- }
-
-
-
-
-
-
- private static long TransactionInsert()
- {
- Stopwatch stopwatch = new Stopwatch();
- stopwatch.Start();
-
- using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))
- {
- ConnNow.Open();
- using (SqlTransaction trans = ConnNow.BeginTransaction())
- {
- try
- {
- for (int i = 0; i < count; i++)
- {
- SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
- }
- trans.Commit();
- }
- catch (Exception ex)
- {
- trans.Rollback();
- }
- }
- }
-
- stopwatch.Stop();
- return stopwatch.ElapsedMilliseconds;
- }
-
-
-
-
-
- private static long TransactionInsert1()
- {
- Stopwatch stopwatch = new Stopwatch();
- stopwatch.Start();
- StringBuilder sb = new StringBuilder();
- for (int i = 0; i < count; i++)
- {
- sb.AppendFormat("insert into passport(PassportKey) values('{0}');", Guid.NewGuid());
- }
- using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))
- {
- ConnNow.Open();
- using (SqlTransaction trans = ConnNow.BeginTransaction())
- {
- try
- {
- SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sb.ToString());
- for (int i = 0; i < count; i++)
- {
- SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
- }
- trans.Commit();
- }
- catch (Exception ex)
- {
- trans.Rollback();
- }
- }
- }
-
- stopwatch.Stop();
- return stopwatch.ElapsedMilliseconds;
- }
-
-
-
-
-
- private static long TransactionInsert2()
- {
- Stopwatch stopwatch = new Stopwatch();
- stopwatch.Start();
- using (SqlConnection ConnNow = new SqlConnection(SqlHelper.SqlConnection))
- {
- ConnNow.Open();
- string tmp = string.Empty; ;
- int itmp = 0;
- using (SqlTransaction trans = ConnNow.BeginTransaction())
- {
- try
- {
- for (int i = 0; i < count; i++)
- {
- itmp++;
- tmp = tmp + "insert into passport(PassportKey) values('" + Guid.NewGuid() + "');";
- if (itmp == 180)
- {
- itmp = 0;
- SqlHelper.ExecuteNonQuery(trans, CommandType.Text, tmp);
- tmp = string.Empty;
- }
- }
- SqlHelper.ExecuteNonQuery(trans, CommandType.Text, tmp);
- trans.Commit();
- }
- catch (Exception ex)
- {
- trans.Rollback();
- }
- }
- }
-
- stopwatch.Stop();
- return stopwatch.ElapsedMilliseconds;
- }
-
-
-
-
-
- private static long SqlBulkCopyInsert()
- {
- Stopwatch stopwatch = new Stopwatch();
- stopwatch.Start();
-
- DataTable dataTable = GetTableSchema();
- for (int i = 0; i < count; i++)
- {
- DataRow dataRow = dataTable.NewRow();
- dataRow[2] = Guid.NewGuid();
- dataTable.Rows.Add(dataRow);
- }
-
-
-
- SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SqlHelper.SqlConnection);
- sqlBulkCopy.DestinationTableName = "Passport";
-
- if (dataTable != null && dataTable.Rows.Count != 0)
- {
- sqlBulkCopy.WriteToServer(dataTable);
- }
- sqlBulkCopy.Close();
-
-
- stopwatch.Stop();
- return stopwatch.ElapsedMilliseconds;
- }
-
-
- private static DataTable GetTableSchema()
- {
- return SqlHelper.ExecuteDataset(SqlHelper.SqlConnection, CommandType.Text, "select * from Passport where 1=2").Tables[0];
- }
-
- }
- }
|