SQL Serverがデータを一括挿入する2つの方法(SqlBulkCopy+テーブル値パラメータ)
6351 ワード
次のスクリプトを実行して、テスト・データベースとテーブル値のプロパティ・タイプのテーブルを作成します.(次のテストで使います).
最も簡単なInsert文を使用して、100万件のデータを挿入します.コードは次のとおりです.
運転が遅すぎて、10万本挿入するだけで72390 millisecondsかかるので、手動で強引に止めました.
次にBulkを使用して挿入する場合:
bulkメソッドの主な考え方は,クライアントでデータをすべてTableにキャッシュし,SqlBulkCopyを用いてTableのデータを一度にデータベースに挿入することである.
コードは次のとおりです.
Bulkを使用すると、効率とパフォーマンスが大幅に向上します.Insertを使用して10万データを挿入するには72390時間かかり、Bulkを使用して100万データを挿入するには17583時間かかります.
最後に、テーブル値パラメータを使用する効率を見てみると、驚きます.表値パラメータはSQL Server 2008の新しい特性で、TVpsと略称します.表値パラメータに詳しくない方は、最新のbook onlineを参考にして、今回は表値パラメータの概念をあまり紹介しません.本題に戻り、コードを見てください.
Bulkよりも5秒速い.
--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万件のデータを挿入します.コードは次のとおりです.
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のデータを一度にデータベースに挿入することである.
コードは次のとおりです.
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を参考にして、今回は表値パラメータの概念をあまり紹介しません.本題に戻り、コードを見てください.
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秒速い.