SQL Server 和ORACLE 批量插入数据的两种方法
来源:互联网 发布:yunos应用中心软件 编辑:程序博客网 时间:2024/06/13 16:33
SQL Server 批量插入数据的两种方法
在SQL Server 中插入一条数据使用Insert语句,但是如果想要批量插入一堆数据的话,循环使用Insert不仅效率低,而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法:Bulk和表值参数(Table-Valued Parameters)。
运行下面的脚本,建立测试数据库和表值参数。
- --Create DataBase
- create database BulkTestDB;
- go
- use BulkTestDB;
- go
- --Create Table
- Create table BulkTestTable(
- Id int primary key,
- UserName nvarchar(32),
- Pwd varchar(16))
- go
- --Create Table Valued
- CREATE TYPE BulkUdt AS TABLE
- (Id int,
- UserName nvarchar(32),
- Pwd varchar(16))
下面我们使用最简单的Insert语句来插入100万条数据,代码如下:
- Stopwatch sw = new Stopwatch();
- SqlConnection sqlConn = new SqlConnection(
- ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);//连接数据库
- SqlCommand sqlComm = new SqlCommand();
- sqlComm.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//参数化SQL
- sqlComm.Parameters.Add("@p0", SqlDbType.Int);
- sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar);
- sqlComm.Parameters.Add("@p2", SqlDbType.VarChar);
- sqlComm.CommandType = CommandType.Text;
- sqlComm.Connection = sqlConn;
- sqlConn.Open();
- try
- {
- //循环插入100万条数据,每次插入10万条,插入10次。
- for (int multiply = 0; multiply < 10; multiply++)
- {
- for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
- {
- sqlComm.Parameters["@p0"].Value = count;
- sqlComm.Parameters["@p1"].Value = string.Format("User-{0}", count * multiply);
- sqlComm.Parameters["@p2"].Value = string.Format("Pwd-{0}", count * multiply);
- sw.Start();
- sqlComm.ExecuteNonQuery();
- sw.Stop();
- }
- //每插入10万条数据后,显示此次插入所用时间
- Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- sqlConn.Close();
- }
- Console.ReadLine();
耗时图如下:
由于运行过慢,才插入10万条就耗时72390 milliseconds,所以我就手动强行停止了。
下面看一下使用Bulk插入的情况:
bulk方法主要思想是通过在客户端把数据都缓存在Table中,然后利用SqlBulkCopy一次性把Table中的数据插入到数据库
代码如下:
- public static void BulkToDB(DataTable dt)
- {
- SqlConnection sqlConn = new SqlConnection(
- ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
- SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
- bulkCopy.DestinationTableName = "BulkTestTable";
- bulkCopy.BatchSize = dt.Rows.Count;
- try
- {
- sqlConn.Open();
- if (dt != null && dt.Rows.Count != 0)
- bulkCopy.WriteToServer(dt);
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- sqlConn.Close();
- if (bulkCopy != null)
- bulkCopy.Close();
- }
- }
- public static DataTable GetTableSchema()
- {
- DataTable dt = new DataTable();
- dt.Columns.AddRange(new DataColumn[]{
- new DataColumn("Id",typeof(int)),
- new DataColumn("UserName",typeof(string)),
- new DataColumn("Pwd",typeof(string))});
- return dt;
- }
- static void Main(string[] args)
- {
- Stopwatch sw = new Stopwatch();
- for (int multiply = 0; multiply < 10; multiply++)
- {
- DataTable dt = Bulk.GetTableSchema();
- for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
- {
- DataRow r = dt.NewRow();
- r[0] = count;
- r[1] = string.Format("User-{0}", count * multiply);
- r[2] = string.Format("Pwd-{0}", count * multiply);
- dt.Rows.Add(r);
- }
- sw.Start();
- Bulk.BulkToDB(dt);
- sw.Stop();
- Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
- }
- Console.ReadLine();
- }
耗时图如下:
可见,使用Bulk后,效率和性能明显上升。使用Insert插入10万数据耗时72390,而现在使用Bulk插入100万数据才耗时17583。
最后再看看使用表值参数的效率,会另你大为惊讶的。
表值参数是SQL Server 2008新特性,简称TVPs。对于表值参数不熟悉的朋友,可以参考最新的book online,我也会另外写一篇关于表值参数的博客,不过此次不对表值参数的概念做过多的介绍。言归正传,看代码:
- public static void TableValuedToDB(DataTable dt)
- {
- SqlConnection sqlConn = new SqlConnection(
- ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
- const string TSqlStatement =
- "insert into BulkTestTable (Id,UserName,Pwd)" +
- " SELECT nc.Id, nc.UserName,nc.Pwd" +
- " FROM @NewBulkTestTvp AS nc";
- SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);
- SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);
- catParam.SqlDbType = SqlDbType.Structured;
- //表值参数的名字叫BulkUdt,在上面的建立测试环境的SQL中有。
- catParam.TypeName = "dbo.BulkUdt";
- 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("Id",typeof(int)),
- new DataColumn("UserName",typeof(string)),
- new DataColumn("Pwd",typeof(string))});
- return dt;
- }
- static void Main(string[] args)
- {
- Stopwatch sw = new Stopwatch();
- for (int multiply = 0; multiply < 10; multiply++)
- {
- DataTable dt = TableValued.GetTableSchema();
- for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
- {
- DataRow r = dt.NewRow();
- r[0] = count;
- r[1] = string.Format("User-{0}", count * multiply);
- r[2] = string.Format("Pwd-{0}", count * multiply);
- dt.Rows.Add(r);
- }
- sw.Start();
- TableValued.TableValuedToDB(dt);
- sw.Stop();
- Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
- }
- Console.ReadLine();
- }
耗时图如下:
比Bulk还快5秒。
FROM:http://blog.csdn.net/tjvictor/archive/2009/07/18/4360030.aspx
PS:以上让Kevin yan本人想到了之前批量写入ORACLE数据库的情况,很怀怀疑SQL2008的TVPS是否就是跟它学来的呢?
以下为使用ODP.NET批量导入数据示例代码
/// <summary>
002
/// 批量插入数据
003
/// </summary>
004
/// <param name="tableName">表名称</param>
005
/// <param name="columnRowData">键-值存储的批量数据:键是列名称,值是该列对应的数据集合</param>
006
/// <returns></returns>
007
public
int
BatchInsert(
string
tableName, Dictionary<
string
,
object
[]> columnRowData)
008
{
009
if
(
string
.IsNullOrEmpty(tableName))
010
{
011
throw
new
ArgumentNullException(
"tableName"
,
"必须指定批量插入的表名称"
);
012
}
013
014
if
(columnRowData ==
null
|| columnRowData.Count < 1)
015
{
016
throw
new
ArgumentException(
"必须指定批量插入的字段名称"
,
"columnRowData"
);
017
}
018
019
int
iResult = 0;
020
string
[] dbColumns = columnRowData.Keys.ToArray();
021
StringBuilder sbCmdText =
new
StringBuilder();
022
if
(columnRowData.Count > 0)
023
{
024
// 准备插入SQL
025
sbCmdText.AppendFormat(
"INSERT INTO {1} ("
, tableName);
026
sbCmdText.Append(
string
.Join(
","
, dbColumns));
027
sbCmdText.Append(
") VALUES ("
);
028
sbCmdText.Append(
":"
+
string
.Join(
", :"
, dbColumns));
029
sbCmdText.Append(
") "
);
030
031
using
(OracleConnection conn =
new
OracleConnection(
"数据库连接字符信息"
))
032
{
033
using
(OracleCommand cmd = _oraDbConn.CreateCommand())
034
{
035
// 绑定批处理的行数
036
cmd.ArrayBindCount = columnRowData.Values.First().Length;
// 很重要
037
cmd.BindByName =
true
;
038
cmd.CommandType = CommandType.Text;
039
cmd.CommandText = sbCmdText.ToString();
040
cmd.CommandTimeout = 600;
// 10分钟
041
// 创建参数
042
OracleParameter oraParam;
043
List<IDbDataParameter> cacher =
new
List<IDbDataParameter>();
044
OracleDbType dbType = OracleDbType.Object;
045
foreach
(
string
colName
in
dbColumns)
046
{
047
dbType =
this
.GetOracleDbType(columnRowData[colName][0]);
048
oraParam =
new
OracleParameter(colName, dbType);
049
oraParam.Direction = ParameterDirection.Input;
050
oraParam.OracleDbTypeEx = dbType;
051
052
oraParam.Value = columnRowData[colName];
053
cmd.Parameters.Add(oraParam);
054
}
055
056
// 执行批处理
057
var trans = conn.BeginTransaction();
058
try
059
{
060
cmd.Transaction = trans;
061
iResult = cmd.ExecuteNonQuery();
062
trans.Commit();
063
}
064
catch
(Exception dbex)
065
{
066
trans.Rollback();
067
throw
dbex;
068
}
069
}
070
}
071
}
072
073
return
iResult;
074
}
075
076
077
/// 批量更新数据
078
/// </summary>
079
/// <param name="tableName">表名称</param>
080
/// <param name="keyColumName">主键列名称</param>
081
/// <param name="columnRowData">键-值存储的批量数据:键是列名称,值是该列对应的数据集合</param>
082
/// <returns></returns>
083
public
int
BatchUpdate(
string
tableName,
string
keyColumName, Dictionary<
string
,
object
[]> columnRowData)
084
{
085
if
(
string
.IsNullOrEmpty(tableName))
086
{
087
throw
new
ArgumentNullException(
"tableName"
,
"必须指定批量插入的表名称"
);
088
}
089
090
if
(
string
.IsNullOrEmpty(tableName))
091
{
092
throw
new
ArgumentNullException(
"keyColumName"
,
"必须指定批量插入表的主键列名称"
);
093
}
094
095
if
(columnRowData ==
null
|| columnRowData.Count < 1)
096
{
097
throw
new
ArgumentException(
"必须指定批量插入的字段名称"
,
"columnRowData"
);
098
}
099
100
int
iResult = 0;
101
string
[] dbColumns = columnRowData.Keys.ToArray();
102
StringBuilder sbCmdText =
new
StringBuilder();
103
if
(columnRowData.Count > 0)
104
{
105
// 准备更新SQL
106
sbCmdText.AppendFormat(
"update {0} set "
, tableName);
107
foreach
(var col
in
dbColumns)
108
{
109
if
(keyColumName.Equals(col,StringComparison.OrdinalIgnoreCase))
110
{
111
continue
;
112
}
113
sbCmdText.AppendFormat(
"{0} = :{0} ,"
, col);
114
}
115
sbCmdText.Remove(sbCmdText.Length - 1, 1);
116
sbCmdText.AppendFormat(
" where {0} = :{0}"
,keyColumName);
117
118
using
(OracleConnection conn =
new
OracleConnection(
"数据库连接字符信息"
))
119
{
120
using
(OracleCommand cmd = _oraDbConn.CreateCommand())
121
{
122
// 绑定批处理的行数
123
cmd.ArrayBindCount = columnRowData.Values.First().Length;
// 很重要
124
cmd.BindByName =
true
;
125
cmd.CommandType = CommandType.Text;
126
cmd.CommandText = sbCmdText.ToString();
127
cmd.CommandTimeout = 600;
// 10分钟
128
// 创建参数
129
OracleParameter oraParam;
130
List<IDbDataParameter> cacher =
new
List<IDbDataParameter>();
131
OracleDbType dbType = OracleDbType.Object;
132
foreach
(
string
colName
in
dbColumns)
133
{
134
dbType =
this
.GetOracleDbType(columnRowData[colName][0]);
135
oraParam =
new
OracleParameter(colName, dbType);
136
oraParam.Direction = ParameterDirection.Input;
137
oraParam.OracleDbTypeEx = dbType;
138
139
oraParam.Value = columnRowData[colName];
140
cmd.Parameters.Add(oraParam);
141
}
142
143
// 执行批处理
144
var trans = conn.BeginTransaction();
145
try
146
{
147
cmd.Transaction = trans;
148
iResult = cmd.ExecuteNonQuery();
149
trans.Commit();
150
}
151
catch
(Exception dbex)
152
{
153
trans.Rollback();
154
throw
dbex;
155
}
156
}
157
}
158
}
159
160
return
iResult;
161
}
162
163
164
/// <summary>
165
/// 根据数据类型获取OracleDbType
166
/// </summary>
167
/// <param name="value"></param>
168
/// <returns></returns>
169
private
OracleDbType GetOracleDbType(
object
value)
170
{
171
OracleDbType dataType = OracleDbType.Object;
172
if
(value
is
string
)
173
{
174
dataType = OracleDbType.Varchar2;
175
}
176
else
if
(value
is
DateTime)
177
{
178
dataType = OracleDbType.TimeStamp;
179
}
180
else
if
(value
is
int
|| value
is
short
)
181
{
182
dataType = OracleDbType.Int32;
183
}
184
else
if
(value
is
long
)
185
{
186
dataType = OracleDbType.Int64;
187
}
188
else
if
(value
is
decimal
|| value
is
double
)
189
{
190
dataType = OracleDbType.Decimal;
191
}
192
else
if
(value
is
Guid)
193
{
194
dataType = OracleDbType.Varchar2;
195
}
196
else
if
(value
is
bool
|| value
is
Boolean)
197
{
198
dataType = OracleDbType.Byte;
199
}
200
else
if
(value
is
byte
[])
201
{
202
dataType = OracleDbType.Blob;
203
}
204
else
if
(value
is
char
)
205
{
206
dataType = OracleDbType.Char;
207
}
208
209
return
dataType;
210
}
- SQL Server 和ORACLE 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- [SQL Server]SQLServer 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- 怎样使一个Android应用不被杀死?
- pig基础实例运算
- 问题 error
- POJ2159Ancient Cipher
- C++资料
- SQL Server 和ORACLE 批量插入数据的两种方法
- java中File类的相关学习
- 在windows下面使用ls命令
- 杜文涛的专栏
- 关于循环语句的一些问题
- Mex
- 红黑树并没有我们想象的那么难
- 对于java.lang.NoSuchMethodError: antlr.collections.AST.getLine()I错误解决
- cocos2d-x-2.1.5 没有InstallWizardForVS2012/2010/2008.js