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 ?

Answer 1

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.

Answer 2

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.