做外贸如何选择网站,wordpress 幻灯片主题,律师在线咨询免费24小时电话,百度教育智能小程序在SQL Server 中插入一条数据使用Insert语句#xff0c;但是如果想要批量插入一堆数据的话#xff0c;循环使用Insert不仅效率低#xff0c;而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法#xff1a;Bulk和表值参数(Table-Valued Parameters…在SQL Server 中插入一条数据使用Insert语句但是如果想要批量插入一堆数据的话循环使用Insert不仅效率低而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法Bulk和表值参数(Table-Valued Parameters)。 运行下面的脚本建立测试数据库和表值参数。 [c-sharp] view plaincopyprint? --Create DataBase create database BulkTestDB; go use BulkTestDB; go --Create Table Create table BulkTestTable( Id int primary key, UserName nvarchar(32), Pwd varchar(16)) go --Create Table Valued CREATE TYPE BulkUdt AS TABLE (Id int, UserName nvarchar(32), Pwd varchar(16)) --Create DataBase create database BulkTestDB; go use BulkTestDB; go --Create Table Create table BulkTestTable( Id int primary key, UserName nvarchar(32), Pwd varchar(16)) go --Create Table Valued CREATE TYPE BulkUdt AS TABLE (Id int, UserName nvarchar(32), Pwd varchar(16)) 下面我们使用最简单的Insert语句来插入100万条数据代码如下 [c-sharp] view plaincopyprint? Stopwatch sw new Stopwatch(); SqlConnection sqlConn new SqlConnection( ConfigurationManager.ConnectionStrings[ConnStr].ConnectionString);//连接数据库 SqlCommand sqlComm new SqlCommand(); sqlComm.CommandText string.Format(insert into BulkTestTable(Id,UserName,Pwd)values(p0,p1,p2));//参数化SQL sqlComm.Parameters.Add(p0, SqlDbType.Int); sqlComm.Parameters.Add(p1, SqlDbType.NVarChar); sqlComm.Parameters.Add(p2, SqlDbType.VarChar); sqlComm.CommandType CommandType.Text; sqlComm.Connection sqlConn; sqlConn.Open(); try { //循环插入100万条数据每次插入10万条插入10次。 for (int multiply 0; multiply 10; multiply) { for (int count multiply * 100000; count (multiply 1) * 100000; count) { sqlComm.Parameters[p0].Value count; sqlComm.Parameters[p1].Value string.Format(User-{0}, count * multiply); sqlComm.Parameters[p2].Value string.Format(Pwd-{0}, count * multiply); sw.Start(); sqlComm.ExecuteNonQuery(); sw.Stop(); } //每插入10万条数据后显示此次插入所用时间 Console.WriteLine(string.Format(Elapsed Time is {0} Milliseconds, sw.ElapsedMilliseconds)); } } catch (Exception ex) { throw ex; } finally { sqlConn.Close(); } Console.ReadLine(); Stopwatch sw new Stopwatch(); SqlConnection sqlConn new SqlConnection( ConfigurationManager.ConnectionStrings[ConnStr].ConnectionString);//连接数据库 SqlCommand sqlComm new SqlCommand(); sqlComm.CommandText string.Format(insert into BulkTestTable(Id,UserName,Pwd)values(p0,p1,p2));//参数化SQL sqlComm.Parameters.Add(p0, SqlDbType.Int); sqlComm.Parameters.Add(p1, SqlDbType.NVarChar); sqlComm.Parameters.Add(p2, SqlDbType.VarChar); sqlComm.CommandType CommandType.Text; sqlComm.Connection sqlConn; sqlConn.Open(); try { //循环插入100万条数据每次插入10万条插入10次。 for (int multiply 0; multiply 10; multiply) { for (int count multiply * 100000; count (multiply 1) * 100000; count) { sqlComm.Parameters[p0].Value count; sqlComm.Parameters[p1].Value string.Format(User-{0}, count * multiply); sqlComm.Parameters[p2].Value string.Format(Pwd-{0}, count * multiply); sw.Start(); sqlComm.ExecuteNonQuery(); sw.Stop(); } //每插入10万条数据后显示此次插入所用时间 Console.WriteLine(string.Format(Elapsed Time is {0} Milliseconds, sw.ElapsedMilliseconds)); } } catch (Exception ex) { throw ex; } finally { sqlConn.Close(); } Console.ReadLine(); 耗时图如下 由于运行过慢才插入10万条就耗时72390 milliseconds所以我就手动强行停止了。 下面看一下使用Bulk插入的情况 bulk方法主要思想是通过在客户端把数据都缓存在Table中然后利用SqlBulkCopy一次性把Table中的数据插入到数据库 代码如下 [c-sharp] view plaincopyprint? 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(); } 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(); } 耗时图如下 可见使用Bulk后效率和性能明显上升。使用Insert插入10万数据耗时72390而现在使用Bulk插入100万数据才耗时17583。 最后再看看使用表值参数的效率会另你大为惊讶的。 表值参数是SQL Server 2008新特性简称TVPs。对于表值参数不熟悉的朋友可以参考最新的book online我也会另外写一篇关于表值参数的博客不过此次不对表值参数的概念做过多的介绍。言归正传看代码 [c-sharp] view plaincopyprint? 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(); } 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(); } 耗时图如下 比Bulk还快5秒。转载于:https://www.cnblogs.com/Nina-piaoye/archive/2013/03/13/2956949.html
