linq查询非常慢 时间问题
几十万数据,分页查询10行,花了30秒,太离谱!
改进
1、连表查询拆分成两个查询,在内存中连表。
从30秒,变3秒
2、日期查询改成区间查询
airHourly.Date 数据举例:2015-01-01 02:00:00.000
System.Data.Entity.DbFunctions.TruncateTime(airHourly.Date) == dateHour
改成:(airHourly.Date >= dateHour && airHourly.Date < dateEnd)
3秒变100毫秒。
若仅改2,也是100毫秒。
说明主要原因是2。
public JsonResult StreetStationHourlyData(int page, int rows, string sort, string order, DateTime dateHour, string stations)//, string param, string stationsType { PageModel pageInfo = new PageModel(); pageInfo.Page = page; pageInfo.Rows = rows; pageInfo.Order = order; pageInfo.Sort = sort; string[] stationArray = null; if (stations != null) { stationArray = stations.Split(','); } ResultModel result = new ResultModel(); if (pageInfo.Page <= 0) { pageInfo.Page = 1; } if (pageInfo.Rows <= 0) { pageInfo.Rows = 20; } dateHour = dateHour.Date; var dateEnd = dateHour.Date.AddDays(1); var data = from airHourly in dbq.T_AirStreetDataHourly2 join station in dbq.T_AirStreetStation2 on airHourly.StationID equals station.StationID where airHourly.StationID != null && (airHourly.Date >= dateHour && airHourly.Date < dateEnd) && stationArray.Contains(airHourly.StationID) orderby airHourly.StationID, airHourly.Date select new AirStreetDataDaily2 { O3 = airHourly.O3, PM10 = airHourly.PM10, PM25 = airHourly.PM25, Date = airHourly.Date, Hour = airHourly.Date.Hour, StationName = station.StationName, StationID = station.StationID, Hierarchy = station.Hierarchy, SeatType = station.SeatType, PointNature = station.PointNature, IsStreet = station.IsStreet }; //var data = // from airHourly in dbq.T_AirStreetDataHourly2 // where airHourly.StationID != null && (airHourly.Date >= dateHour && airHourly.Date < dateEnd) && stationArray.Contains(airHourly.StationID) // //where stationArray.Contains(airHourly.StationID) && System.Data.Entity.DbFunctions.TruncateTime(airHourly.Date) == dateHour // orderby airHourly.StationID, airHourly.Date.Hour // select new AirStreetDataDaily2 { StationID = airHourly.StationID, O3 = airHourly.O3, PM10 = airHourly.PM10, PM25 = airHourly.PM25, Date = airHourly.Date, Hour = airHourly.Date.Hour }; //排序 if (pageInfo.Order != null && pageInfo.Sort != null) { bool isAsc = pageInfo.Order == "asc"; data = data.OrderBy(new[] { pageInfo.Sort }, new[] { isAsc }); } var data1 = data.Skip((pageInfo.Page - 1) * pageInfo.Rows).Take(pageInfo.Rows).ToList(); //var dataB = (from station in dbq.T_AirStreetStation2 // where stationArray.Contains(station.StationID) // select new AirStreetDataDaily2 { StationName = station.StationName, StationID = station.StationID, Hierarchy = station.Hierarchy, SeatType = station.SeatType, PointNature = station.PointNature, IsStreet = station.IsStreet } //).ToList(); foreach (var item in data1) { //var station2= dataB.Where(d => d.StationID == item.StationID).Select(d=>d).FirstOrDefault(); //item.StationName = station2.StationName; //item.StationID = station2.StationID; //item.Hierarchy = station2.Hierarchy; //item.SeatType = station2.SeatType; //item.PointNature = station2.PointNature; //item.IsStreet = station2.IsStreet; if (item.O3 != null) { item.O3 = Math.Round((double)item.O3, 2, MidpointRounding.AwayFromZero); } if (item.PM10 != null) { item.PM10 = Math.Round((double)item.PM10, 2, MidpointRounding.AwayFromZero); } if (item.PM25 != null) { item.PM25 = Math.Round((double)item.PM25, 2, MidpointRounding.AwayFromZero); } } result.rows = data1; result.total = data.Count(); result.success = true; return Json(result);//, JsonRequestBehavior.AllowGet }
树立目标,保持活力,gogogo!