CODEWITHSUNDEEP
c#entity-framework
John S
John S

Reputation: 8331

What is causing EF Core to retrieve data much slower then the actual SQL Query and suggestions to speed up?

When running a multi join EF Core query against a context to a DB I get my return data in about 20 seconds (This is running the query thru LinqPad or actual code). When I take the actual SQL generated and run that against the same DB either in LinqPad or SSMS the query returns the results in 3 seconds. I understand that there is going to be some overhead in EF but is there anyway to speed or optimize the EF query to speed that up? The EF query loads data into context for further use.

 _context.Organizations
                .Where(predicate)
                .Include(a => a.OrganizationType)
                .Include(a => a.OrganizationLicenses)
                .Include(a => a.Contacts)
                .ThenInclude(b => b.Phones.Where(p => p.IsActive))
                .ThenInclude(a => a.PhoneType)
                .Load();

Upvotes: 2

Views: 800

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

You can try using Split Queries, instead of generating one big query, eg:

  _context.Organizations
          .Where(predicate)
          .Include(a => a.OrganizationType)
          .Include(a => a.OrganizationLicenses)
          .Include(a => a.Contacts)
          .ThenInclude(b => b.Phones.Where(p => p.IsActive))
          .ThenInclude(a => a.PhoneType)
          .AsSplitQuery()
          .Load();

which is intended primarily to reduce the load on the database engine by sending simpler queries, but a side-effect is that EF doesn't have to extract multiple entities from a single tabular result.

Upvotes: 2

Related Questions