Entity Framework Core 3.0 .Include call taking 10x more time

Question

I reviewed almost everything online but couldn't get it work in a reasonable time.

Entity Framework Core 3.0 .Include call is taking 10x more time than calling the individual tables separately and then mapping them in C# code.

I have two tables product and productimages, where product productimages is a collection inside product.

Now when I chain the call with .Include function, it takes forever to get the records form DB:

products = _context.ProductUploadQueue
               .Include(x => x.ProductUploadQueueImage)
               .Where(x => x.ClientId == clientId && x.PalletId == palletID)
               .ToList();

but if I do the same while calling the both tables individually and not chaining the Include call, it speeds up everything:

 var g = _context.ProductUploadQueue
      .AsNoTracking()
      .Where(x => x.ClientId == clientId && x.PalletId == palletID)
      .ToList(); 

 var hp = g.Select(x => x.Id); 

 var y = _context.ProductUploadQueueImage
      .Where(x => hp.Contains(x.ProductUploadQueueId))  
      .ToList();

How can I speed it up?

Could this be an index problem on the database? Get the SQL call that EF generates and run that in management studio and see if the query takes a long time there — Zeb Rawnsley, Jun 06 '20 at 20:52
@ZebRawnsley our tables are well indexed and perform good when queried directly, also I'm not sure about generating sql from LINQ can you share how can I do that? — Zeeshan Adil, Jun 06 '20 at 21:05
First thing I would check if this is problem with db or with EF tracking/mapping etc. To do this, just go into Sql Server Profiler, take a look at generated query from first case and run it in management studio with option show query plan (ctrl + m) — sTrenat, Jun 06 '20 at 21:09
defintely worst diagnosing the issue. If db is culprit, sometimes a simple but well designed index can make the difference — Pac0, Jun 06 '20 at 21:13
@ZeeshanAdil see the answer here https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework — Zeb Rawnsley, Jun 06 '20 at 21:15
@Pac0 yes it might be but in the current case EF 3 has some changes in the implementation and they don't recommend the include thing. — Zeeshan Adil, Jun 13 '20 at 00:49

David Browne - Microsoft · Accepted Answer · 2020-06-06T21:07:32.680

2

how can I speed it up?

You just discovered how. Go back and look at g and you'll find that all of the productUploadQueueImage navigation properties have been populated. When you run the second query the Change Tracker will fix-up any relationships as it loads the second colleciotn.

Before EF Core 3, Include queries could be executed in multiple round-trips, like you are doing here. Generating a single query that includes multiple tables is often significantly slower.

edited Jun 06 '20 at 21:07

answered Jun 06 '20 at 20:54

David Browne - Microsoft

80,331
6
39
67

Hi David, Thanks for sharing that, and yes, I am getting a reasonably better performance with that but as far as I remember, include calls used to be fast. if I go with separate calls for both entities, I will have to fix the bottlenecks throughout the application, is there a way to do it with EF in your opinion? – Zeeshan Adil Jun 06 '20 at 21:04
1

In that case, I think I am only left with the option of separating the include call wherever we need performance, provided it will add some overhead for the database roundtrip calls too. thanks for the answer, I'm accepting it – Zeeshan Adil Jun 06 '20 at 21:10
@ZeeshanAdil A lot of people experienced and reported this 3.0 introduced significant query degradation. Related issues are [Significant Query Slowdown When Using Multiple Joins Due To Changes In 3.0 #18022](https://github.com/dotnet/efcore/issues/18022) (issue) and [Relational split query mode for collection includes #20892](https://github.com/dotnet/efcore/issues/20892) (actual work). Suggested workaround by EF Team member is https://gist.github.com/smitpatel/d4cb3619e5b33e8d9ea24d3f2a88333a – Ivan Stoev Jun 07 '20 at 06:31
@IvanStoev Thanks for sharing that. Appreciate it. – Zeeshan Adil Jun 13 '20 at 00:48

Entity Framework Core 3.0 .Include call taking 10x more time

1 Answers1