Linq takes too long get results

Question

I'm attempting to make the code below run as quickly as possible. It takes about couple mins to get the results added the finaloutput.

This code is part of API endpoint that get vehicle information, foreach loops over each vehicle to count the number of plates from PlateVehicle table and from another policies table to get insurance use.

        foreach(var vehicle in result)
           {
                //Get total Plates Count
                var plates = await _context.PlateVehicles
                             .Where(a => a.VehicleId.Equals(vehicle.VehicleId)
                                    && a.Status=="Active").ToListAsync();
                vehicle.PlatesCount = plates.Count();
    
                //Get Insurance Use from Policy
                var policyInfo = await _context.Policies
                               .Where(a=>a.Vehicle_Id.Equals(vehicle.VehicleId)).FirstOrDefaultAsync();

                if (policyInfo != null)
                    {
                      vehicle.InsuranceUse = policyInfo.Class == null ? string.Empty : policyInfo.Class;
                      finalOutput.Add(vehicle);
                    }
             }

there are around 6000 vehicle records in the DB and it is currently taking about 2 mins to get the results. Any suggestions greatly appreciated. Thanks.

You should include information about your server and database model / table, incl indexes etc. — marsze, Aug 06 '23 at 22:00
How many `vehicle` is in the `result` that you're looping through? — Poul Bak, Aug 06 '23 at 22:34
Show us what `result` is! if it is a query, then you should be able to join the results. If you can't change what `result` is then use a TVP; https://stackoverflow.com/a/73202010/4139809 — Jeremy Lakeman, Aug 07 '23 at 01:05

score 2 · Accepted Answer · answered Aug 06 '23 at 22:41

There's a lot that can be done to clean that up. With Entity Framework you want to leverage projection to get the relevant data in a single query rather than a more ADO-like approach fetching data in queries.

For a minimal impact solution to what you already have:

foreach(var vehicle in result)
{
    vehicle.PlateCount = await _context.PlateVehicles
        .Where(a => a.VehicleId.Equals(vehicle.VehicleId)
            && a.Status=="Active")
        .Count();

 // If there can be more than one policy, add an OrderBy then use FirstOrDefault()
    vehicle.InsuranceUse = await _context.Policies
        .Where(a => a.Vehicle_Id.Equals(vehicle.VehicleId))
        .Select(a => a.Class)
        .SingleOrDefault() ?? string.Empty; 

    finalOutput.Add(vehicle);
}

This doesn't address the fact that you are running these queries for each individual vehicle.

To further improve this, you would need to look at where the vehicle data is being loaded and how it is structured. For instance if we have a DTO or ViewModel for the Vehicle and it was loaded from the database, the best thing would be to adjust the loading of the vehicle data to project these fields directly into the view model using navigation properties. For instance to include the vehicle plate count:

var vehicles = await _context.Vehicles
    .Where(v => /* search criteria */)
    .Select(v => new VehicleViewModel
    {
        Id = v.Id,
        // Other properties...
        PlateCount = v.PlateVehicles.Count(pv => pv.Status == "Active");
        InsuranceUse = v.Policies.Select(p => p.Class) // Let the ViewModel getter resolve #null as string.Empty
     }).ToListAsync();

This would fetch the required details all within one query to the DB for maximum efficiency, but requires that all of the navigation properties are set up properly for the related entities.

Thank you , I was able to get the platescount and insuranceuse in one DB call using select statement you provided. — sri, Aug 07 '23 at 23:50

score 1 · Answer 2 · answered Aug 06 '23 at 22:02

1

You're looping through vehicles and making a _context (database) call every loop. So essentially you're calling the database 6000 times if there is 6000 records. This would be why it's taking so long to get through. I'd suggesting making a stored procedure or a view to get the data necessary and call that once.

answered Aug 06 '23 at 22:02

B-Brazier

96
6

1

SP is an option, but it is a lazy and in this case less than optimal solution. There are set-based queries that we can execute directly using EF and LINQ. SP is better for complex write operations or view models with dozens of sub queries per entity. SP in this particular scenario just adds uneccesary technical debt if your DB logic is not already encapsulated in SPs – Chris Schaller Aug 07 '23 at 12:29

Linq takes too long get results

2 Answers2