方法一
DataTable dt = silkPlanDataset.MT_PL_ZS_TASK_DEL.GetChanges(DataRowState.Deleted);
if (dt != null)
{
//dt.RejectChanges();
DateTime dtime1 = DateTime.Now;
DataColumn[] keys = new DataColumn[1];
keys[0] = dt.Columns["ZS_TASK_DEL_ID"];
dt.PrimaryKey = keys; //指定主键
tran = conn.BeginTransaction();
sql = "delete from MT_PL_ZS_TASK_DEL where ZS_TASK_DEL_ID =:ZS_TASK_DEL_ID";
command = db.GetSqlStringCommand(sql);
db.AddInParameter(command, "ZS_TASK_DEL_ID", DbType.String, "ZS_TASK_DEL_ID", DataRowVersion.Current);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
db.UpdateDataSet(ds, "MT_PL_ZS_TASK_DEL", null, null, command, tran);
tran.Commit();
DateTime dtime2 = DateTime.Now;
TimeSpan tp = dtime2 - dtime1;
}
方法二
DataTable dt = silkPlanDataset.MT_PL_ZS_TASK_DEL.GetChanges(DataRowState.Deleted);
if (dt != null)
{
//dt.RejectChanges();
DateTime dtime1 = DateTime.Now;
DataColumn[] keys = new DataColumn[1];
keys[0] = dt.Columns["ZS_TASK_DEL_ID"];
dt.PrimaryKey = keys; //指定主键
tran = conn.BeginTransaction();
sql = "delete from MT_PL_ZS_TASK_DEL where rowid =(select rowid from MT_PL_ZS_TASK_DEL where ZS_TASK_DEL_ID = :ZS_TASK_DEL_ID)";
command = db.GetSqlStringCommand(sql);
db.AddInParameter(command, "ZS_TASK_DEL_ID", DbType.String, "ZS_TASK_DEL_ID", DataRowVersion.Current);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
db.UpdateDataSet(ds, "MT_PL_ZS_TASK_DEL", null, null, command, tran);
tran.Commit();
DateTime dtime2 = DateTime.Now;
TimeSpan tp = dtime2 - dtime1;
}采用企业库删除的时候,一次删除200条记录,方法二效率更高,为什么?
方法二是先查找了rowid 然后再删除.
DataTable dt = silkPlanDataset.MT_PL_ZS_TASK_DEL.GetChanges(DataRowState.Deleted);
if (dt != null)
{
//dt.RejectChanges();
DateTime dtime1 = DateTime.Now;
DataColumn[] keys = new DataColumn[1];
keys[0] = dt.Columns["ZS_TASK_DEL_ID"];
dt.PrimaryKey = keys; //指定主键
tran = conn.BeginTransaction();
sql = "delete from MT_PL_ZS_TASK_DEL where ZS_TASK_DEL_ID =:ZS_TASK_DEL_ID";
command = db.GetSqlStringCommand(sql);
db.AddInParameter(command, "ZS_TASK_DEL_ID", DbType.String, "ZS_TASK_DEL_ID", DataRowVersion.Current);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
db.UpdateDataSet(ds, "MT_PL_ZS_TASK_DEL", null, null, command, tran);
tran.Commit();
DateTime dtime2 = DateTime.Now;
TimeSpan tp = dtime2 - dtime1;
}
方法二
DataTable dt = silkPlanDataset.MT_PL_ZS_TASK_DEL.GetChanges(DataRowState.Deleted);
if (dt != null)
{
//dt.RejectChanges();
DateTime dtime1 = DateTime.Now;
DataColumn[] keys = new DataColumn[1];
keys[0] = dt.Columns["ZS_TASK_DEL_ID"];
dt.PrimaryKey = keys; //指定主键
tran = conn.BeginTransaction();
sql = "delete from MT_PL_ZS_TASK_DEL where rowid =(select rowid from MT_PL_ZS_TASK_DEL where ZS_TASK_DEL_ID = :ZS_TASK_DEL_ID)";
command = db.GetSqlStringCommand(sql);
db.AddInParameter(command, "ZS_TASK_DEL_ID", DbType.String, "ZS_TASK_DEL_ID", DataRowVersion.Current);
DataSet ds = new DataSet();
ds.Tables.Add(dt);
db.UpdateDataSet(ds, "MT_PL_ZS_TASK_DEL", null, null, command, tran);
tran.Commit();
DateTime dtime2 = DateTime.Now;
TimeSpan tp = dtime2 - dtime1;
}采用企业库删除的时候,一次删除200条记录,方法二效率更高,为什么?
方法二是先查找了rowid 然后再删除.
解决方案 »
- oracle里的merge方法
- Oracle链接出错 【有能力解决的来看看】
- 硬盘列盘,Oracle数据文件怎么冷恢复出来
- 建了一张时间表time,有年、年描述、月、月描述、日、日描述等,怎么将系统时间初始化进去,比如说20100101到20121231这段时间,用pl/sql实现。
- oracle10G 的 data guard 图形办面的配制
- 请问如何删除表的重复记录
- 如何恢复已设置成未使用的列
- 初学者提问
- 又来提问了,还是关于SQL的问题,高分相送!!!!!!!!!!
- linux7.3安装Oracle10客户端时,系统提示ERROR in writing to directory /tmp/OraInstll.....
- oracle imp 命令报错
- 数据库迁移
第1种做法因为WHERE子句中存储参数:ZS_TASK_DEL_ID,对于表的每条记录进行判断时都要进行一次参数绑定。
而第2种做法确只需要绑定一次。
方法1 200条记录 需要23秒
方法2 200条记录 需要17秒
方法3 自己组织删除语句,逐条删除,一次提交只要 0.5秒