Can this Entity Framework Query be improved for performance?

Question

* UPDATE *

I am unable to delete this question even though it was actually not a problem in the long run... I still had some code from when I was working on it that did separate queries and populated some of these fields... when I stripped out that code... the following query runs very fast (2 seconds):

public IQueryable<ApplicationUser> QueriableUsersList()
{
    return _context.Users.OrderBy(u => u.UserName)           
        .Include(u => u.Accounts.Select(a => a.Broker))
        .Include(u => u.Roles);
}

* ORIGINAL QUESTION *

I am trying to load a list of users (about 600 of them) using Entity Framework… I want to preload with the query account records for each user and a broker record for each account (along with roles for each user)… this SQL gets me results very quickly ( < 1 second):

select ISNULL(ur.RoleId, 0) as IsAdmin, u.*, a.*, b.* 
  from AspNetUsers u
 inner join Accounts a on u.Id = a.ClientId
 inner join Brokers b on a.Brokerid = b.BrokerId
 left outer join AspNetUserRoles ur on u.id = ur.UserId and ur.RoleId = 1
order by u.UserName

But this Entity Framework query takes a long time (something like 20 - 25 seconds)... especially in production where the database is on a different server:

public IQueryable<ApplicationUser> QueriableUsersList()
{
    return _context.Users.OrderBy(u => u.UserName)           
        .Include(u => u.Accounts.Select(a => a.Broker))
        .Include(u => u.Roles);
}

Any thoughts on how the Entity Framework query could be improved and/or what it would take to get it to be faster like the native SQL query is?. (Note: I do need all the data from all the above object for my list so lazy loading isn’t going to help.) I was hoping it would generate a single query along the lines of what I show above… but it is generating several queries (below) for each user it seems.

I eventually want to use this IQueryable to get paginated data as well.

My objects are defined like this:

public class ApplicationUser : IdentityUser<int, CustomUserLogin, CustomUserRole, CustomUserClaim>/
{
    public List<Account> Accounts { get; set; }
…
    public bool isAdmin { get {  return this.Roles != null && this.Roles.Any(r => r.RoleId == 1); } }
…
}

public class Account
{
    public int AccountId { get; set; }
…
    public int BrokerId { get; set; }
    public virtual Broker Broker { get; set; }
…
}

public class Broker
{
    public int BrokerId { get; set; }
    public string Name { get; set; }
…
}

With the entity framework query using SQL Server profiler I see one of these:

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[UserName] AS [UserName], 
[Project1].[FirstName] AS [FirstName], 
[Project1].[LastName] AS [LastName], 
[Project1].[PhoneNumber] AS [PhoneNumber], 
[Project1].[Email] AS [Email], 
[Project1].[Address] AS [Address], 
[Project1].[City] AS [City], 
[Project1].[State] AS [State], 
[Project1].[Zip] AS [Zip], 
[Project1].[DateAdded] AS [DateAdded], 
[Project1].[IsActive] AS [IsActive], 
[Project1].[C1] AS [C1], 
[Project1].[AccountId] AS [AccountId], 
[Project1].[ClientId] AS [ClientId], 
[Project1].[BrokerId] AS [BrokerId], 
[Project1].[AccountNumber] AS [AccountNumber], 
[Project1].[BrokerUserName] AS [BrokerUserName], 
[Project1].[BrokerPasswordHash] AS [BrokerPasswordHash], 
[Project1].[EquityCurve] AS [EquityCurve], 
[Project1].[CapitalInvested] AS [CapitalInvested], 
[Project1].[IsSimulated] AS [IsSimulated], 
[Project1].[ClosedProfit] AS [ClosedProfit], 
[Project1].[MarketValueTimeStamp] AS [MarketValueTimeStamp], 
[Project1].[IsAuthorizedForLiveTrading] AS [IsAuthorizedForLiveTrading], 
[Project1].[ActivatedOn] AS [ActivatedOn]
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[Zip] AS [Zip], 
    [Extent1].[City] AS [City], 
    [Extent1].[State] AS [State], 
    [Extent1].[DateAdded] AS [DateAdded], 
    [Extent1].[IsActive] AS [IsActive], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[PhoneNumber] AS [PhoneNumber], 
    [Extent1].[UserName] AS [UserName], 
    [Extent2].[AccountId] AS [AccountId], 
    [Extent2].[ClientId] AS [ClientId], 
    [Extent2].[BrokerId] AS [BrokerId], 
    [Extent2].[AccountNumber] AS [AccountNumber], 
    [Extent2].[BrokerUserName] AS [BrokerUserName], 
    [Extent2].[BrokerPasswordHash] AS [BrokerPasswordHash], 
    [Extent2].[EquityCurve] AS [EquityCurve], 
    [Extent2].[CapitalInvested] AS [CapitalInvested], 
    [Extent2].[IsSimulated] AS [IsSimulated], 
    [Extent2].[ClosedProfit] AS [ClosedProfit], 
    [Extent2].[MarketValueTimeStamp] AS [MarketValueTimeStamp], 
    [Extent2].[IsAuthorizedForLiveTrading] AS [IsAuthorizedForLiveTrading], 
    [Extent2].[ActivatedOn] AS [ActivatedOn], 
    CASE WHEN ([Extent2].[AccountId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[AspNetUsers] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Accounts] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ClientId]
)  AS [Project1]
ORDER BY [Project1].[UserName] ASC, [Project1].[Id] ASC, [Project1].[C1] ASC

Then seemingly one of these for each user:

SELECT TOP (1) 
[Extent1].[BrokerId] AS [BrokerId], 
[Extent1].[Name] AS [Name], 
[Extent1].[Code] AS [Code], 
[Extent1].[IsExternal] AS [IsExternal]
FROM [dbo].[Brokers] AS [Extent1]
WHERE [Extent1].[BrokerId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2

And also one of these for each user:

SELECT 
[Project2].[Id] AS [Id], 
[Project2].[ReferralId] AS [ReferralId], 
[Project2].[FirstName] AS [FirstName], 
[Project2].[LastName] AS [LastName], 
[Project2].[Address] AS [Address], 
[Project2].[Address2] AS [Address2], 
[Project2].[Zip] AS [Zip], 
[Project2].[City] AS [City], 
[Project2].[StateProvince] AS [StateProvince], 
[Project2].[Country] AS [Country], 
[Project2].[State] AS [State], 
[Project2].[startPopupChecked] AS [startPopupChecked], 
[Project2].[DateAdded] AS [DateAdded], 
[Project2].[CurrentPortfolioId] AS [CurrentPortfolioId], 
[Project2].[NotificationsEmailAddress] AS [NotificationsEmailAddress], 
[Project2].[NotificationMobileNumber] AS [NotificationMobileNumber], 
[Project2].[ReceivesEmailNotifications] AS [ReceivesEmailNotifications], 
[Project2].[ReceivesTextNotifications] AS [ReceivesTextNotifications], 
[Project2].[IsActive] AS [IsActive], 
[Project2].[HasSeen] AS [HasSeen], 
[Project2].[Email] AS [Email], 
[Project2].[EmailConfirmed] AS [EmailConfirmed], 
[Project2].[PasswordHash] AS [PasswordHash], 
[Project2].[SecurityStamp] AS [SecurityStamp], 
[Project2].[PhoneNumber] AS [PhoneNumber], 
[Project2].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
[Project2].[TwoFactorEnabled] AS [TwoFactorEnabled], 
[Project2].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
[Project2].[LockoutEnabled] AS [LockoutEnabled], 
[Project2].[AccessFailedCount] AS [AccessFailedCount], 
[Project2].[UserName] AS [UserName], 
[Project2].[BlockOrder_BlockOrderId] AS [BlockOrder_BlockOrderId], 
[Project2].[C1] AS [C1], 
[Project2].[UserId] AS [UserId], 
[Project2].[RoleId] AS [RoleId]
FROM ( SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[ReferralId] AS [ReferralId], 
    [Limit1].[FirstName] AS [FirstName], 
    [Limit1].[LastName] AS [LastName], 
    [Limit1].[Address] AS [Address], 
    [Limit1].[Address2] AS [Address2], 
    [Limit1].[Zip] AS [Zip], 
    [Limit1].[City] AS [City], 
    [Limit1].[StateProvince] AS [StateProvince], 
    [Limit1].[Country] AS [Country], 
    [Limit1].[State] AS [State], 
    [Limit1].[startPopupChecked] AS [startPopupChecked], 
    [Limit1].[DateAdded] AS [DateAdded], 
    [Limit1].[CurrentPortfolioId] AS [CurrentPortfolioId], 
    [Limit1].[NotificationsEmailAddress] AS [NotificationsEmailAddress], 
    [Limit1].[NotificationMobileNumber] AS [NotificationMobileNumber], 
    [Limit1].[ReceivesEmailNotifications] AS [ReceivesEmailNotifications], 
    [Limit1].[ReceivesTextNotifications] AS [ReceivesTextNotifications], 
    [Limit1].[IsActive] AS [IsActive], 
    [Limit1].[HasSeen] AS [HasSeen], 
    [Limit1].[Email] AS [Email], 
    [Limit1].[EmailConfirmed] AS [EmailConfirmed], 
    [Limit1].[PasswordHash] AS [PasswordHash], 
    [Limit1].[SecurityStamp] AS [SecurityStamp], 
    [Limit1].[PhoneNumber] AS [PhoneNumber], 
    [Limit1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
    [Limit1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
    [Limit1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
    [Limit1].[LockoutEnabled] AS [LockoutEnabled], 
    [Limit1].[AccessFailedCount] AS [AccessFailedCount], 
    [Limit1].[UserName] AS [UserName], 
    [Limit1].[BlockOrder_BlockOrderId] AS [BlockOrder_BlockOrderId], 
    [Extent2].[UserId] AS [UserId], 
    [Extent2].[RoleId] AS [RoleId], 
    CASE WHEN ([Extent2].[UserId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM   (SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[ReferralId] AS [ReferralId], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[LastName] AS [LastName], 
        [Extent1].[Address] AS [Address], 
        [Extent1].[Address2] AS [Address2], 
        [Extent1].[Zip] AS [Zip], 
        [Extent1].[City] AS [City], 
        [Extent1].[StateProvince] AS [StateProvince], 
        [Extent1].[Country] AS [Country], 
        [Extent1].[State] AS [State], 
        [Extent1].[startPopupChecked] AS [startPopupChecked], 
        [Extent1].[DateAdded] AS [DateAdded], 
        [Extent1].[CurrentPortfolioId] AS [CurrentPortfolioId], 
        [Extent1].[NotificationsEmailAddress] AS [NotificationsEmailAddress], 
        [Extent1].[NotificationMobileNumber] AS [NotificationMobileNumber], 
        [Extent1].[ReceivesEmailNotifications] AS [ReceivesEmailNotifications], 
        [Extent1].[ReceivesTextNotifications] AS [ReceivesTextNotifications], 
        [Extent1].[IsActive] AS [IsActive], 
        [Extent1].[HasSeen] AS [HasSeen], 
        [Extent1].[Email] AS [Email], 
        [Extent1].[EmailConfirmed] AS [EmailConfirmed], 
        [Extent1].[PasswordHash] AS [PasswordHash], 
        [Extent1].[SecurityStamp] AS [SecurityStamp], 
        [Extent1].[PhoneNumber] AS [PhoneNumber], 
        [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
        [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
        [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
        [Extent1].[LockoutEnabled] AS [LockoutEnabled], 
        [Extent1].[AccessFailedCount] AS [AccessFailedCount], 
        [Extent1].[UserName] AS [UserName], 
        [Extent1].[BlockOrder_BlockOrderId] AS [BlockOrder_BlockOrderId]
        FROM [dbo].[AspNetUsers] AS [Extent1]
        WHERE [Extent1].[Id] = @p__linq__0 ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[AspNetUserRoles] AS [Extent2] ON [Limit1].[Id] = [Extent2].[UserId]
)  AS [Project2]
ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC',N'@p__linq__0 int',@p__linq__0=346

Answer 1

With the entity framework query using SQL Server profiler I see one of these:

Then seemingly one of these for each user:

And also one of these for each user:

It's because you're most likely doing a combination of; not executing the query, Entity Framework has Lazy Loading enabled (which I recommend you turn off) and accessing navigation properties in a loop. It looks very much like the Entity-Framework N+1 Problem.

Non-executed the query:

var nonexecuted = _context.Users.OrderBy(u => u.UserName)           
    .Include(u => u.Accounts.Select(a => a.Broker))
    .Include(u => u.Roles);

Executed query:

var executed = _context.Users.OrderBy(u => u.UserName)           
    .Include(u => u.Accounts.Select(a => a.Broker))
    .Include(u => u.Roles)
    .ToList() // .AsEnumerable() etc...

With a non-executed query if you do:

var maxIndex = 4;
for(idx = 0; idx < maxIndex; idx ++)
{
  nonexecuted.Users.Skip(idx).Accounts.First()
}

Will execute a query for maxIndex times because it's lazy-loading loading a navigation property.

I highly suggest disabling lazying loading. It will force you to create good queries that only need what you want.

Depending on the number of Accounts, Users and Roles you have, you could be running into a Cartesian Product problem as well.

Answer 2

You are join 3 tables, fetch all data from tables, I think that you don't have any indexed column, and database is on another server

1) filter record because you don't need really all record

2) only select columns that you need, instead of u., a. that returns all columns data

3) add a index column for your database(you should use index column for columns that appear in your where clause most of the time)

and check your query execution time on your database server time to see it is not network latency

Answer 3

Have you traced the SQL that your second statement produces? It looks like it's going to load all the Users, Accounts, Brokers and Roles without doing any of the joining and filtering that you want. If these tables are large this could take time.

(EDIT: Sorry, I’ve just noticed that you have done this.)

I think that if you want to produce some SQL like your first statement, you're going to have to use linq. It's hard to know exactly what you need without access to your model, but I suspect it's going to look something like:

var users = 
    from user in context.Users
    join acc in Accounts on user.ID equals acc.ClientId
    join broker in Brokers on acc.BrokerId equals broker.brokerid
    join r in Roles on user.id equals r.userid into roles
    from role in roles.DefaultIfEmpty()
    where role.RoleId == 1
    select user;   // or whatever you want to select

return users.ToList();

The tricky part is the left outer join, and I'm not in a position to check my syntax at the moment, but I hope this helps.

Answer 4

You can use .FromSql and pass in the query you want. Or you can use a View (unless using EF Core of course) that encapsulates your query.

Honestly though, if performance is a concern you are much better off using something like Dapper or Chain. Even for basic CRUD operations EF is significantly slower than the other two.

Answer 5

I am unable to delete this question even though it was actually not a problem in the long run... I still had some code from when I was working on it that did separate queries and populated some of these fields... when I stripped out that code... the following query runs very fast (2 seconds):

public IQueryable<ApplicationUser> QueriableUsersList()
{
    return _context.Users.OrderBy(u => u.UserName)           
        .Include(u => u.Accounts.Select(a => a.Broker))
        .Include(u => u.Roles);
}