Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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?

share|improve this question
1  
You say "I think that NHibernate is loading all the staff objects separately". Why not research this first? IMHO this question is off-topic anyway, since there really is no code to review. –  BCdotWEB Jul 30 at 12:19
    
How large is the data set it is querying? Also, have you tried comparing performance with and without the .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
    
I have the confirmation. NHibernate is loading all the object separately. If i change my API to eager load some properties, the performance issue disappears. Just for the records, you can use Query<Entity>().Fetch().Future or QueryOver<Tentity>.Fech().Eager. –  Sébastien Jul 31 at 3:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.