EF Core Lazy loading extremely slow

Question

I did a small test enabling lazy loading.optionsBuilder.UseLazyLoadingProxies().UseSqlServer(ConnectionString);

(with EF Core 2.1.4)

I am looping through instruments with and without and this is the results I get

Case 1

var instruments = db.instruments.OrderBy(t=>t.id).Include(t=>t.NavPro1).ThenInclude(t=>t.NavPro2).Take(200);

Case 2

var instruments = db.instruments.OrderBy(t=>t.id).Include(t=>t.NavPro1).ThenInclude(t=>t.NavPro2).Take(200);

Then

   foreach (var i in instruments)
   {
        var props = i.NavPro1;
        foreach (var prop in props)
        {
            sbPrintGreeks.AppendLine(prop.NavPro2.Name + " - " + prop.id + " - " + prop.Value);
        }
   }

Takes 7s to get 100k rows without lazy loading

Takes 160s to get 3k rows with lazy loading.

What can be done to get decent performance ?

This could be helpful also, When *Eager , Lazy and explicit loading in EF?* https://stackoverflow.com/a/34628138/2946329 — Salah Akbari, Nov 03 '18 at 16:05
And this https://stackoverflow.com/questions/97197/what-is-the-n1-select-query-issue — Salah Akbari, Nov 03 '18 at 16:57

score 4 · Answer 1 · answered Nov 03 '18 at 14:24

This is a general problem that is known as the N+1 problem.

What's happening here is that you have a lot more requests when you use lazy loading.

The case where you use Include, you have one huge request that gives you all the data - or maybe one request per table, so three request in your case. This depends on the exact structure of your data.

In the case with the lazy loading, you have one request for the instruments and for each instrument you have another request for NavPro1. And for each NavPro1 element, you have yet another request for NavPro2.

So, if you have 1000 instruments, and each has 10 NavPro1, you now have 1 + (1000 * (1 + 10)) = 11001 requests instead of a maximum of three requests. That's slow, period.

score 4 · Accepted Answer · answered Nov 03 '18 at 14:26

Yes, avoid lazy loading. Period.

Problem is - always, going back to every ORM ever built - that if you do lazy loading, every reference is a lazy load. Which is 1+ round trips (one per property, minimum). Separate SQL execution, separate network time. That adds up extremely fast.

Which is why every ORM ever written supports NON lazy loading, in the EF variant via the .Include statements, which either extend the SQL, or generate separate SQL (ef core, tolist to materialize relationships with efficient sql).

If you insist on using lazy loading - as your question implies, there is no magic dust you spread over your code to avoid the implied negatives of lazy loading.

Now, adding to that - any EF Core before 3.0 is as broken as it gets. And yes, that is 3.0 - not even 2.2. See, there are TONS of issues with various parts, including pretty basic LINQ. And performance. At least 2.2 (coming hopefully in a month) should fix some issue. Until then, try using .Include and AsNoTracking - because IIRC there is a performance bug that also may hit you with when loading 200k rows.

Lazy loading shouldn't even exist. Waiting for the database to magically load the values you want... I used to turn it off when I had to use EF 6 — Camilo Terevinto, Nov 03 '18 at 14:41
ok thank you. Can you elaborate, as broken as it gets on pretty basic LINQ ? IIRC there is a performance bug ? Do you use AsNoTracking everywhere you are only querying ? — sofsntp, Nov 03 '18 at 14:56
Head over to the github repository, look at the bug reports. Stuff like union being client evaluated, Null checks blowing LINQ execution. Among them, also, is a bug about change tracking in large loads getting VERY slow over time, which is what you do. Fix is IIRC in for 2.2. — TomTom, Nov 03 '18 at 15:17

score -1 · Answer 3 · answered Nov 19 '18 at 13:22

-1

https://github.com/aspnet/EntityFrameworkCore/issues/12451

This is solution

services .AddDbContext(b => b.ReplaceService());

answered Nov 19 '18 at 13:22

Alexander Tretjakov

1
1

services .AddDbContext(b => b.ReplaceService()); – Alexander Tretjakov Nov 19 '18 at 13:22
can you explain please ? – sofsntp Nov 19 '18 at 13:56
This solution is for ONE DbContext. I need to replace the default service for all future registered DbContexts. – Alexander Tretjakov Nov 20 '18 at 15:59

EF Core Lazy loading extremely slow

3 Answers3