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

        }