SqlBulkCopy のスピードは 20 倍ぐらい早い

投稿日: 2011/06/07 作成者: masuda

SQL Server に insert を繰り返してデータを入れる場合は、

・bcp を使う。
・bulk insert を使う。
・SqlBulkCopy を使う。

を使います。bcp や bulk insert の場合は、ファイルからインポートするのでちょっと扱いづらい。SQL Server が別のマシン（サーバー機）にある場合は、一度ファイル転送をするか、ファイル共有をしないといけないので、ちょっと面倒です。

なので、SqlBulkCopy を使う…ってところまでは知っていたのですが、果たしてどのぐらいのスピードかどうかは定かだではないので、測定してみました。

結論から言えば、20 倍ほど早くなります。SQL Server 2008 の場合は 30 倍ほど、SQL Server 2000 の場合は 10-20 倍ほどなので業務コードに入れる場合は実測が必須ですね。

以下は、

1
2
3
4
create table bulk0 (
  id int,
  val varchar(100)
)

のテーブルに 10 万件のデータを挿入したときの結果です(CPU 1.7GHz程度)

1
2
3
4
5
6
7
8
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

以下は、実験用のコード。
SqlBulkCopy には、DataTable あるいは DataRow の配列を渡せるので、大量データの挿入が非常に楽になります。まあ、大量すぎる場合は、DataTable のメモリ溢れに注意する必要がありますが on memory に乗る量であれば、この程度で ok ということで。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
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();
        }
    }
}
カテゴリー: 開発, C# パーマリンク