3

I have a complex LINQ query with grouping and multiple includes. I found out that ToListAsync() takes more then second to complete, while ToList() returns in a fraction of second.

allReservationsList = allReservations.ToList(); // fast
allReservationsList = await allReservations.ToListAsync(); // more then a second for 60 rows

Am I doing anything wrong or is it an issue with entity framework?

Here is the query: 

            var reservations = db.Reservations
                .Include(r => r.PickUpLocation)
                .Include(r => r.ReturnLocation)
                .Include(r => r.RequestedVehicleModel).ThenInclude(m => m.Photos)
                .Include(r => r.RequestedVehicleModel.VehicleType.VehicleModels)
                .Include(r => r.RequestedVehicleType).ThenInclude(t => t.Photos)
                .Include(r => r.AssignedUnit.VehicleModel.VehicleType)
                .Include(r => r.DailyPrice.Currency);

            var allReservations =
                from r in reservations
                orderby r.VersionDate descending
                group r by r.BookingCode into g
                select g;
Sergey Kandaurov
  • 2,626
  • 3
  • 24
  • 35
  • Would you please provide your query and relevant code? The only thing I can say is what Chris said bellow. Async will always be slower for one operation than a sync call due to overhead. – Maxime Rouiller Nov 10 '15 at 14:34
  • @MaximeRouiller I added the query. The difference in performance is too dramatic to be explained by overhead, probably 100s times. – Sergey Kandaurov Nov 10 '15 at 16:20
  • This is almost assuredly because of the Photos table containing varbinary(max) and/or [n]varchar(max) columns. https://stackoverflow.com/questions/28543293/entity-framework-async-operation-takes-ten-times-as-long-to-complete – Adam Caviness Jun 28 '17 at 17:14

2 Answers2

1

There's too many variables here. However, generally speaking, async methods will take longer to return than sync methods, simply because async has more overhead. Whether that accounts for the entire delay you're seeing or not, there's no way to tell, at least with the information you've provided.

Chris Pratt
  • 232,153
  • 36
  • 385
  • 444
  • 1
    I added the query. I would understand 50%, even 100% difference. But what I see is probably 100s time difference. ToList() completes nearly instantly, and ToListAsync() takes more then a second. In VS Diagnostic Tool I can see it very clearly on CPU utilization graph - a small and short spike vs. high load more then a second long. – Sergey Kandaurov Nov 10 '15 at 16:19
  • Again, there's just too many variables to say what's going on. This seems like pre-mature optimization to me, though. Publish to a proxy of your production environment, hit it with load and profile it there. If it's problematic at that point, then you can proceed in trying to optimize. At the very least you'll have a lot more relevant data to work with at that point. As it stands, I'd say don't worry about it. – Chris Pratt Nov 10 '15 at 16:30
  • 1
    This is exactly the reason I started to look at the problem: response time was too slow in a production environment. So I figured out what function call makes it slow, replaced it with a synchronous analog, and it fixed performance problems. It works fine like this, but it seems very strange to me that the async call is hundreds times slower then the synced one. – Sergey Kandaurov Nov 10 '15 at 16:59
  • Aside from the inherent overhead, the only thing that could really make async take longer would be if there are no available threads. Just as with a sync action, where if all the threads are tied up, you're queued, once the async action needs to continue processing, after giving up its thread, it must wait until a thread is available, and will queue if there are none. However, if that's occurring then your server is getting too much load and you need to give it more resources. – Chris Pratt Nov 10 '15 at 17:50
  • I can see this even in a test environment with a single request. Also the CPU utilization graph show high CPU load for over a second, so it's actually doing something CPU-intensive. – Sergey Kandaurov Nov 10 '15 at 18:05
1

As reported in this SO answer, if you're using large column types (blobs, varchar(max), etc.), Entity Framework 6 will perform poorly when using ToListAsync(). I can't say I 100% understand the intricacies which make this the case, but suffice it to say you are correct to use ToList() if you're retrieving large data values like this.

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315