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 ?


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.