Base solution for your next web application
Open Closed

Performance issue getting users with a specific permission #279

User avatar
0
guillaumemorin created

I need to make an Application Service method that returns a list of users which have a specific Permission granted.

I'm injecting an IPermissionChecker in my app service.

When I try to call IPermissionChecker.IsGranted inside my LINQ query (before the SQL query is actually executed), I get an exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean IsGranted(Abp.Authorization.IPermissionChecker, Int64, System.String)' method, and this method cannot be translated into a store expression.

ex:

var list  = _userRepository.GetAll()
	.Where(x => x.IsActive && !x.IsDeleted)
	.Where(x => _permissionChecker.IsGranted(x.Id, PermissionNames.CanBeAssigneeOnTask))
	.ToList();

So instead, I tried to first load all users into memory, then call IPermissionChecker.IsGranted() for each users.

foreach (AppUser appUser in list)
{
	if (_permissionChecker.IsGranted(appUser.Id, PermissionNames.CanBeAssigneeOnTask))
	{
		...
	}
}

That works but each calls to IPermissionChecker.IsGranted() issues SEVEN different sql queries (I'm monitoring with SQL Profiler). So if I have 100+ users * 7 queries = 700+ queries... it is getting very slow to execute.

Is there any way to optimize this ?

Go to accepted answer
5 Answer(s)
  • User Avatar
    0
    hikalkan created
    Support Team

    Hi,

    This is a known performance issue which has a high priority: <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate/issues/466">https://github.com/aspnetboilerplate/as ... issues/466</a> I'll optimize it in a short time.

  • User Avatar
    0
    guillaumemorin created

    Thanks for the insight.

    For now, I did optimize using this 'hacky' approach. I inject an IRepository<RolePermissionSetting, long>, and get the list of roles which have the permission. Total resulting queries = 2. ** I'm using Permissions only at role level. No user specific permissions.

    // get list of roleid that have the permission
var roleIds = _rolePermissionSettingRepository.GetAll()
	.Where(x => x.Name == PermissionNames.CanBeAssigneeOnTask)
	.Select(x => x.RoleId)
	.ToList();

var query  = _userRepository.GetAll()
	.Where(x => x.IsActive && !x.IsDeleted)
	.Where(x => x.Roles.Any(y => roleIds.Contains(y.RoleId)));
  • User Avatar
    0
    hikalkan created
    Support Team

    Thanks.

  • User Avatar
    0
    guillaumemorin created

    Since the Caching feature is now implemented, I did retry to call permissionChecker.IsGranted() inside a loop. There is still 5 SQL query issued for EACH iterations. So the caching does not seems to work.

    I've isolated the problem in AbpMemoryCache.cs, in the method Set():

    public override void Set(string key, object value, TimeSpan? slidingExpireTime = null)
{
	//TODO: Optimize by using a default CacheItemPolicy?
	_memoryCache.Set(
		key,
		value,
		new CacheItemPolicy
		{
			SlidingExpiration = slidingExpireTime ?? DefaultSlidingExpireTime
		});

	var test = _memoryCache.Get(key);
}

    Here I added a Get() right after the Set(), and the value returned is ALWAYS null. In my case:

    • key = a number in string ex: "1" , "2", ...
    • value = an instance of UserPermissionCacheItem
    • slidingExpireTime is null so it takes DefaultSlidingExpireTime which is 1 hour

    I can't understand why the memoryCache.Get() returns null right after the Set().

    Any ideas ?

  • User Avatar
    0
    hikalkan created
    Support Team

    It's very strange. MemoryCache is .NET's standard class. It seems it's not working for your case :S Can you create an issue on Github to track it. Thanks.

Assignee
No one
Labels
None yet