Poor performance during role deletion #9167

tom.ohle created 4 years ago

The RoleAppService.DeleteRole method takes a very long time when the role to be deleted is currently assigned to a large number of users. In my case it is assigned to 20,000 users, and the call that takes the longest is UserManager.RemoveFromRoleAsync as you can see in the screenshot below.

It seems like the call to UserManager.RemoveFromRoleAsync decreases in performance when there are more than a small number of users assigned to the role (ie. the performance is drastically better when less than a hundred users are assigned).

Is this a known issue? Is there a recommended approach for working around this issue, or should I start thinking about how to come up with my own solution?

This behavior can be reproduced by dropping the following method into an application service in a brand new ASP.NET Zero 8.7.0 project (ASP.NET Core & Angular).

public void CreateTestUsers()
{
    // create 20000 test users
    
    var context = CurrentUnitOfWork.GetDbContext<MyDbContext>();
    
    for (var i = 0; i < 20000; ++i)
    {
        var first = $@"first{i}";
        var last = $@"last{i}";
        var userName = $@"{first}.{last}";
        var emailAddress = $@"{userName}@dummy.com";
        
        var user = new User
        {
            EmailAddress = emailAddress,
            IsEmailConfirmed = true,
            Name = first,
            Surname = last,
            UserName = userName,
            Password = "AM4OLBpptxBYmM79lGOX9egzZk3vIQU3d/gFCJzaBjAPXzYIK3tQ2N7X4fcrHtElTw==", //123qwe
            TenantId = 1,
        };

        user.SetNormalizedNames();
        context.Users.Add(user);
    }
    
    context.SaveChanges();
    
    // assign users to test role
    
    var userIds = context.Users.Select(user => user.Id).ToList();
    
    foreach (var userId in userIds)
    {
        context.UserRoles.Add(new UserRole
        {
            RoleId = 6,
            UserId = userId,
            TenantId = 1,
            CreationTime = DateTime.Now,
            CreatorUserId = 1,
        });
    }
    
    context.SaveChanges();
}

Thanks for reading!

14 Answer(s)

0
maliming created 4 years ago
Support Team
hi

You can try to delete the user's role using the following code. It is the design of the framework to load all roles when deleting user roles.

await _userRoleRepository.DeleteAsync(r => r.RoleId == role.Id && r.UserId == user.Id);
0
tom.ohle created 4 years ago
Hi maliming,

I am assuming what you meant is to replace this....

foreach (var user in users) { CheckErrors(await UserManager.RemoveFromRoleAsync(user, role.Name)); }

...with this...

foreach (var user in users) { await _userRoleRepository.DeleteAsync(r => r.RoleId == role.Id && r.UserId == user.Id); }

If so, does that mean calls to CheckErrors and UserManager.RemoveFromRoleAsync are not important? Do we run the risk of introducing a regression by using the lower level approach?
0

maliming created 4 years ago
Support Team

hi @tom.ohle

You can view the implementation source code of UserManager.RemoveFromRoleAsync, I just implemented it in a different way.

Do we run the risk of introducing a regression by using the lower level approach?

I don't think so. This is the handling of special cases.
0

tom.ohle created 4 years ago

Thanks!
0
tom.ohle created 3 years ago
Hi maliming,

We tried the approach below but we are still seeing the same performance issue on a brand new ASP.NET Zero 8.7.0 project (ASP.NET Core & Angular).

foreach (var user in users) { await _userRoleRepository.DeleteAsync(r => r.RoleId == role.Id && r.UserId == user.Id); }

We are currently looking for the root cause of this issue, but in the meantime we would appreciate your thoughts on how we might resolve this issue.

maliming created 3 years ago

Support Team

Can you use stopwatch to confirm which method call has performance issues?

You can also use the following code instead of for loop deletion.

var users = await UserManager.GetUsersInRoleAsync(role.Name);
var userId = users.Select(x => x.Id).ToList();
await _userRoleRepository.DeleteAsync(r => r.RoleId == role.Id && userId.Contains(r.UserId));

0

tom.ohle created 3 years ago

Hi maliming,

We tried the suggested code you provided above and added a stopwatch in between the calls. The first 2 statements returned after 3.6 seconds, but the call to DeleteAsync() still does not/has not returned. We are trying to delete over 20,000 records.

Let us know if there is anything you need us to confirm.
0
maliming created 3 years ago
Support Team
hi

The UserRole entity table already has an index, you can check the generated SQL, and try to manually execute the database to see the execution speed. Or you can disable the transaction of the unit of work which will also provide performance.

https://aspnetboilerplate.com/Pages/Documents/Unit-Of-Work

using (var uow = _unitOfWorkManager.Begin(new UnitOfWorkOptions { Scope = TransactionScopeOption.RequiresNew, IsTransactional = false })) { ///..... uow.Complete(); }

modelBuilder.Entity<UserRole>(b => { b.HasIndex(e => new { e.TenantId, e.UserId }); b.HasIndex(e => new { e.TenantId, e.RoleId }); });

tom.ohle created 3 years ago

Hi maliming,

We generated the SQL calls that were produced as a result of the call to DeleteAsync. We manually executed a subset (~1000 statements) against the database using the EntityFrameworkCore ExecuteSqlRaw method just to test out the execution speed as suggested. Performing the sql against 1000 updates took about 23.94 seconds. We also tried encapsulating our DeleteAsync call within a unit of work as suggested with transactions disabled and received this timing with the following code:

output:

***** RoleAppService.DeleteRole ****** DeleteRole Started - 7556.8653 Retreived Users - 7653.065 Selected UserIds - 8804.0222 After DeleteAsync - 9871961.5467

code:

public async Task DeleteRole(EntityDto input) {

        var role = await _roleManager.GetRoleByIdAsync(input.Id);
        var s = Stopwatch.StartNew();
        Debug.WriteLine("***** RoleAppService.DeleteRole ******");
        Debug.WriteLine("DeleteRole Started - " + s.Elapsed.TotalMilliseconds);

        var users = await UserManager.GetUsersInRoleAsync(role.Name);

        Debug.WriteLine("Retreived Users - " + s.Elapsed.TotalMilliseconds);
        var userId = users.Select(x => x.Id).ToList();
        
        Debug.WriteLine("Selected UserIds - " + s.Elapsed.TotalMilliseconds);

        using (var uow = UnitOfWorkManager.Begin(new UnitOfWorkOptions
        {
          Scope = TransactionScopeOption.RequiresNew,
          IsTransactional = false
        }))
        { 
          await _userRoleRepository.DeleteAsync(r => r.RoleId == role.Id && userId.Contains(r.UserId));
       
          uow.Complete();   
        }

        Debug.WriteLine("After DeleteAsync - " + s.Elapsed.TotalMilliseconds);
        CheckErrors(await _roleManager.DeleteAsync(role));
       
    }

The DeleteAsync is still taking quite a bit of time. Is there anything else we can do to improve the performance of the DeleteAsync call? We are deleting a role with 20,126 users assigned to it.

0

maliming created 3 years ago
Support Team

hi tom.ohle

The GetUsersInRoleAsync method returns the User entity collection. This method can be optimized to query only the user's Id collection . But this method is not too slow.

I am not sure that the database execution DeleteAsync(r => r.RoleId == role.Id && userId.Contains(r.UserId)) will be so slow.
0

tom.ohle created 3 years ago

Hi maliming,

Perhaps something is being lost in translation. To reproduce the steps for our issue, we have created a new test project based off of the 8.7 aspnetzero project. We created a new role and then imported 20,000 users using the excel import on the Administration -> Users page with that role. We then try to delete that role from the Administration -> Roles page. This is where we are getting our issue.

Can you let me know how I can forward you this test project and our excel file of 20,000 test users with our test role to create those users?

Thanks
0

maliming created 3 years ago
Support Team

hi

You can send to my email: [email protected]
0

maliming created 3 years ago
Support Team

hi tom.ohle

I will check it on the weekend. : )
0
maliming created 3 years ago
Support Team
hi tom.ohle

EF Core will load the entity you want to delete by default, this performance is very low, you can try the raw SQL. If the userid is too long, you can consider to execute SQL statements in groups.

await _userRoleRepository.DeleteAsync(r => r.RoleId == role.Id && userId.Contains(r.UserId));

var sql = $"DELETE FROM [AbpUserRoles] WHERE RoleId = {role.Id} AND UserId IN ({string.Join(",", userId)})"; await _userRoleRepository.GetDbContext().Database.ExecuteSqlRawAsync(sql);

https://github.com/zzzprojects/EntityFramework-Extensions

EntityFramework-Extensions has a similar DeleteFromQuery method, but it is a PRO version.

https://github.com/zzzprojects/EntityFramework-Extensions https://entityframework-extensions.net/delete-from-query

Have an answer to this question? Log in and write your answer.