SQLServer使用表值参数，高性能批量插入数据
2024-03-22 15:45:24
记得前段时间帮同事写了个解析账号并入库的小工具，来批量导入账号信息，账号量相当大，程序每读取一条记录便执行一次insert来插入数据，整整跑了一下午才把账号全部入库。
今天又接到同事类似的需求，不过这次的账号量更大，考虑到上次遇到的问题，这次打算采用某种方案来提高插入数据的性能。
了解了下SQLServer批量插入数据的技术，主要有两种：Bulk和表值参数(SQLServer 2008的特性)，这两种方式相比循环使用insert插入数据，效率和性能明显上升。使用表值参数带来的提升更为显著。
使用表值参数插入数据的一个例子
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
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Collections;
using NPOI.HSSF.UserModel;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace Account2DB
{
class Program
{
public static void TableValuedToDB(DataTable dt)
{
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
const string TSqlStatement =
"insert into NewAccount (Account,Ting,Jie)" +
" SELECT nc.Account, nc.Ting,nc.Jie" +
" FROM @BulkTvp AS nc";
SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);
SqlParameter catParam = cmd.Parameters.AddWithValue("@BulkTvp", dt);
catParam.SqlDbType = SqlDbType.Structured;
//表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。
catParam.TypeName = "dbo.BulkAccount";
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("Account",typeof(string)),
new DataColumn("Ting",typeof(DateTime)),
new DataColumn("Jie",typeof(DateTime))});
return dt;
}
static void Main(string[] args)
{
Logger.Info("正在插入数据，请稍后...");
try
{
StreamReader sr = new StreamReader(args[0], Encoding.Default);
StringBuilder error = new StringBuilder();
DataTable dt = GetTableSchema();
int count = 0;
while (!sr.EndOfStream)
{
string acc = sr.ReadLine();
if (acc.StartsWith("账号") || acc.StartsWith("---") || String.IsNullOrEmpty(acc))
{
continue;
}
var data = acc.Split(',');
if (data.Count() != 3)
{
Logger.Error("错误数据：" + acc);
error.AppendLine(acc);
}
DataRow r = dt.NewRow();
r[0] = data[0].Trim();
r[1] = Convert.ToDateTime(data[1]);
r[2] = Convert.ToDateTime(data[2]);
dt.Rows.Add(r);
count++;
// 每一万个账号作为一批账号插入
if (count % 10000 == 0)
{
TableValuedToDB(dt);
Logger.Info("已插入：" + count);
dt.Clear();
}
}
// 插入剩余账号
TableValuedToDB(dt);
Logger.Info("已插入：" + count);
sr.Close();
File.AppendAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "error.txt"), error.ToString(), Encoding.Default);
}
catch (Exception ex)
{
Logger.Error(ex.ToString());
}
Logger.Info("执行结束，按任意键关闭...");
Console.ReadLine();
}
}
}
最新文章
- 【转】Android 底层开发的几点
- jQuery实现的表格展开伸缩效果实例
- 如何关闭emacs开启时自己打开的欢迎界面
- FLEXNET License管理
- 【CSS】使用CSS选择器（第二部分）
- VFS分析(二)基本数据结构(持续更新)
- sql行列转换
- 【转】Installing the libv8 Ruby gem on Centos 5.8
- [转载][HTML] 普通的DIV分层以及版透明效果
- HDU 2516 取石子游戏 斐波纳契博弈
- 【转】ActionScript，Flash，Flash/Flex Builder，FlashPlayer，AIR，swf，swc，swz之间的区别
- lesson - 5 Linux用户和组管理
- DeepLearning.ai学习笔记（四）卷积神经网络 -- week3 目标检测
- #定义一个方法get_num(num),num参数是列表类型，判断列表里面的元素为数字类型。其他类型则报错，并且返回一个偶数列表：（注：列表里面的元素为偶数）。
- Android SDK 开发——发布使用踩坑之路
- 【c#】RabbitMQ学习文档（二）Work Queues（工作队列）
- jpa/hibernate @onetomany 使用left join 添加多条件,可以使用过滤器filters (with-clause not allowed on fetched associations; use filters异常信息)
- java.lang.IllegalStateException: getWriter() has already been called for this response
- ionic3开发ios端
- （转）JVM——内存管理和垃圾回收