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
 
}