Too much load time

Question

I'm using MVC5 along with EF6 to develop an application. I'm using SQL Server Express for database. I have two tables/entities in the database.

1. Vehicle - Contains Information about the vehicle
2. VehicleLog - Contains data received from GPS Tracker Fit in the vehicle.

My VehicleLog table currently has around 20K records and it takes around 80 Seconds to fetch data from the table for a particular vehicle. For example: I tried fetching the last record to show current status of vehicle(i.e. Moving or stopped) and it takes more than 1 and half minute for that.

The number of records in Vehicle log table will increase with time.

When I try to open the table using Server explorer it shows ALL the data with in 5-10 seconds. Can anyone help me get the details loaded quickly on the page as well.

Thanks for reading and paying attention to the question.

My Code:

 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;
    }

If i Comment the last line(VehicleStats=....) the Load time for page get below 1 second. But If Its commented then around 2 minutes.

Model:

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

Your query is actually LINQ to Objects in memory and not a database query (LINQ to Entities) because the source collection is au.Vehicles which is a collection in memory. However, due to lazy loading there are several database queries involved that load much more data than you need behind the scenes:

• au.Vehicles is lazily loaded, i.e. all vehicles of the user au -> 1 DB query
• Vehicle.License is lazily loaded -> 1 DB query per vehicle (that satisfies the where filter)
• Vehicle.Driver is lazily loaded -> 1 DB query per vehicle (that satisfies the where filter)
• Vehicle.GsmDeviceLogs is lazily loaded, i.e. all vehicle logs -> 1 DB query per vehicle (that satisfies the where filter)

All those lazy loading queries are most likely the reason for the poor performance.

To make sure that the whole thing is a single database query that only loads the required data you must compose the query as much as you can from a DbSet<T>/IQueryable<T>. In your case especially avoid the Find for the ApplicationUser. It could look like this:

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)
    });

As Max has mentioned the GeoUtils.GetStatusOf can't be translated into SQL, so you must end the database query with AsEnumerable() (which has less memory overhead than ToList()) and perform the final Select and calling GetStatusOf in memory.

Answer 2

It will do a lot of fetches. Try Max approach but change LastOrDefault() that cannot be translated to SQL to OrderBy plus FirstOrDefault. Something like this

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)
});

It should be 'outer apply' SQL syntax as a result.

Answer 3

The problem is your call to ToList(). This evaluates the query and retrieves every row from the database.

You are then calling navigation properties in a loop. This will cause a new query to be executed for each item from the database. This is the cause of the slow runtime.

If you simply move the 'ToList()' call to after the 'select' statement, all your problems will be solved.

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)
    });

Edit: changed the ternary operators to null coalescence to make them EntityFramework friendly (it doesn't matter if the Driver is null as this is an expression which is converted into SQL behind the scenes).

Edit: having seen the code of GeoUtils, updated answer. It definitely won't be parsed in the expression tree. Also, changed call to LastOrDefault().