How to optimize the loading of 10 rows in a datatable?

Question

1.00/5 (1 vote)

See more:

I am working on a server side processing datatable which gets about 1000 fake/generic users data from the database and loads 10 rows per page. The way I have written my code right now, it takes at least 2 minutes just to show the first 10 rows.

I have debugged it to find the reason it is taking so long is because I am getting the Roles for each user and showing it on the datatable. When I comment out the getting the roles part and use an empty string and List, the datatable loads fast. But I have to get the Roles and I having a hard time to figure out how to do it. Can someone please help me out in this matter? I have been struggling with the SQL part for the longest time and no success so far.

What I have tried:

private async Task<IEnumerable<UserViewModel>> GetUserData(ApplicationDbContext db)
{
    var users = db.Users;
    var list = await GetUsers(db, users);
    var vmlist = list.Select(item => new UserViewModel()
    {
        ID = item.ID,
        Username = item.Username,
        FirstName = item.FirstName,
        LastName = item.LastName,
        Email = item.Email,
        TaxID = item.TaxID,
        TaxIdHash = item.TaxIdHash,
        IV = item.IV,
        TaxIDEncrypted = item.TaxIDEncrypted,
        //RoleList = new List<string>(),
        //Roles = "",

        // This is the part giving me issues
        RoleList = RoleRepository.GetRolesByUserId(UserManager, item.ID, false),
        Roles = string.Join(",", RoleRepository.GetRolesByUserId(UserManager, item.ID, false).ToArray()),
    });

    return vmlist;
 }

I am calling the GetUserData method in this UserGrid method.

public async Task<ActionResult> UserGrid(DataTablesViewModel param)
{
    using (var db = new Infrastructure.Data.ApplicationDbContext())
    {
        db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

    var data = await GetUserData(db);

    IQueryable<UserViewModel> filteredUsers = data.AsQueryable();        

    List<UserViewModel> displayedTasks = filteredUsers.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();

    int count = filteredUsers.Count();
    foreach (var item in displayedTasks){
        if (!String.IsNullOrEmpty(item.TaxID) && item.IV != null && item.TaxIDEncrypted != null)
        {
            if (item.RoleList.Any(x => x == UserRoles.Applicant))
            {
                string ssndecrypt = GenericHelper.DecryptPortalTaxID(item.IV, item.TaxIDEncrypted);
                string ssn = (ssndecrypt.Length == 11 ? ssndecrypt.Substring(7, 4) : ssndecrypt);
                item.TaxID = (ssndecrypt.Length == 11 ? String.Join("", item.TaxID.Substring(0, 7), ssn) : ssn);}
        }
        else
        {
            item.TaxID = String.Empty;
        }
        item.Delete = item.RoleList.Intersect(UserRoles.CanDelete).Count() > 0 ?
            "<a class=\"delete\"  data-url=\"" + Url.Action("DeleteUser", "Users") + "\" data-userid=\"" + item.ID + "\"data-username=\"" + item.Username + "\" href=\"#\">Delete User</a>"
            : String.Empty;
    }

    var result = GridHelper.GetData(displayedTasks);
    var json = Json(new
    {
        sEcho = param.sEcho,
        iTotalRecords = count,
        iTotalDisplayRecords = count,
        aaData = result
    },
JsonRequestBehavior.AllowGet);
    return await Task.FromResult(json);
}

I will need the RoleList because I use it later for decrypting the encrypted SSN and showing its last 4 numbers.

This is the UserViewModel if needed.

public UserViewModel(IList<string> roles, ApplicationUser user)
        {
            ID = user.Id;
            Username = user.UserName;
            Email = user.Email;
            RoleList = roles.ToList(); ;
            Roles = string.Join(",", RoleList.ToArray());
            IsActive = user.IsActive;

            AllowRoleChanges = !RoleList.Intersect(UserRoles.FinalConsumers).Any();
            AssignableRoles = HousingServices.Core.Models.UserRoles.CanCreate.Select(x => new SelectListItem { Text = x, Value = x }).ToList();
            if (RoleList.Contains(UserRoles.Caseworker))
            {
                AssignableRoles.Add(new SelectListItem { Value = UserRoles.Caseworker, Text = UserRoles.Caseworker });
            }
            RoleIDs = RoleList.Select(x => x).ToList();
          ..........

        }
        
    }

private async Task<IEnumerable<dynamic>> GetUsers(ApplicationDbContext db, IDbSet<ApplicationUser> users)
        {
            IQueryable<ApplicationUser> userlist = users;
          <pre>var hsuserlist = userlist.Join(db.EliteHousingServicesUsers, u => u.Id, hu => hu.UserId, (u, hu) => new
            {
                ID = u.Id,
                Username = u.UserName,
                FirstName = hu.FirstName,
                LastName = hu.LastName,
                Email = u.Email,
                TaxID = "XXX-XX-XXXX",
                Active = (u.IsActive ? "Yes" : "No"),
                TaxIdHash = hu.TaxIDHash,
                IV = hu.IV,
                TaxIDEncrypted = hu.TaxIDEncrypted,
                IsActive = u.IsActive,
                Status = (u.AccessFailedCount >= MaxFailedAccessAttemptsBeforeLockout ?
                "<a class=\"unlock\" data-url=\"" + unlockUrl + "\" data-userid=\"" + u.Id + "\"data-username=\"" + u.UserName + "\" href=\"#\">Unlock</a>"
                : "<a class=\"reset\" data-url=\"" + resetUrl + "\" data-userid=\"" + u.Id + "\"data-username=\"" + u.UserName + "\" href=\"#\">Reset Password</a>"),
            });

Posted 26-Apr-17 15:34pm

Truecolors

Updated 27-Apr-17 4:43am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2017-04-26T16:47:00

Solution 1

Well, I don't know what the Roles have to do with your code, but you can save a ton of time by restructuring your database a bit. The SSN has to be decrypted to get the last 4 digits, the least secure part of the string. So, don't decrypt it. Store the last 4 as a separate column and retrieve that instead of the full SSN. You get to skip the entire decrypt operation, saving yourself a ton of time.

Posted 26-Apr-17 16:47pm

Dave Kreskowiak

Comments

[no name] 26-Apr-17 22:52pm

I cannot do that with the SSN. The SSNs are hashed in the database. I am trying to just get the Roles for each user. Cannot change anything with the SSN part.

PIEBALDconsult 26-Apr-17 23:05pm

If you're a developer you can do anything.

[no name] 26-Apr-17 23:08pm

I meant I cannot modify that part of the project. And I am not a "Dev" as per say... just an intern.

Dave Kreskowiak 26-Apr-17 23:44pm

OK, so then comment out those three lines of code that decrypt the SSN and put dummy data, just a simple string, into item.TaxID. You're intentionally skipping the decryption phase for a performance test.

Run the code. If the datatable comes back fast now, you know where the problem is and you just said you can't do anything about it.

[no name] 27-Apr-17 8:57am

The problem of it running slow is not in the decrypting part. It is in the getting the roles for each user part.

Dave Kreskowiak 27-Apr-17 10:18am

OK, so there's a bunch of possible sources of problems. From the code, it looks like you might be hitting the database again and again for each item in your loop. Such as...

    if (item.RoleList.Any(x => x == UserRoles.Applicant))

Did you load all the possible Roles into the context before hand so you already have them loaded? Did you Include the Roles with the items you loaded or are they lazy loaded when you hit this if statement? If they are lazy loaded, that's a round trip to the database for every item in your loop.

Back to your GetUserData method, you've got a second call to the database to GetRolesByUserId. This is another round trip to the database. It should be an .Include on the original query. This could be a deferred execution query that is executed when the list is needed in your loop, NOT when you return the vmUsers list.

I get the feeling you're not familiar with how EF does lazy loading and deferred execution of queries. This could be what's tripping your code up as it's probably hitting the database multiple times throughout your loop code.

[no name] 27-Apr-17 10:25am

Yes, I am not very familiar with EF lazy loading and the process of executing queries. I asked my mentor if the roles are already in the database, she said yes. And she also said I don't have to use RoleRepository. But I am having a hard time to work around this issue.

Dave Kreskowiak 27-Apr-17 10:33am

Yeah, the roles are in the database. The problem is they are not loaded into your code except when needed. That's the performance problem. Your code is loading only the roles associated with a single item only when needed. That results in another round trip to the database on every item. You need to rewrite this code to load the Users and their roles in one query. That's where the .Include comes in on your GetUsers method.

https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx

[no name] 27-Apr-17 15:08pm

In my GetUsers method, I did the .Include for user roles like this,

var hsuserlist = userlist.Join(db.EliteHousingServicesUsers.Include(r => r.User.Roles.Select(p => p.RoleId)), u => u.Id, hu => hu.UserId, (u, hu) => new

and tried to call it in the GetUserData method like this

Roles = string.Join(",", item.Roles).ToString()

It is loading the table fast, but it is still not showing the Roles? And in the roles column, it is showing something like System.Data.Entity.Proxies.IdentityUserRole _ and bunch of letters and numbers. I don't know what's going on?

Dave Kreskowiak 27-Apr-17 15:16pm

You're getting the proxy objects because it hasn't loaded the Roles.

The Include should just contain the association, not a specific item from the table.

If you have a table of Users where each User has a collection of Roles they are members of, you could return the entire table of Users with all of their Roles in a single query:

    var users = myContext.Users.Include(x => x.Roles);

For a single user:

    var user = myContext.Users.Include(x => x.Roles).Where(x => x.UserId == idToFind).SingleOrDefault();

[no name] 27-Apr-17 15:58pm

I am trying to create a method GetRolesByUserId to get the list of Role names for each user (RoleList), and so I don't have to use the RoleRepository method.

Are you able to assist me with the query part? Please, a humble request.

I have 2 tables, IdentityUserRole table which has UserId and RoleId,
and then IdentityRoles table which has the RoleId, and the RoleName.

My RoleList is a List of string, so I am trying to write this query in this method, if you can please help me. I am trying hard but have had no success for the past 3 days.

public static IEnumerable<string> GetRolesByUserId(string userId)
        {
            ApplicationDbContext db = new ApplicationDbContext();

            return db.Set<IdentityUserRole>().Where(r => r.UserId.Contains(userId)).Select(x => x.RoleId);
        }

I just want to get the RoleList populated.

Dave Kreskowiak 27-Apr-17 16:18pm

There's a problem with what you're doing just on those two lines of code.

You're returning a deferred query, NOT a list of items. The query hasn't run yet, not until the caller starts manipulating the objects this query is supposed to return. The problem is your context falls out of scope and will be Disposed, orphaning your returned query. When you go to use the query result, it'll fail because the context is came from doesn't exist any more.

This is going to require a considerable rewrite of your code to fix all of these issues.

I normally do a "Services" class, some people call it a "Repository", that deals with a specific database table or set of tables as defined by business rules. This service class is Disposable, and maintains a class-level instance of the DbContext it deals with. All of the methods to get/insert/update/delete objects reside in this class and use the same DbContext object supplied by the class. In the class Dispose method, the DbContext instance is disposed of, cleaning up resources.

Read this -> https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

It's a bit old, but it still demonstrates the concept.