1.5W条记录,按条件Update和Insert,做了2分钟,正常吗

Question

1.5W条记录,按条件Update和Insert,做了2分钟,正常吗.
2.0GHZ双核的,放在事务里了using (SqlServerDataAccess dataAccess = SqlServerDataAccess.DBConnect())
            {                dataAccess.Open();
                dataAccess.BeginTransaction();                try
                {
                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        int a=0;
                        for (int j=0;j<dt2.Rows.Count;j++)
                        {
                            if (dt1.Rows [i][0].ToString ()==dt2.Rows [j][0].ToString ())
                            {
                                a=1;
                            }
                        }
                        if (a==1)
                        {
                            a = 0;
                            string sqlCommstr = " update tPart_Master set vcType = '" + dt1.Rows[i][3].ToString() + "',dUpdataTime_Kind = '" + dt1.Rows[i][4].ToString() + "',vcUpdataUser_Kind = '" + dt1.Rows[i][5].ToString() + "' where  vcPartsNo = '" + dt1.Rows[i][0].ToString() + "' ";
                            dataAccess.ExcuteNoQuery(sqlCommstr);
                        }
                        else
                        {
                            string sqlCommstr = " insert into tPart_Master (vcPartsNo,dTimeFrom,dTimeTo,vcCpdCompany,vcType,dUpdataTime_Kind,vcUpdataUser_Kind) values ('" + dt1.Rows[i][0].ToString() + "','" + dt1.Rows[i][1].ToString() + "','" + dt1.Rows[i][2].ToString() + "','" + dt1.Rows[i][3].ToString() + "','','" + dt1.Rows[i][4].ToString() + "','" + dt1.Rows[i][5].ToString() + "')";
                            dataAccess.ExcuteNoQuery(sqlCommstr);
                        }
                    }                    dataAccess.Commit();
                    return "导入成功！";
                }
                catch (Exception ex)
                {
                    dataAccess.Rollback();                    throw ex;
                }
            }

Answer 1

很不正常 我MYSQL在存储过程里面插入100万条记录都没用那么长时间

Answer 2

楼主可以写个批量插入批量跟新，组一次dataAccess.ExcuteNoQuery（）就可以了

Answer 3

是从EXCEL获取数据,再判断,根据内容,做更新或者插入

Answer 4

可以全部用字符串拼好所有的insert语句再执行命名不？

Answer 5

這樣寫試試，應該會快一點。
stringbuild sb=new stringbuild();
for(int i=0;i<15000;i++)
{
     sb.append(" Insert into db select @p1,@p2 union all");
}
string str=sb.ToString().TrimEndWith("union all");
dataAccess.ExcuteNoQuery(str);

Answer 6

  //批量插入数据
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = DBUtility.SQLHelper.connectionString;
                conn.Open();
                SqlTransaction sqlbulkTransaction = conn.BeginTransaction();
                SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);
                copy.DestinationTableName = "sys_employee";//数据库中的表名                foreach (DataColumn dc in dtexcel.Columns)
                {
                    copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                }
                try
                {
                    copy.WriteToServer(dtexcel);
                    sqlbulkTransaction.Commit();                }
                catch (Exception ex)
                {
                    sqlbulkTransaction.Rollback();
                    Console.WriteLine(ex.ToString());                }
                finally
                {
                    copy.Close();
                    conn.Close();                }

Answer 7

这1.5W多数据有1.5W+是Update,只有那么几条是插入的

Answer 8

用这个 SqlBulkCopy  速度快

Answer 9

SqlBulkCopy 只能插入数据，无法修改数据。

Answer 10

放到一起插入和更新会快点.另外string类型的最好用StringBuilder代替吧

Answer 11

也许换个好点的CPU更好 哈哈 就这样了 不管了

Answer 12

ALTER proc [dbo].[PK_FS0100InsertParts_MasterMain] 
(
@vcPartsNo varchar(12),
@dTimeFrom varchar(20),
@dTimeTo varchar(20),
@vcType varchar(2),
@vcimportDate varchar(20),
@vcimportUser varchar(6)
)
as
if ((select count(*) from dbo.tPart_Master where vcPartsNo = @vcPartsNo ) > 0)
begin
Update dbo.tPart_Master Set vcPartsNo=@vcPartsNo,vcType=@vcType,dUpdataTime_Kind=@vcimportDate,vcUpdataUser_Kind=@vcimportUser
Where vcPartsNo = @vcPartsNo
end
else
insert tPart_Master(vcPartsNo,dTimeFrom,dTimeTo,vcType,vcCpdCompany,dUpdataTime_Kind,vcUpdataUser_Kind)
Values( @vcPartsNo,@dTimeFrom,@dTimeTo,@vcType,'',@vcimportDate,@vcimportUser)

Answer 13

灰常不正常update 30W数据 30s