title: Why is my linq to SQL query very slow?
body: <p>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.</p>
  
  <pre><code>public async Task&lt;Membership&gt; FindByEmailByAccessL1OrL3OrL4Async(string email)
  {
      return await (from m in this.Queryable()
                    where m.Email == email
                          &amp;&amp; (m.RoleMemberships.Select(r =&gt; r.RoleId).Contains(RoleConstants.ACCESSGRANTEDL1ID)
                              || m.RoleMemberships.Select(r =&gt; r.RoleId).Contains(RoleConstants.ACCESSGRANTEDL3ID)
                              || m.RoleMemberships.Select(r =&gt; r.RoleId).Contains(RoleConstants.ACCESSGRANTEDL4ID))
                    select m).SingleOrDefaultAsync();
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>Thank you,</p>
  
  <p>David </p>
  
  <p><strong>Edit</strong></p>
  
  <p><code>this.Queryable()</code> is the equivalent of doing <code>select * from Memberships</code>.
  <code>Membership</code> table has many roles, and a role has many memberships.</p>
  
  <p>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 <code>IQueryable</code>, <code>IEnumerable</code> or <code>List</code>, just the <code>SingleOrDefault</code> value.</p>