CODEWITHSUNDEEP
c#sqllinq
David Létourneau
David Létourneau

Reputation: 1250

Why is my linq to SQL query very slow?

I got this query that is very slow in a production environment. I would like to know why and if there is a better way to achieve the same result.

public async Task<Membership> FindByEmailByAccessL1OrL3OrL4Async(string email)
{
    return await (from m in this.Queryable()
                  where m.Email == email
                        && (m.RoleMemberships.Select(r => r.RoleId).Contains(RoleConstants.ACCESSGRANTEDL1ID)
                            || m.RoleMemberships.Select(r => r.RoleId).Contains(RoleConstants.ACCESSGRANTEDL3ID)
                            || m.RoleMemberships.Select(r => r.RoleId).Contains(RoleConstants.ACCESSGRANTEDL4ID))
                  select m).SingleOrDefaultAsync();
}

In short, the purpose of this query is to get a user membership by email and proceed to a login. More I got user memberships, more this query will be slow.

Thank you,

David

Edit

this.Queryable() is the equivalent of doing select * from Memberships. Membership table has many roles, and a role has many memberships.

Also, memberships table takes up to 12 seconds to get the login information for 240 rows. I must filtering by role, because the email value isn't unique in the system. Only some roles have access to login and those roles will have unique email. This is why I have to filter. This query returns one membership to proceed to login, that has been selected with the query above. So no IQueryable, IEnumerable or List, just the SingleOrDefault value.

Upvotes: 0

Views: 891

Answers (2)

Zein Makki
Zein Makki

Reputation: 30052

I think the generated query is very bad, since you're doing the same thing multiple times which is:

m.RoleMemberships.Select(r => r.RoleId)

Instead, you can do that once using:

public Task<Membership> FindByEmailByAccessL1OrL3OrL4Async(string email)
{
    return (from m in this.Queryable()
            where m.Email == email
              && (m.RoleMemberships.Any(
                    r => r.RoleId == RoleConstants.ACCESSGRANTEDL1ID 
                      || r.RoleID == RoleConstants.ACCESSGRANTEDL3ID 
                      || r.RoleID == RoleConstants.ACCESSGRANTEDL4ID)
            select m).SingleOrDefaultAsync();
}

The above would make things better I guess. You can check the generated query in both cases using the debugger.

Also note that you don't need to await the result, just return the task and the caller would need to await for it.

Upvotes: 3

Vlad Bayrak
Vlad Bayrak

Reputation: 39

I think that there is much of data to load and EF is very slow mapping technology which creates interesting queries (you can see this here).Do you have problems with performance?Maybe you should choose other mapping technology (stacks dapper for example Dapper). When we made tests, it was in two times faster than EF.

Upvotes: 0

Related Questions