ASP.Net杂谈之 — 10W条数据拷贝效率测试（接上篇 “Linq杂谈之 — Linq导入Excel”）

       上篇提要：

       接到一个需求，实现一个小功能—导入Excel，想想诸位活跃于.NET平台上的兄弟们，其中应该有相当一部分是从事如信息系统类开发的，所以小弟在这里姑且臭屁一下导入Excel的几种实现方法，如有错误之处，烦请大虾指正。

       1、 Excel导入 — 循环执行插入操作，批量导入数据

       2、 Excel导入 — 使用Linq提供的InsertAllOnSubmit方法，批量导入数据

       3、 Excel导入 — 使用SqlBulkCopy的WriteToServer方法，批量导入数据

      本篇重点： 

      在上一篇中谈到了.NET的三种数据拷贝方法，但是并没有做性能测试，今天闲来无事，索性做下性能测试，测试数据量10W条。

      机器配置及开发工具：CPU/P E5200 2.5GHZ

                    内存/2G

                    操作系统/Microsoft WINDOWS XP SP3

                    数据库/Microsoft SQL SERVER 2005

                    Framework/.Net Framework 3.5 SP1

                    开发工具/Microsoft  VS 2008 SP1

      原始数据表：IPInfo[IPid（Identity）,IPFrom,IPTo,IPLocation,IPCity,IPToNumber,IPFromNumber]

      数据条数：345000条（这里用10W条来测试）

             

      导入数据表：IPInfo1[IPid（Identity）,IPFrom,IPTo,IPLocation,IPCity,IPToNumber,IPFromNumber]

      数据条数：0条，每次导入前，均清空IPInfo1表中数据

      首先是测试第一种方法，也就是直接用ADO.NET执行循环插入操作，批量导入数据，这里先贴上CODE：

public static void InsertToIPInfo1_ADO(DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection("Data Source=WSH-S0904003;Initial Catalog=GroupSell;User ID=sa;Password=123"))
            {
                conn.Open();
                string strSql = "Insert Into IPInfo1(IPFrom,IPTo,IPLocation,IPCity,IPToNumber,IPFromNumber) values (@IPFrom,@IPTo,@IPLocation,@IPCity,@IPToNumber,@IPFromNumber)";
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SqlCommand Comm = conn.CreateCommand();
                    Comm.CommandText = strSql;
                    Comm.CommandType = CommandType.Text;
                    Comm.Parameters.Add("@IPFrom", SqlDbType.VarChar).Value = dt.Rows[i]["IPFrom"];
                    Comm.Parameters.Add("@IPTo", SqlDbType.VarChar).Value = dt.Rows[i]["IPTo"];
                    Comm.Parameters.Add("@IPLocation", SqlDbType.VarChar).Value = dt.Rows[i]["IPLocation"];
                    Comm.Parameters.Add("@IPCity", SqlDbType.VarChar).Value = dt.Rows[i]["IPCity"];
                    Comm.Parameters.Add("@IPToNumber", SqlDbType.VarChar).Value = dt.Rows[i]["IPToNumber"];
                    Comm.Parameters.Add("@IPFromNumber", SqlDbType.VarChar).Value = dt.Rows[i]["IPFromNumber"];
                    Comm.ExecuteNonQuery();
                }
            }
        }

 

         下面是调用方法：

class Program
    {
        static void Main(string[] args)
        {
            //取10W数据
            DataTable dt = IpInfo_BLL.get10WData();
            //开始时间
            Console.WriteLine(DateTime.Now.ToString());
            DateTime db = DateTime.Now;
            
            //Linq InsertAllOnSubmit()
            IpInfo_BLL.InsertToIPInfo1_Linq(dt);
            
            //ADO Insert
            //IpInfo_BLL.InsertToIPInfo1_ADO(dt);

            //SqlBulkCopy 
            //IpInfo_BLL.InsertToIPInfo1_SqlCopy(dt);

            //时间差值
            Console.WriteLine(DateTime.Now - db);
        }
        
    }

 

          下面是取DataTable数据的方法，索性也贴上来：

public static DataTable get10WData() 
        {
            using (SqlConnection conn = new SqlConnection("Data Source=WSH-S0904003;Initial Catalog=GroupSell;User ID=sa;Password=123"))
            {
                string strSql = "select top(100000) * from IPInfo";
                DataSet ds = new DataSet();
                SqlDataAdapter adaper = new SqlDataAdapter(strSql, conn);
                adaper.Fill(ds);
                return ds.Tables[0];
            }

        }

 

              下面上结果：

           耗时2分34秒8281250（小数部分）

           下面我们测试第二种方法也就是使用Linq的InsertAllOnSubmit的方法实现数据拷贝：

public static void InsertToIPInfo1_Linq(DataTable dt)
        {
            using (IPInfo1DataContext db = new IPInfo1DataContext()) 
            {
                var query = from q in dt.AsEnumerable()
                            select new
                            {
                                IPFrom = q["IPFrom"].ToString().Trim(),
                                IPTo = q["IPTo"].ToString().Trim(),
                                IPLocation = q["IPLocation"].ToString().Trim(),
                                IPCity = q["IPCity"].ToString().Trim(),
                                IPToNumber = q["IPToNumber"].ToString().Trim(),
                                IPFromNumber = q["IPFromNumber"].ToString().Trim()
                            };
                List<IPInfo1> list = new List<IPInfo1>();
                foreach (var q in query)
                {
                    IPInfo1 Entity = new IPInfo1();
                    Entity.IPCity = q.IPCity;
                    Entity.IPFrom = q.IPFrom;
                    Entity.IPFromNumber = q.IPFromNumber;
                    Entity.IPLocation = q.IPLocation;
                    Entity.IPTo = q.IPTo;
                    Entity.IPToNumber = q.IPToNumber;
                    list.Add(Entity);
                }
                db.IPInfo1.InsertAllOnSubmit(list);
                db.SubmitChanges();
            }
        }

 

            下面上结果：

          耗时1分50秒4843750（小数部分）

          下面我们来看第三种方法，使用SqlBulkCopy的WriteToServer方法，批量导入数据，大家要做好心理准备哦！

public static void InsertToIPInfo1_SqlCopy(DataTable dt) 
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy("Data Source=WSH-S0904003;Initial Catalog=GroupSell;User ID=sa;Password=123"))
            {
                bulkCopy.DestinationTableName = "IPInfo1";
                bulkCopy.WriteToServer(dt);
            }
        }

 

          下面上测试结果：

            耗时：0分5秒9532774（小数部分）

        测试总结：

        测试结果显示选择使用ADO.NET的基本方法来复制大批数据是错误的，也是耗时最长的，这里的测试是在本机进行的，如果数据库的Server跟网站的Server不在同一台服务器上，将会花费更多的时间。当然Linq的效率要比前者高些，略高2/5，但Linq操作起来便捷，对效率要求不是太苛刻的朋友，用Linq导入数据也是一种不错的选择，当然了，最棒的方法还是SqlBulkCopy，但是这种做法的缺点是要求EXCEL的格式必须与要导入的Table完全一致而且还要删除多余的Sheet。所以测试结果如上，朋友们可以视情况而定。