一次性插入10万条数据,怎么提高效率 结帖率:0.00% OMG~~~~把数据直接添加到datatable了,一次插入。这就最简单了。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 这10万条记录从哪来的,如果能直接通过SQL语句在数据库层面处理是最好的了,毕竟应用程序和数据库沟通也是要时间的。 请用datatable写个例子给我看下 10万条很少,不需要考虑什么问题,直接SQL插就是了不过最快的方式就是从文件导入,要、数据排序要按照主键的顺序正序排列 /// <summary> /// SqlDataReader批量添加(没有事务) /// </summary> /// <param name="Reader">数据源</param> /// <param name="Mapping">定义数据源和目标源列的关系集合</param> /// <param name="DestinationTableName">目标表</param> public static void MySqlBulkCopy(SqlDataReader Reader, SqlBulkCopyColumnMapping[] Mapping, string DestinationTableName) { using(SqlBulkCopy copy=new SqlBulkCopy(ConnectionString)) { copy.DestinationTableName = DestinationTableName;//设置要添加的表名 if(Mapping!=null) { //如果有匹配 foreach (SqlBulkCopyColumnMapping Mapp in Mapping) { copy.ColumnMappings.Add(Mapp); } } try { copy.WriteToServer(Reader);//批量添加 } finally { Reader.Close();//关闭 } } } #endregion这个模式是没有事务的,事务的,你查一下MSDN DateTime begin = DateTime.Now; string connectionString = ""; using(SqlConnection conn = new SqlConnection(connectionString)){ conn.Open(); SqlDataAdapter sd = new SqlDataAdapter(); sd.SelectCommand = new SqlCommand("select top 200 devid,data_time,data_value from CurrentTest", conn); DataSet dataset = new DataSet(); sd.Fill(dataset); Random r = new Random(1000); sd.UpdateCommand = new SqlCommand("update CurrentTest " + " set data_time = @data_time,data_value = @data_value where devid = @devid", conn); sd.UpdateCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time"); sd.UpdateCommand.Parameters.Add("@data_value", SqlDbType.Int, 4, "data_value"); sd.UpdateCommand.Parameters.Add("@devid", SqlDbType.Char, 20, "devid"); sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; sd.UpdateBatchSize = 0; for (int count = 0; count < 100000;) { for (int i = 0; i < 200; i++,count++) { dataset.Tables[0].Rows[i].BeginEdit(); dataset.Tables[0].Rows[i]["data_time"] = "2222-22-22 22:22:22"; dataset.Tables[0].Rows[i]["data_value"] = 100; dataset.Tables[0].Rows[i]["devid"] = "DEVID"+count; dataset.Tables[0].Rows[i].EndEdit(); } sd.Update(dataset.Tables[0]); } dataset.Tables[0].Clear(); sd.Dispose(); dataset.Dispose(); conn.Close(); } TimeSpan ts = DateTime.Now - begin; MessageBox.Show("ts = " + ts.TotalMilliseconds); SqlBulkCopy 看这名Copy,微软定义它,就是用来转移数据的 这种情况,我也没遇过。一直SQL 将要插入的SQL语句保存到数据组,通过事务再插入。比比一条一条快点,不知道是不是最好的方法。 DBOpen(); SqlCommand DataCom = new SqlCommand(); SqlDataAdapter DataAD = new SqlDataAdapter(); SqlTransaction Trans = null; Trans = Conn.BeginTransaction(); DataCom.Connection = Conn; DataCom.Transaction = Trans; try { DataCom.CommandText = Sql; DataAD.SelectCommand = DataCom; SqlCommandBuilder DataComBu = new SqlCommandBuilder(DataAD); DataAD.Update(Tables); Trans.Commit(); } catch { Trans.Rollback(); } finally { DBClose(); DataCom.Dispose(); Trans.Dispose(); } sql可以是空的数据结构,像:select * from ab where 1=2主要是说导入到AB表对应的列DATASET是你原先10W条数据,列名与SQL中的列要相同你试一下效率 sqlBulkCopy 单表,大数据量批量插入,用它应该最合适了。如果你的应用程序对数据的及时反馈要求很高,那就无能为力了。 1.MSSQL使用SqlBulkCopy,传数据源,表名,列影射,我导入150万几秒完成 /// <summary> /// 使用SqlBulkCopy批量插入,只限SQLServer /// 缺点,没有返回行数 /// </summary> /// <param name="table">填充的DataTable,支持其它数据源,请看重载</param> /// <param name="tableName">数据库对应表名</param> /// <param name="columns">插入表对应的列名集合</param> public void SqlBulkCopyInsert(DataTable table, string tableName, string[] columns) { SqlBulkCopy sbc = new SqlBulkCopy("接连字符串"); sbc.DestinationTableName = tableName; foreach (string col in columns) { sbc.ColumnMappings.Add(col, col); } sbc.WriteToServer(table); }2.其它数据库,将数据查到一个datatable,往table填充数据,再在adpt.Update(table) /// <summary> /// 多行插入,Connection/Command/DataAdapter看你连接的数据库类型 /// 进行相应的替换即可 /// </summary> /// <param name="ds">填充数据后的数据集</param> /// <returns>受影响行数</returns> public int MultyInsert(DataSet ds) { int result = 0; IDbConnection con = new OracleConnection("连接字符串"); con.Open(); IDbCommand cmd = new OracleCommand(); cmd.CommandText = "Insert into Member(UserName,Password) values(@name,@password)"; IDbDataParameter namePar = cmd.CreateParameter(); namePar.ParameterName = "@name"; namePar.SourceColumn = "UserName"; namePar.SourceVersion = DataRowVersion.Original; namePar.DbType = DbType.String; cmd.Parameters.Add(namePar); IDbDataParameter passPar = cmd.CreateParameter(); passPar.ParameterName = "@pass"; passPar.DbType = DbType.String; passPar.SourceColumn = "Password"; passPar.SourceVersion = DataRowVersion.Original; cmd.Parameters.Add(passPar); IDbDataAdapter adpt = new OracleDataAdapter(); adpt.InsertCommand = cmd; try { result = adpt.Update(ds); } catch (Exception) { throw; } finally { con.Close(); } return result; } 忘了加个cmd.Connection=con了= = /// <summary> /// DataTable批量添加(有事务) /// </summary> /// <param name="Table">数据源</param> /// <param name="Mapping">定义数据源和目标源列的关系集合</param> /// <param name="DestinationTableName">目标表</param> public static bool MySqlBulkCopy(DataTable Table, SqlBulkCopyColumnMapping[] Mapping, string DestinationTableName) { bool Bool = true; using (SqlConnection con = new SqlConnection(ConnectionString)) { con.Open(); using (SqlTransaction Tran = con.BeginTransaction()) { using (SqlBulkCopy Copy = new SqlBulkCopy(con,SqlBulkCopyOptions.KeepIdentity,Tran)) { Copy.DestinationTableName = DestinationTableName;//指定目标表 if (Mapping != null) { //如果有数据 foreach (SqlBulkCopyColumnMapping Map in Mapping) { Copy.ColumnMappings.Add(Map); } } try { Copy.WriteToServer(Table);//批量添加 Tran.Commit();//提交事务 } catch { Tran.Rollback();//回滚事务 Bool = false; } } } } return Bool; }给你一个有事务的吧 1.首先读入datatable:tb12.打开数据库datatable:Tb23.Tb2.merge(tb1) 没用程序试过,手动导入到oracle试过,数据存放到excel再导进oracle中。 sql server 2008的 SSIS 技术,你百度一下; 或者 上面很多人说的 SqlBulkCopy ; 如果不想用程序的话,可以先把数据粘贴到Excel里面,再导入到数据库中,这个又快,又省事。 老大,你用的什么数据库啊!使用程序导入数据不知道,但是oracle好像有个方法,不记录日志的导入数据。可以写个存储过程,按分区检索数据,不记录数据库日志的写入目的表中,这样按照分区分成几个部分导入,速度会很快。如果是SQL SERVER,就使用“ylchenjian”的方法吧。 动态sql···一条sql语句····关闭数据库的日志oracle 5秒搞定 /// <summary> /// 使用SqlBulkCopy方式插入数据 /// </summary> /// <param name="dataTable"></param> /// <returns></returns> private static long SqlBulkCopyInsert() { Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); DataTable dataTable = GetTableSchema();//复制表结构 string passportKey; for (int i = 0; i < count; i++) { passportKey = Guid.NewGuid().ToString(); DataRow dataRow = dataTable.NewRow(); dataRow[0] = passportKey; dataTable.Rows.Add(dataRow); } //var sss = dataTable.Rows[99].ItemArray; SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString); sqlBulkCopy.DestinationTableName = "Passport"; sqlBulkCopy.BatchSize = dataTable.Rows.Count; sqlBulkCopy.BulkCopyTimeout = 200;//超时200秒(默认30秒) SqlConnection sqlConnection = new SqlConnection(connectionString); sqlConnection.Open(); if (dataTable!=null && dataTable.Rows.Count!=0) { sqlBulkCopy.WriteToServer(dataTable); } sqlBulkCopy.Close(); sqlConnection.Close(); stopwatch.Stop(); return stopwatch.ElapsedMilliseconds; } private static DataTable GetTableSchema() { DataTable dataTable = new DataTable(); dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("PassportKey") }); return dataTable; } 有两种方式处理:1、使用数据库的工具进行文本倒入2、使用SQL做批量插入,插入时考虑暂时关闭日志 用TXET方式导入,数据写到TXET,然后导入,10万条,一秒就够了。缺点:触发器不会被触发 最好用批处理,直接loaddata这个最快. 请问一个gridview 控件更新时的问题。。 asp.net下载文件,文件打开内容乱码 asp.net页面传值 在哪设置vs2008使用framework 2.0进行开发啊? 刚用asp.net2.0做的百度MP3小偷程序提供下载 怎么样datagrid有滚动条 问个简单的DataGrid的问题 flash显示问题 请教一个文章发布系统中无限级分类的问题 我想点击树某个节点就激发一个事件,如何做??? 发个贴。问下<%=%>和<%#%>分别代表什么意思。 一次性插入10万条数据,怎么提高效率
这10万条记录从哪来的,如果能直接通过SQL语句在数据库层面处理是最好的了,毕竟应用程序和数据库沟通也是要时间的。
不过最快的方式就是从文件导入,要、数据排序要按照主键的顺序正序排列
/// <summary>
/// SqlDataReader批量添加(没有事务)
/// </summary>
/// <param name="Reader">数据源</param>
/// <param name="Mapping">定义数据源和目标源列的关系集合</param>
/// <param name="DestinationTableName">目标表</param>
public static void MySqlBulkCopy(SqlDataReader Reader, SqlBulkCopyColumnMapping[] Mapping, string DestinationTableName)
{
using(SqlBulkCopy copy=new SqlBulkCopy(ConnectionString))
{
copy.DestinationTableName = DestinationTableName;//设置要添加的表名
if(Mapping!=null)
{
//如果有匹配
foreach (SqlBulkCopyColumnMapping Mapp in Mapping)
{
copy.ColumnMappings.Add(Mapp);
}
}
try
{
copy.WriteToServer(Reader);//批量添加
}
finally
{
Reader.Close();//关闭
}
}
}
#endregion
这个模式是没有事务的,事务的,你查一下MSDN
string connectionString = "";
using(SqlConnection conn = new SqlConnection(connectionString)){
conn.Open(); SqlDataAdapter sd = new SqlDataAdapter();
sd.SelectCommand = new SqlCommand("select top 200 devid,data_time,data_value from CurrentTest", conn); DataSet dataset = new DataSet();
sd.Fill(dataset);
Random r = new Random(1000); sd.UpdateCommand = new SqlCommand("update CurrentTest "
+ " set data_time = @data_time,data_value = @data_value where devid = @devid", conn);
sd.UpdateCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
sd.UpdateCommand.Parameters.Add("@data_value", SqlDbType.Int, 4, "data_value");
sd.UpdateCommand.Parameters.Add("@devid", SqlDbType.Char, 20, "devid");
sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
sd.UpdateBatchSize = 0;
for (int count = 0; count < 100000;)
{ for (int i = 0; i < 200; i++,count++)
{
dataset.Tables[0].Rows[i].BeginEdit();
dataset.Tables[0].Rows[i]["data_time"] = "2222-22-22 22:22:22";
dataset.Tables[0].Rows[i]["data_value"] = 100;
dataset.Tables[0].Rows[i]["devid"] = "DEVID"+count;
dataset.Tables[0].Rows[i].EndEdit();
}
sd.Update(dataset.Tables[0]);
}
dataset.Tables[0].Clear();
sd.Dispose();
dataset.Dispose();
conn.Close();
}
TimeSpan ts = DateTime.Now - begin;
MessageBox.Show("ts = " + ts.TotalMilliseconds);
这种情况,我也没遇过。一直SQL
SqlCommand DataCom = new SqlCommand();
SqlDataAdapter DataAD = new SqlDataAdapter();
SqlTransaction Trans = null;
Trans = Conn.BeginTransaction();
DataCom.Connection = Conn;
DataCom.Transaction = Trans;
try
{
DataCom.CommandText = Sql;
DataAD.SelectCommand = DataCom;
SqlCommandBuilder DataComBu = new SqlCommandBuilder(DataAD);
DataAD.Update(Tables);
Trans.Commit();
}
catch
{
Trans.Rollback();
}
finally
{
DBClose();
DataCom.Dispose();
Trans.Dispose();
}
主要是说导入到AB表对应的列DATASET是你原先10W条数据,列名与SQL中的列要相同你试一下效率
/// <summary>
/// 使用SqlBulkCopy批量插入,只限SQLServer
/// 缺点,没有返回行数
/// </summary>
/// <param name="table">填充的DataTable,支持其它数据源,请看重载</param>
/// <param name="tableName">数据库对应表名</param>
/// <param name="columns">插入表对应的列名集合</param>
public void SqlBulkCopyInsert(DataTable table, string tableName, string[] columns)
{
SqlBulkCopy sbc = new SqlBulkCopy("接连字符串");
sbc.DestinationTableName = tableName;
foreach (string col in columns)
{
sbc.ColumnMappings.Add(col, col);
}
sbc.WriteToServer(table);
}2.其它数据库,将数据查到一个datatable,往table填充数据,再在adpt.Update(table)
/// <summary>
/// 多行插入,Connection/Command/DataAdapter看你连接的数据库类型
/// 进行相应的替换即可
/// </summary>
/// <param name="ds">填充数据后的数据集</param>
/// <returns>受影响行数</returns>
public int MultyInsert(DataSet ds)
{
int result = 0;
IDbConnection con = new OracleConnection("连接字符串");
con.Open();
IDbCommand cmd = new OracleCommand();
cmd.CommandText = "Insert into Member(UserName,Password) values(@name,@password)";
IDbDataParameter namePar = cmd.CreateParameter();
namePar.ParameterName = "@name";
namePar.SourceColumn = "UserName";
namePar.SourceVersion = DataRowVersion.Original;
namePar.DbType = DbType.String;
cmd.Parameters.Add(namePar);
IDbDataParameter passPar = cmd.CreateParameter();
passPar.ParameterName = "@pass";
passPar.DbType = DbType.String;
passPar.SourceColumn = "Password";
passPar.SourceVersion = DataRowVersion.Original;
cmd.Parameters.Add(passPar);
IDbDataAdapter adpt = new OracleDataAdapter();
adpt.InsertCommand = cmd;
try
{
result = adpt.Update(ds);
}
catch (Exception)
{ throw;
}
finally
{
con.Close();
}
return result;
}
/// <summary>
/// DataTable批量添加(有事务)
/// </summary>
/// <param name="Table">数据源</param>
/// <param name="Mapping">定义数据源和目标源列的关系集合</param>
/// <param name="DestinationTableName">目标表</param>
public static bool MySqlBulkCopy(DataTable Table, SqlBulkCopyColumnMapping[] Mapping, string DestinationTableName)
{
bool Bool = true;
using (SqlConnection con = new SqlConnection(ConnectionString))
{
con.Open();
using (SqlTransaction Tran = con.BeginTransaction())
{
using (SqlBulkCopy Copy = new SqlBulkCopy(con,SqlBulkCopyOptions.KeepIdentity,Tran))
{
Copy.DestinationTableName = DestinationTableName;//指定目标表
if (Mapping != null)
{
//如果有数据
foreach (SqlBulkCopyColumnMapping Map in Mapping)
{
Copy.ColumnMappings.Add(Map);
}
}
try
{
Copy.WriteToServer(Table);//批量添加
Tran.Commit();//提交事务
}
catch
{
Tran.Rollback();//回滚事务
Bool = false;
}
}
}
}
return Bool;
}
给你一个有事务的吧
2.打开数据库datatable:Tb2
3.Tb2.merge(tb1)
或者 上面很多人说的 SqlBulkCopy ;
oracle 5秒搞定
/// 使用SqlBulkCopy方式插入数据
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private static long SqlBulkCopyInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start(); DataTable dataTable = GetTableSchema();//复制表结构
string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[0] = passportKey;
dataTable.Rows.Add(dataRow);
}
//var sss = dataTable.Rows[99].ItemArray;
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
sqlBulkCopy.DestinationTableName = "Passport";
sqlBulkCopy.BatchSize = dataTable.Rows.Count;
sqlBulkCopy.BulkCopyTimeout = 200;//超时200秒(默认30秒)
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
if (dataTable!=null && dataTable.Rows.Count!=0)
{
sqlBulkCopy.WriteToServer(dataTable);
}
sqlBulkCopy.Close();
sqlConnection.Close(); stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
} private static DataTable GetTableSchema()
{
DataTable dataTable = new DataTable();
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("PassportKey") });
return dataTable;
}
1、使用数据库的工具进行文本倒入
2、使用SQL做批量插入,插入时考虑暂时关闭日志
缺点:触发器不会被触发