---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流! ----------------------
利用普通的方法进行大数据的插入,速度十分的慢。
如图可见,25万8千条数据的插入,按照普通的方案,即便是独占Connection不断开,也需要将近20分钟。而对比bulk仅需要将近4秒钟,对比差距很大。
而bulk仅仅是开始,我们继续设法将效率进行提升!
为了更有效的对比(接下来的提升可能用上面的数据对比,无法显示出优势来),我们将在控制台当中,模拟100万条数据的插入。
- public static void BulkToDB(DataTable dt)
- {
- SqlConnection sqlConn = new SqlConnection(
- ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
- SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
- bulkCopy.DestinationTableName = "BulkTestTable";
- bulkCopy.BatchSize = dt.Rows.Count;
- try
- {
- sqlConn.Open();
- if (dt != null && dt.Rows.Count != 0)
- bulkCopy.WriteToServer(dt);
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- sqlConn.Close();
- if (bulkCopy != null)
- bulkCopy.Close();
- }
- }
- public static DataTable GetTableSchema()
- {
- DataTable dt = new DataTable();
- dt.Columns.AddRange(new DataColumn[]{
- new DataColumn("Id",typeof(int)),
- new DataColumn("UserName",typeof(string)),
- new DataColumn("Pwd",typeof(string))});
- return dt;
- }
- static void Main(string[] args)
- {
- Stopwatch sw = new Stopwatch();
- for (int multiply = 0; multiply < 10; multiply++)
- {
- DataTable dt = Bulk.GetTableSchema();
- for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
- {
- DataRow r = dt.NewRow();
- r[0] = count;
- r[1] = string.Format("User-{0}", count * multiply);
- r[2] = string.Format("Pwd-{0}", count * multiply);
- dt.Rows.Add(r);
- }
- sw.Start();
- Bulk.BulkToDB(dt);
- sw.Stop();
- Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
- }
- Console.ReadLine();
- }
接下来,使用表值参数的效率,会另你大为惊讶的
表值参数是SQL Server 2008新特性,简称TVPs。
- public static void TableValuedToDB(DataTable dt)
- {
- SqlConnection sqlConn = new SqlConnection(
- ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
- const string TSqlStatement =
- "insert into BulkTestTable (Id,UserName,Pwd)" +
- " SELECT nc.Id, nc.UserName,nc.Pwd" +
- " FROM @NewBulkTestTvp AS nc";
- SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);
- SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);
- catParam.SqlDbType = SqlDbType.Structured;
- //表值参数的名字叫BulkUdt,在上面的建立测试环境的SQL中有。
- catParam.TypeName = "dbo.BulkUdt";
- try
- {
- sqlConn.Open();
- if (dt != null && dt.Rows.Count != 0)
- {
- cmd.ExecuteNonQuery();
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- sqlConn.Close();
- }
- }
- public static DataTable GetTableSchema()
- {
- DataTable dt = new DataTable();
- dt.Columns.AddRange(new DataColumn[]{
- new DataColumn("Id",typeof(int)),
- new DataColumn("UserName",typeof(string)),
- new DataColumn("Pwd",typeof(string))});
- return dt;
- }
- static void Main(string[] args)
- {
- Stopwatch sw = new Stopwatch();
- for (int multiply = 0; multiply < 10; multiply++)
- {
- DataTable dt = TableValued.GetTableSchema();
- for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
- {
- DataRow r = dt.NewRow();
- r[0] = count;
- r[1] = string.Format("User-{0}", count * multiply);
- r[2] = string.Format("Pwd-{0}", count * multiply);
- dt.Rows.Add(r);
- }
- sw.Start();
- TableValued.TableValuedToDB(dt);
- sw.Stop();
- Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
- }
- Console.ReadLine();
- }
也就是说,百万级快5秒,数据上来看,速度提升将近25%,应该也算是一个不小的提升了。