负载时间过长

Question

我正在使用MVC5和EF6开发一个应用程序。我使用作为数据库。我在数据库中有两个表/实体。

1. 车辆-包含有关车辆的信息
2. VehicleLog -包含从全球定位系统跟踪器收到的数据适合于车辆。

我的VehicleLog表目前有大约20K的记录，从特定车辆的表中获取数据需要大约80秒。例如:我试着获取最后一个记录来显示车辆的当前状态(即移动或停止)，这需要超过一个半分钟的时间。

车辆记录表中的记录数量将随着时间的推移而增加。

当我尝试使用打开表时，它会在5-10秒内显示所有数据。有人能帮我快速加载页面上的细节吗？

感谢你阅读并关注这个问题。

我的守则：

 public ActionResult Dashboard()
    {
        ApplicationUser au = db.Users.Find(currentUser);

        var myVehicles = from vehicle in au.Vehicles.ToList()
                         where vehicle.License.ExpiryDate >= DateTime.Now && !vehicle.IsDeleted
                         select new CurrentVehicleStatus
                         {
                             VehicleName = vehicle.Name,
                             DriverName = vehicle.Driver != null ? vehicle.Driver.Name : "No driver",
                             DriverId=vehicle.Driver != null ? vehicle.Driver.DriverId : 0,
                             VehicleId = vehicle.VehicleId,
                             VehicleStatus = GeoUtils.GetStatusOf(vehicle.GsmDeviceLogs.Last())
                          };
    return PartialView("Dashboard", myVehicles);
    }


public static VehicleStatus GetStatusOf(GSMDeviceLog deviceLog)
    {
        VehicleStatus currentStatus = VehicleStatus.Stop;

        if (deviceLog != null)
        {
            //Considering DigitalInputLevel1 as Ignition. Not a correct way to do it as DigitalInputLevel1
            //is device dependent. Must change in future.
            if (deviceLog.DigitalInputLevel1)
                currentStatus = VehicleStatus.Idle;

            if (deviceLog.DigitalInputLevel1 && deviceLog.Speed > ZERO_SPEED)
                currentStatus = VehicleStatus.Moving;
            else if (!deviceLog.DigitalInputLevel1 && deviceLog.Speed >= ZERO_SPEED)
                currentStatus = VehicleStatus.Towed;
            if ((DateTime.Now - deviceLog.DateTimeOfLog).TotalMinutes > 2)
                currentStatus = VehicleStatus.Unreachable;
        }
        else
            currentStatus = VehicleStatus.Unreachable;

        return currentStatus;
    }

如果我评论最后一行(VehicleStats=.)页面的加载时间低于1秒。但如果它发表评论的话，大约2分钟。

型号：

public class Vehicle
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int VehicleId
    { get; set; }

    [Display(Name = "Vehicle name")]
    [StringLength(100)]
    public String Name
    { get; set; }

    [Required]
    public String VehicleType
    { get; set; }

    [Required]
    [StringLength(20)]
    [Display(Name = "Registration no.")]
    public String RegNo
    { get; set; }

    [Required]
    [StringLength(100)]
    public String Manufacturer
    { get; set; }

    [StringLength(20)]
    [Display(Name = "Model or Year")]
    public String Year
    { get; set; }

    [StringLength(100)]
    [Display(Name = "Service provider")]
    public String ServiceProvider
    { get; set; }

    [DataType(DataType.Date)]
    [Display(Name = "Insurance date")]
    [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)]
    public DateTime InsuranceDate
    { get; set; }

    [DataType(DataType.Date)]
    [Display(Name = "Last serviced on")]
    public DateTime LastServicedOn
    { get; set; }

    [Display(Name = "Last serviced at (km)")]
    public int LastServicedAt
    { get; set; }

    [Display(Name = "Next service at (km)")]
    public int NextServiceAt
    { get; set; }

    [DataType(DataType.Date)]
    [Display(Name = "PUC expiry date")]
    public DateTime PUCExpiryDate
    { get; set; }


    [Display(Name = "Vehicle Ownership document")]
    [DataType(DataType.ImageUrl)]
    public virtual List<OwnershipPaper> OwnershipPapers
    { get; set; }

    //[Display(Name = "Vehicle status")]
    //public VehicleStatusType VehicleStatus
    //{ get; set; }

    [Display(Name = "Target Utilization (km) per day")]
    public int TargetUtilizationPerDay
    { get; set; }

    public virtual Driver Driver
    { get; set; }
    [Display(Name = "Vehicle Group")]

    [Required]
    public virtual VehicleGroup VehicleGroup
    { get; set; }

    public string IMEI 
    { get; set; }

    [Display(Name="Fuel tank capacity")]
    [Required]
    public double FuelTankCapacityLitres
    { get; set; }

    public virtual License License { get; set; }

    public virtual ICollection<GSMDeviceLog> GsmDeviceLogs { get; set; }

    public virtual Policy Policy { get; set; }

    public virtual ApplicationUser User { get; set; }

    public bool IsDeleted { get; set; }
}



public class GSMDeviceLog
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int GSMDeviceLogId { get; set; }
    public string IMEI { get; set; }
    public string Message { get; set; }
    public string ProfileName { get; set; }
    public bool GPSStatus { get; set; }
    public int SignalStrength { get; set; }
    public DateTime DateTimeOfLog { get; set; }
    //public string TimeOfLog { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
    public float Altitude { get; set; }
    public float Speed { get; set; }
    public float Direction { get; set; }
    public int NoOfSatelite { get; set; }
    public float GPSPositionAccuracyIndication { get; set; }
    public float MilageReading { get; set; }
    public string Cell { get; set; }
    public float AnalogInputVoltage1 { get; set; }
    public float AnalogInputVoltage2 { get; set; }
    public float AnalogInputVoltage3 { get; set; }
    public float AnalogInputVoltage4 { get; set; }
    public bool DigitalInputLevel1 { get; set; }
    public bool DigitalInputLevel2 { get; set; }
    public bool DigitalInputLevel3 { get; set; }
    public bool DigitalInputLevel4 { get; set; }
    public bool DigitalOutputLevel1 { get; set; }
    public bool DigitalOutputLevel2 { get; set; }
    public bool DigitalOutputLevel3 { get; set; }
    public bool DigitalOutputLevel4 { get; set; }

    [Display(Name="Address")]
    public string Location { get; set; }

    public int InfoNumber { get; set; }

    //Reperesent harsh accelration and deaccelration
    public bool HarshDetecation { get; set; }

    //RFID Tag Number
    [StringLength(15)]
    public string RFID { get; set; }

    //public virtual Policy Policy { get; set; }

    public virtual ICollection<Violation> Violations { get; set; }

    public virtual Vehicle Vehicle { get; set; }


}

Answer 1

您的查询实际上是内存中的LINQ对象，而不是数据库查询(LINQ )，因为源集合是au.Vehicles，它是内存中的集合。但是，由于延迟加载，涉及到几个数据库查询，它们加载的数据比幕后所需的数据多得多：

• au.Vehicles是延迟加载的，即用户au -> 1 DB查询的所有工具。
• Vehicle.License每辆车都懒散地加载-> 1 DB查询(满足where过滤器)
• Vehicle.Driver每辆车都懒散地加载-> 1 DB查询(满足where过滤器)
• Vehicle.GsmDeviceLogs是延迟加载的，即每辆车的所有车辆日志-> 1 DB查询(满足where过滤器)

所有这些延迟加载查询都很可能是性能较差的原因。

为了确保整个过程是一个只加载所需数据的单一数据库查询，您必须尽可能多地从DbSet<T>/IQueryable<T>组合查询。在您的情况下，尤其要避免使用用于Find的ApplicationUser。看起来可能是这样的：

var myVehicles = db.Users
    .Where(u => u.UserName == currentUser)
    .SelectMany(u => u.Vehicles)
    .Where(v => v.License.ExpiryDate >= DateTime.Now && !v.IsDeleted)
    .Select(v => new
    {
        VehicleName = v.Name,
        DriverName = v.Driver != null ? v.Driver.Name : "No driver",
        DriverId = v.Driver != null ? v.Driver.DriverId : 0,
        VehicleId = v.VehicleId,
        LastGsmDeviceLog = v.GsmDeviceLogs
            .OrderByDescending(gdl => gdl.CreateDateTime)
            .FirstOrDefault()
    })
    .AsEnumerable()
    .Select(x => new CurrentVehicleStatus
    {
        VehicleName = x.VehicleName,
        DriverName = x.DriverName,
        DriverId = x.DriverId,
        VehicleId = x.VehicleId,
        VehicleStatus = GeoUtils.GetStatusOf(x.LastGsmDeviceLog)
    });

正如Max提到的，不能将GeoUtils.GetStatusOf转换为SQL，因此必须用AsEnumerable()结束数据库查询(这比ToList()占用的内存少)，并执行最终的Select并在内存中调用GetStatusOf。

Answer 2

问题在于调用ToList()。这将计算查询，并从数据库中检索每一行。

然后在循环中调用导航属性。这将导致对数据库中的每个项执行新的查询。这是运行时缓慢的原因。

如果您只是将'ToList()‘调用移到'select’语句之后，那么您的所有问题都将得到解决。

myVehicles = from vehicle in au.Vehicles
    where vehicle.License.ExpiryDate >= DateTime.Now && !vehicle.IsDeleted
    select new
    {
        VehicleName = vehicle.Name,
        DriverName = vehicle.Driver.Name ?? "No driver",
        DriverId= vehicle.Driver == null ? 0 : vehicle.Driver.DriverId,
        VehicleId = vehicle.VehicleId,
        GsmDeviceLogs = vehicle.GsmDeviceLogs.LastOrDefault()
    }.ToList()
    .Select({vehicle => new CurrentVehicleStatus
        VehicleName = VehicleName,
        DriverName = DriverName,
        DriverId= DriverId,
        VehicleId = VehicleId,
        VehicleStatus = GeoUtils.GetStatusOf(GsmDeviceLogs)
    });

编辑:将三元操作符改为空合并，以使它们对EntityFramework友好(驱动程序是否为空并不重要，因为这是一个在幕后转换为SQL的表达式)。

编辑:看到了GeoUtils的代码，更新了答案。它肯定不会在表达式树中被解析。另外，更改了对LastOrDefault()的调用。

Answer 3

它会带来很多麻烦。尝试Max方法，但是更改不能转换为SQL的LastOrDefault()为OrderBy + FirstOrDefault。就像这样

myVehicles = from vehicle in au.Vehicles
where vehicle.License.ExpiryDate >= DateTime.Now && !vehicle.IsDeleted
select new
{
    VehicleName = vehicle.Name,
    DriverName = vehicle.Driver.Name ?? "No driver",
    DriverId= vehicle.Driver == null ? 0 : vehicle.Driver.DriverId,
    VehicleId = vehicle.VehicleId,
    GsmDeviceLogs = vehicle.GsmDeviceLogs.OrderByDescending(l => l.Id).FirstOrDefault()
}.ToList()
.Select({vehicle => new CurrentVehicleStatus
    VehicleName = VehicleName,
    DriverName = DriverName,
    DriverId= DriverId,
    VehicleId = VehicleId,
    VehicleStatus = GeoUtils.GetStatusOf(GsmDeviceLogs)
});

因此，它应该是“外部应用”SQL语法。