|
在SQL Server 中插入一條數(shù)據(jù)使用Insert語句，但是如果想要批量插入一堆數(shù)據(jù)的話，循環(huán)使用Insert不僅效率低，而且會(huì)導(dǎo)致SQL一系統(tǒng)性能問題。下面介紹SQL Server支持的兩種批量數(shù)據(jù)插入方法：Bulk和表值參數(shù)(Table-Valued Parameters)。
運(yùn)行下面的腳本，建立測試數(shù)據(jù)庫和表值參數(shù)。
- --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萬條數(shù)據(jù)，代碼如下：
- 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)");
- 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
- {
-
- 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();
- }
-
- Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- sqlConn.Close();
- }
-
- Console.ReadLine();
耗時(shí)圖如下：
由于運(yùn)行過慢，才插入10萬條就耗時(shí)72390 milliseconds，所以我就手動(dòng)強(qiáng)行停止了。
下面看一下使用Bulk插入的情況：
bulk方法主要思想是通過在客戶端把數(shù)據(jù)都緩存在Table中，然后利用SqlBulkCopy一次性把Table中的數(shù)據(jù)插入到數(shù)據(jù)庫
代碼如下：
- 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();
- }
耗時(shí)圖如下：
可見，使用Bulk后，效率和性能明顯上升。使用Insert插入10萬數(shù)據(jù)耗時(shí)72390，而現(xiàn)在使用Bulk插入100萬數(shù)據(jù)才耗時(shí)17583。
最后再看看使用表值參數(shù)的效率，會(huì)另你大為驚訝的。
表值參數(shù)是SQL Server 2008新特性，簡稱TVPs。對(duì)于表值參數(shù)不熟悉的朋友，可以參考最新的book online，我也會(huì)另外寫一篇關(guān)于表值參數(shù)的博客，不過此次不對(duì)表值參數(shù)的概念做過多的介紹。言歸正傳，看代碼：
- 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;
-
- 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();
- }
耗時(shí)圖如下：
比Bulk還快5秒。
|
