Iterating through datacontext - most efficient way?

Question

I have started using performance wizard in visual studio 2012 because there was a slow method which is basically used to get all users from the datacontext. I fixed the initial problem but I am now curious if I can make it faster.

Currently I am doing this:

public void GetUsers(UserManagerDashboard UserManagerDashboard)
{
    try
    {
        using (GenesisOnlineEnties = new GenesisOnlineEntities())
        {
            var q = from u in GenesisOnlineEnties.vw_UserManager_Users
                    select u;

            foreach (var user in q)
            {
                User u = new User();
                u.UserID = user.UserId;
                u.ApplicationID = user.ApplicationId;
                u.UserName = user.UserName;
                u.Salutation = user.Salutation;
                u.EmailAddress = user.Email;
                u.Password = user.Password;
                u.FirstName = user.FirstName;
                u.Surname = user.LastName;
                u.CompanyID = user.CompanyId;
                u.CompanyName = user.CompanyName;
                u.GroupID = user.GroupId;
                u.GroupName = user.GroupName;
                u.IsActive = user.IsActive;
                u.RowType = user.UserType;
                u.MaximumConcurrentUsers = user.MaxConcurrentUsers;
                u.ModuleID = user.ModuleId;
                u.ModuleName = user.ModuleName;

                UserManagerDashboard.GridUsers.users.Add(u);
            }
        }
    }
    catch (Exception ex)
    {
    }
}

Performance report from VS2012

It's a very straight forward method. Connect to the database using entity framework, get all users from the view "vw_usermanager_users" and populate the object which is part of a collection.

I was casting ?int to int and I changed the property to ?int so no cast is needed. I know that it is going to take longer because I am looping through records. But is it possible to speed this query up?

Side note: don't `catch (Exception ex) { }`. You will regret it later! — Iain Galloway, Mar 05 '14 at 13:14

score 3 · Answer 1 · edited May 23 '17 at 10:31

Ok, first things first, what does your vw_UserManager_Users object look like? If any of those properties you're referencing are navigational properties:-

public partial class UserManager_User
{
  public string GroupName { get { return this.Group.Name; } }

  // See how the getter traverses across the "Group" relationship
  // to get the name?
}

then you're likely running face-first into this issue - basically you'll be querying your database once for the list of users, and then once (or more) for each user to load the relationships. Some people, when faced with a problem, think "I know, I'll use an O/RM". Now they have N+1 problems.

You're better to use query projection:-

var q = from u in GenesisOnlineEnties.vw_UserManager_Users
        select new User()
        {
          UserID = u.UserId,
          ApplicationId = u.ApplicationID,
          GroupName = u.Group.Name, // Does the join on the database instead
          ...
        };

That way, the data is already in the right shape, and you only send the columns you actually need across the wire.

If you want to get fancy, you can use AutoMapper to do the query projection for you; saves on some verbosity - especially if you're doing the projection in multiple places:-

var q = GenesisOnlineEnties.vw_UserManager_Users.Project().To<User>();

Next up, what grid are you using? Can you use databinding (or simply replace the Grid's collection) rather than populating it one-by-one with the results from your query?:-

UserManagerDashboard.GridUsers.users = q.ToList();

or:-

UserManagerDashboard.GridUsers.DataSource = q.ToList();

or maybe:-

UserManagerDashboard.GridUsers = new MyGrid(q.ToList());

The way you're adding the users to the grid right now is like moving sand from one bucket to another one grain at a time. If you're making a desktop app it's even worse because adding an item to the grid will probably trigger a redraw of the UI (i.e. one grain at a time and, describing every grain in the bucket to your buddy after each one). Either way you're doing unnecessary work, see what methods your grid gives you to avoid this.

How many users are in the table? If the number is very large, then you'll want to page your results. Make sure that the paging happens on the database rather than after you've got all the data - otherwise it kind of defeats the purpose:-

q = q.Skip(index).Take(pageSize);

though bear in mind that some grids interact with IQueryable to do paging out-of-the-box, in that case you'd just pass q to the grid directly.

Those are the obvious ones. If that doesn't fix your problem, post more code and I'll take a deeper look.

Thanks for your suggestions. I will let you know how things go with your suggestions. — nick gowdy, Mar 05 '14 at 14:36

score 1 · Answer 2 · answered Mar 05 '14 at 12:57

Yes, by turning off change tracking:

var q = from u in GenesisOnlineEnties.vw_UserManager_Users.AsNoTracking()
   select u;

Unless you are using all the properties on the entity you can also select only the columns you want.

var q = from u in GenesisOnlineEnties.vw_UserManager_Users.AsNoTracking()
   select new User
   {
       UserId = u.UserId,
       ...
   }

Iterating through datacontext - most efficient way?

2 Answers2