SQL Server に insert を繰り返してデータを入れる場合は、
・bcp を使う。
・bulk insert を使う。
・SqlBulkCopy を使う。
を使います。bcp や bulk insert の場合は、ファイルからインポートするのでちょっと扱いづらい。SQL Server が別のマシン（サーバー機）にある場合は、一度ファイル転送をするか、ファイル共有をしないといけないので、ちょっと面倒です。
なので、SqlBulkCopy を使う…ってところまでは知っていたのですが、果たしてどのぐらいのスピードかどうかは定かだではないので、測定してみました。
結論から言えば、20 倍ほど早くなります。SQL Server 2008 の場合は 30 倍ほど、SQL Server 2000 の場合は 10-20 倍ほどなので業務コードに入れる場合は実測が必須ですね。
以下は、
[code]
create table bulk0 (
id int,
val varchar(100)
)
[/code]
のテーブルに 10 万件のデータを挿入したときの結果です(CPU 1.7GHz程度)
[code]
Normal 3.37 sec
Insert 46.34 sec
Normal 4.00 sec
Insert 43.29 sec
Normal 1.85 sec
Insert 47.50 sec
avg. Normal 3.07 sec
avg. Insert 45.71 sec
[/code]
以下は、実験用のコード。
SqlBulkCopy には、DataTable あるいは DataRow の配列を渡せるので、大量データの挿入が非常に楽になります。まあ、大量すぎる場合は、DataTable のメモリ溢れに注意する必要がありますが on memory に乗る量であれば、この程度で ok ということで。
[code lang=”csharp”]
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace SampleBluk
{
class TestBulk
{
public void Go()
{
double t1 = 0.0;
double t2 = 0.0;
_dt = MakeDataTable();
int max = 3;
for (int i = 0; i < max; i++)
{
DateTime start;
double span;
setup();
start = DateTime.Now;
TestNormal();
span = ((TimeSpan)(DateTime.Now – start)).TotalSeconds;
Console.WriteLine("Normal {0:0.00} sec", span);
t1 += span;
setup();
start = DateTime.Now;
TestInsert();
span = ((TimeSpan)(DateTime.Now – start)).TotalSeconds;
Console.WriteLine("Insert {0:0.00} sec", span);
t2 += span;
}
Console.WriteLine("avg. Normal {0:0.00} sec", t1 / max);
Console.WriteLine("avg. Insert {0:0.00} sec", t2 / max);
// avg. Normal 3.07 sec
// avg. Insert 45.71 sec
}
int _max = 100000;
DataTable _dt;
// 接続文字列
const string CNSTR =
@"Data Source=.\sqlexpress;Initial Catalog=stress;Integrated Security=True;Pooling=False";
private string toMD5(string s)
{
//文字列をbyte型配列に変換する
byte[] data = System.Text.Encoding.UTF8.GetBytes(s);
//MD5CryptoServiceProviderオブジェクトを作成
System.Security.Cryptography.MD5CryptoServiceProvider md5 =
new System.Security.Cryptography.MD5CryptoServiceProvider();
//または、次のようにもできる
//System.Security.Cryptography.MD5 md5 =
// u System.Security.Cryptography.MD5.Create();
//ハッシュ値を計算する
byte[] bs = md5.ComputeHash(data);
//byte型配列を16進数の文字列に変換
System.Text.StringBuilder result = new System.Text.StringBuilder();
return BitConverter.ToString(bs).ToLower().Replace("-", "");
}
public DataTable MakeDataTable()
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("id", typeof(int)));
dt.Columns.Add(new DataColumn("val", typeof(string)));
string s = toMD5(DateTime.Now.ToString());
for (int i = 0; i < _max; i++)
{
DataRow row = dt.NewRow();
dt.Rows.Add(row);
row["id"] = i;
row["val"] = s;
s = toMD5(s);
}
return dt;
}
public void setup()
{
SqlConnection cn = new SqlConnection(CNSTR);
SqlCommand cmd = new SqlCommand("truncate table bulk0", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
public void TestNormal()
{
DataTable dt = _dt;
SqlConnection cn = new SqlConnection(CNSTR);
SqlBulkCopy bc = new SqlBulkCopy(cn);
bc.DestinationTableName = "bulk0";
cn.Open();
bc.WriteToServer(dt);
cn.Close();
}
public void TestInsert()
{
DataTable dt = _dt;
SqlConnection cn = new SqlConnection(CNSTR);
SqlCommand cmd = new SqlCommand("insert into bulk0 ( id, val ) values ( @id, @val ) ", cn);
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@val", SqlDbType.VarChar, 100));
cn.Open();
foreach (DataRow row in dt.Rows)
{
cmd.Parameters["@id"].Value = row["id"];
cmd.Parameters["@val"].Value = row["val"];
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
}
[/code]