项目数据库里已经有了省市数据,任务是导入县数据。其中省市县之间有外键关系。
public void ImportCounties(List<County> counties) { List<City> cities = counties.Select(d => d.City).ToList(); MyTestEntities db = new MyTestEntities(); foreach (var city in cities) { Province province = db.Provinces.Where(d => d.Name == city.Province.Name).First(); city.Province = province; } foreach (var county in counties) { db.Counties.AddObject(county); } db.SaveChanges(); }
这是项目里的源代码,耗时40秒左右。我测试之后发现不仅效率低,而且是有bug的。测试时第一次录入数据没问题,如果删除Counties表里的数据再 导入数据是有问题的,我不知道为什么要为Province赋值而且第一次可以成功,如果有大神看见,请帮我解惑。我只能先尝试以我的方法去做。
public void ImportCountiesSecond(List<County> counties) { MyTestEntities db = new MyTestEntities(); List<City> cities = db.Cities.ToList(); foreach (var county in counties) { county.City = cities.Where(d => d.Name == county.City.Name).FirstOrDefault(); db.Counties.AddObject(county); } db.SaveChanges(); }
速度大概缩减到20秒,但这也不是我所能忍受的。于是上网又查了资料,了解到SqlBulkCopy。
代码如下:
public void ImportCountiesThird(List<County> counties) { MyTestEntities db = new MyTestEntities(); string conStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString; List<City> cities = db.Cities.ToList(); DataTable table = new DataTable(); table.Columns.Add("CityId", typeof(int)); table.Columns.Add("Name"); table.Columns.Add("OrderNum", typeof(int)); table.Columns.Add("IsCity", typeof(bool)); for (int i = 0; i < counties.Count; i++) { County line = counties[i]; int cityId = cities.Where(d => d.Name == counties[i].City.Name).FirstOrDefault().CityId; string name = line.Name; int orderNum = line.OrderNum; bool isCity = line.IsCity; DataRow row = table.NewRow(); row["CityId"] = cityId; row["Name"] = name; row["OrderNum"] = orderNum; row["IsCity"] = isCity; table.Rows.Add(row); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conStr)) { bulkCopy.DestinationTableName = "Counties"; bulkCopy.ColumnMappings.Add("CityId", "CityId"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("OrderNum", "OrderNum"); bulkCopy.ColumnMappings.Add("IsCity", "IsCity"); bulkCopy.WriteToServer(table); } }
时间缩减到4秒左右,其中主要是在EF查询City表并为每一个Counties赋值时耗时占了大半。但这个的优化我觉得暂时做不到了,先这样吧。