批量插入 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吧，原理还的高手告知~~