大批量数据更新,分享给大家。

wnttmk 2014-05-09 02:16:52

今天要做一个大批量的数据分析，在表数据中要加入一些字段。并且通过程序分析得出有效值后存入数据库中。以方便接下来的操作。
好吧，数据量也不是很大。只有55W条。通过数据导出将他从服务器上导出单表数据后。存入本地的数据库中。所以，所有的字段都设置为Varchar类型，没有主键。
老老实实的写方法，首先通过ADO.NET中的SQLCommand来执行吧。也就是说一条数据执行一条。每次从数据库中读取100条。分析完后执行SQL语句。

new SqlCommand("Update order_db Set Sex = @Sex, Age = @Age where Ord_No = @Ord_No", conn).ExecuteNonQuery();

平均速度为1秒<10条。纠结！~~55W条数据啊！~~

好吧，另寻方法吧！这次使用ADO.NET中的SqlDataAdapter进行批量数据更新吧。同样，每次从数据库中取100条。批量进行数据更新

 SqlDataAdapter sda = new SqlDataAdapter();

            sda.SelectCommand = command;

            sda.UpdateCommand = new SqlCommand("Update order_db Set Sex = @Sex, Age = @Age where Ord_No = @Ord_No", conn);

            sda.UpdateCommand.Parameters.Add("@Sex", SqlDbType.VarChar, 50, "sex");

            sda.UpdateCommand.Parameters.Add("@Age", SqlDbType.VarChar, 50, "Age");

            sda.UpdateCommand.Parameters.Add("@Ord_No", SqlDbType.VarChar, 50, "Ord_No");

对ds的所有dataRow更新后执行更新

 sda.Update(ds);

            ds.AcceptChanges();

速度小有提升,因为与执行SQL语句的方法类似所以不太明显。这要是要处理这么多数据也要一大段时间。恼火。
这时，突然想起曾经在做数据采集时用到的一个对象——SqlBulkCopy。这玩意很特殊，只能插入，但是速度那真不是吹出来的。1W条数据进行写入时可能就1秒不到。
头脑风暴来了，因为是数据分析，所以数据的安全性可以不用考虑。我设想删除的动作会非常快。然后再写入进去不就可以了。
步骤就是取出到一个dataset中，然后对dataset中的值进行更改。然后删除掉数据库中的数据，再用SqlBulkCopy拷贝进去就可以了。

 var lq = from t in ds.Tables[0].AsEnumerable()

                             select new

                             {

                                 ordNo = t.Field<string>("Ord_No")

                             };

                    List<string> ordList = new List<string>();

                    foreach (var q in lq)

                    {

                        ordList.Add(q.ordNo);

                    }

                    string k = string.Join("','", ordList.ToArray());

                    Console.WriteLine(DateTime.Now.ToString() + "删除原始订单");

                    new SqlCommand()

                    {

                        CommandText = "Delete From order_db Where Ord_No In('" + k + "')",

                        Connection = conn,

                        CommandType = CommandType.Text

                    }.ExecuteNonQuery();

                    Console.WriteLine(DateTime.Now.ToString() + "添加新订单");

                    SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);

                    bulkCopy.DestinationTableName = ("order_db");

                    bulkCopy.BatchSize = ds.Tables[0].Rows.Count;

                    foreach (DataColumn dc in ds.Tables[0].Columns)

                    {

                        bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

                    }

                    bulkCopy.WriteToServer(ds.Tables[0]);

                    bulkCopy.Close();