正文
批量插入 SqlBulkCopy的测试
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
关于SqlBulkCopy的测试
最近要做.net关于sql大量插入，找到了sqlbulkcopy(自己google下，应该很多说明了)这个好东西，于是测试下性能，用了三个方法对比：
1)直接用ado.net,for循环N次进行单条插入
2)把N条插入语句拼在一个sql，进行插入
3)直接使用sqlbulkcopy进行插入
代码如下：
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
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;
using
System.Data;
using
System.Data.SqlClient;
using
System.Diagnostics;
namespace
SQLTEST
{
class
Program
{
static
void
Main(
string
[] args)
{
//int time = 200;
test(20);
test(200);
test(2000);
test(10000);
test(50000);
Console.ReadLine();
}
public
static
void
test(
int
time){
Stopwatch sp =
new
Stopwatch();
Console.WriteLine(time +
"条数据插入测试"
);
//测试方法1
{
using
(SqlConnection sqlcon =
new
SqlConnection(
"Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"
))
{
sqlcon.Open();
string
singesql =
"INSERT INTO [student] ([name],[age])VALUES('abc',3);"
;
SqlCommand sqlcommand =
new
SqlCommand(singesql, sqlcon);
//计时开始
sp.Restart();
for
(
int
i = 0; i < time; i++)
{
sqlcommand.ExecuteNonQuery();
}
sp.Stop();
}
}
Console.WriteLine(
"方法1："
+ sp.ElapsedMilliseconds +
"毫秒"
);
//测试方法2
{
using
(SqlConnection sqlcon =
new
SqlConnection(
"Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"
))
{
sqlcon.Open();
string
singesql =
"INSERT INTO [student] ([name],[age])VALUES('abc',3);"
;
string
execsql =
""
;
for
(
int
i = 0; i < time; i++)
{
execsql = execsql + singesql;
}
SqlCommand sqlcommand =
new
SqlCommand(execsql, sqlcon);
//计时开始
sp.Restart();
sqlcommand.ExecuteNonQuery();
sp.Stop();
}
}
Console.WriteLine(
"方法2："
+ sp.ElapsedMilliseconds +
"毫秒"
);
//测试方法3
{
using
(SqlConnection sqlcon =
new
SqlConnection(
"Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"
))
{
sqlcon.Open();
SqlBulkCopy sqlc =
new
SqlBulkCopy(sqlcon);
DataTable dt =
new
DataTable();
dt.Columns.Add(
"id"
);
dt.Columns.Add(
"name"
);
dt.Columns.Add(
"age"
);
for
(
int
i = 0; i < time; i++)
{
dt.Rows.Add(38009,
"nemw"
, 123);
}
sqlc.DestinationTableName =
"student"
;
//计时开始
sp.Restart();
sqlc.WriteToServer(dt);
sp.Stop();
}
}
Console.WriteLine(
"方法3："
+ sp.ElapsedMilliseconds +
"毫秒"
);
Console.WriteLine();
}
}
}
插入N条数据的测试结果如下：
效率相差还是很夸张的，大家大量数据插入还是有sqlbulkcopy吧，原理还的高手告知~~
最近要做.net关于sql大量插入，找到了sqlbulkcopy(自己google下，应该很多说明了)这个好东西，于是测试下性能，用了三个方法对比：
1)直接用ado.net,for循环N次进行单条插入
2)把N条插入语句拼在一个sql，进行插入
3)直接使用sqlbulkcopy进行插入
代码如下：
|
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
|
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;
using
System.Data;
using
System.Data.SqlClient;
using
System.Diagnostics;
namespace
SQLTEST
{
class
Program
{
static
void
Main(
string
[] args)
{
//int time = 200;
test(20);
test(200);
test(2000);
test(10000);
test(50000);
Console.ReadLine();
}
public
static
void
test(
int
time){
Stopwatch sp =
new
Stopwatch();
Console.WriteLine(time +
"条数据插入测试"
);
//测试方法1
{
using
(SqlConnection sqlcon =
new
SqlConnection(
"Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"
))
{
sqlcon.Open();
string
singesql =
"INSERT INTO [student] ([name],[age])VALUES('abc',3);"
;
SqlCommand sqlcommand =
new
SqlCommand(singesql, sqlcon);
//计时开始
sp.Restart();
for
(
int
i = 0; i < time; i++)
{
sqlcommand.ExecuteNonQuery();
}
sp.Stop();
}
}
Console.WriteLine(
"方法1："
+ sp.ElapsedMilliseconds +
"毫秒"
);
//测试方法2
{
using
(SqlConnection sqlcon =
new
SqlConnection(
"Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"
))
{
sqlcon.Open();
string
singesql =
"INSERT INTO [student] ([name],[age])VALUES('abc',3);"
;
string
execsql =
""
;
for
(
int
i = 0; i < time; i++)
{
execsql = execsql + singesql;
}
SqlCommand sqlcommand =
new
SqlCommand(execsql, sqlcon);
//计时开始
sp.Restart();
sqlcommand.ExecuteNonQuery();
sp.Stop();
}
}
Console.WriteLine(
"方法2："
+ sp.ElapsedMilliseconds +
"毫秒"
);
//测试方法3
{
using
(SqlConnection sqlcon =
new
SqlConnection(
"Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"
))
{
sqlcon.Open();
SqlBulkCopy sqlc =
new
SqlBulkCopy(sqlcon);
DataTable dt =
new
DataTable();
dt.Columns.Add(
"id"
);
dt.Columns.Add(
"name"
);
dt.Columns.Add(
"age"
);
for
(
int
i = 0; i < time; i++)
{
dt.Rows.Add(38009,
"nemw"
, 123);
}
sqlc.DestinationTableName =
"student"
;
//计时开始
sp.Restart();
sqlc.WriteToServer(dt);
sp.Stop();
}
}
Console.WriteLine(
"方法3："
+ sp.ElapsedMilliseconds +
"毫秒"
);
Console.WriteLine();
}
}
}
|
插入N条数据的测试结果如下：
效率相差还是很夸张的，大家大量数据插入还是有sqlbulkcopy吧，原理还的高手告知~~
