I have a service called "Roles" allowing me to retrieve all 'items' on which I have permissions. I'm experiencing a interesting performance issue with NHibernate
3.4 (.NET 3.5).
Example:
var items = service.Roles.GetAccessibleEntities<Staff>(MyRoles);
Here is the implementation of this method:
public IDictionary<TEntity, List<Role>> GetAccessibleEntities<TEntity>(Detail detail)
where TEntity : ISecuredEntity
{
if (detail == null || !detail.Roles.Any())
return new Dictionary<TEntity, List<Role>>();
var admin = detail.Roles.FirstOrDefault(x => x.IsSysAdmin);
if (admin != null) // If admin we return all objects
return _context.Set<TEntity>()
.ToDictionary(k => k, v => new List<Role>(new[] { admin }));
(...)
var pred = ScopeTranslator<TEntity>.Translate(scopes);
return pred == null ?
new Dictionary<TEntity, List<Role>>() :
_context.Find<TEntity>(pred)
.ToDictionary(k => k, v => new List<Role>(SecurityService.GetEligibleRoles(detail, v)));
}
The find method returns an IQueryable
object.
The method will return a Dictionary<Staff, List<Role>>
containing the staff object and the role(s) allowing me to access it.
Once that done, I want to flatten this collection of objects in order to return it as a JSON string:
var objs = items.Where(x => x.Key.IsActive).Select(x => new
{
EmployeeNumber = x.Key.EmployeeNumber,
FullName = x.Key.Detail.FullName,
Company = x.Key.CompanyId.HasValue ? x.Key.Company.Name : "N/A",
Branch = x.Key.BranchId.HasValue ? x.Key.Branch.Name : "N/A",
Department = x.Key.DepartmentId.HasValue ? x.Key.Department.Name : "N/A",
Team = x.Key.TeamId.HasValue ? x.Key.Team.Name : "N/A",
}).ToList();
For 3800 records, the operation takes around 20 seconds! I think that NHibernate
is loading all the staff objects separately and thus is performing 3800 "select" queries.
Is there a way to group queries? Do you have any suggestions, ideas, or comments to help me?
.Select(...)
bit? My suspicion is that the combination of that and the.ToList()
call are what are hurting you. I think the anonymous type that NHibernate and LINQ have to generate is what is dragging performance. – EBrown Jul 30 at 15:31