David Létourneau David Létourneau - 15 days ago 4
SQL Question

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.

Answer

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:

static 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.