title: ASP.NET Membership database and performance
body: <p>We're writing a custom admin page for our ASP.NET MVC 3 app and we're surprised by how long it takes to grab users from the aspnet "application services" database. </p>
  
  <p>The built-in Membership.GetAllUsers() method is painfully slow, but understandably so since it fetches a large quantity of info for each user. We don't need that much so we decided to throw a LINQ to SQL layer on top of our aspnet db and write our own queries using LINQ. Instead of fetching everything, we just get a couple columns - the only ones we're interested in.  </p>
  
  <p>The results are better, but still: it takes about 30 seconds to fetch a bit more than 120 users with the standard method versus 9 seconds with our custom query.</p>
  
  <p>9 seconds for 150 rows seems huge. Any idea why it takes so long ?</p>
  
  <p><strong>EDIT</strong> Here's an example of a method that takes 9 seconds to return something:</p>
  
  <pre><code>    public static List&lt;LightMembershipUser&gt; GetUsersInRole(string roleName) {
          //Find the role
          var roleId =   (from role in db.Roles
                          where roleName == role.RoleName
                          select role.RoleId)
                          .First();
          if (roleId==null){ return null; }
  
          var res =   from u in db.Users 
                      join ur in db.UsersInRoles on u.UserId equals ur.UserId
                      join m in db.Memberships on u.UserId equals m.UserId
                      where  ur.RoleId==roleId
                      select new LightMembershipUser { UserName = u.UserName, CreationDate = m.CreateDate, Email = m.Email, Roles = Roles.GetRolesForUser(u.UserName) };
          return res.ToList();
      }
  </code></pre>