time of executing query nhibernate

Question

i have this query in nhibernate

var fdata = (from p in  _session.Query<WfTask>()
                     join d in _session.Query<WfTaskDetail>() on p.WfTaskDetail.Id equals d.Id
                     orderby p.ActionDate descending //(order)
                     where
                        (_session.Query<WfTask>().Any(x => x.Actor.Id == personid && x.Action != null
                                   && x.Action.Id == actionDic[statusId] && p.Id != x.Id && p.WfTaskDetail.Id==x.WfTaskDetail.Id && p.StepNo>=x.StepNo-1) || (p.Actor.Id == personid && p.Status == statusId && p.StepNo==1))
                                   && p.Owner.Id == personid && (actorId == new Guid() || p.Actor.Id == actorId)

                     select new DataList
                     {
                         WfTask = p,
                         Name = userService.GetFullName(p.Actor.Id),
                         PositionName = org.GetOrganizatinOfPerson(p.Actor.Id).Name,// p.PositionName
                         //Statusid = new Guid(),//grp.Where(x => x.WfTask.StepNo == maxStepNo && x.WfTask.DocId == p.WfTask.DocId).Select(x => x.WfTask.Status).FirstOrDefault(),
                         Actionid = actionDic[statusId]
                     });

            total = fdata.Count();
            var dataa = pageNumber > 0 ? fdata.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList() : fdata.Take(pageSize).ToList();

the problem is the time of executing the query that is very high. how can i solve this problem?

models and mappings:

public class WfTask 
{
    public virtual Guid Id { get; set; }
    public virtual Person Owner { get; set; }               
    public virtual Person Actor { get; set; }              
    public virtual Guid DocId { get; set; }                 
    public virtual int StepNo { get; set; }                 
    public virtual Guid WfInstanceId { get; set; }    
    public virtual Guid OwnerGroupId { get; set; }      
    public virtual Guid Status { get; set; }   

    public virtual Guid WfActionId { get; set; }            
    public virtual Guid WfStateId { get; set; }  
    public virtual Guid OwnerId { get; set; }             
    public virtual Guid ActorId { get; set; }             
    public virtual Guid WfTaskDetailId { get; set; }      

    public virtual string Comment { get; set; }             
    public virtual DateTime ActionDate { get; set; }        
    public virtual WfAction Action { get; set; }         
    public virtual WfState WfState { get; set; }           
    public virtual WfTaskDetail WfTaskDetail { get; set; }  
    public virtual bool ViewState { get; set; }            
    public virtual bool Deleted { get; set; }       
}
public WfTaskMap()
    {
        Table("WfTask");
        Id(m => m.Id).Column("Id");
        Map(m => m.WfInstanceId);
        Map(m => m.OwnerGroupId);
        Map(m => m.Status);
        Map(m => m.DocId);            
        Map(m => m.StepNo);
        Map(m => m.Comment).Length(500);
        Map(m => m.ActionDate);
        References(x => x.Action).Column("WfActionId").Cascade.All();
        References(x => x.WfState).Column("WfStateId").Cascade.All();
        References(x => x.WfTaskDetail).Column("WfTaskDetailId").Cascade.All();
        References(x =>     x.Owner).Column("OwnerId").ForeignKey("FK_Person_WfTask_OwnerId").Cascade.All();
        References(x => x.Actor).Column("ActorId").ForeignKey("FK_Person_WfTask_ActorId").Cascade.All();
        Map(m => m.ViewState);
        Map(m => m.Deleted);
    }
public class WfTaskDetail:IModel
{
     public virtual Guid Id { get; set; }
     public virtual Guid DocId { get; set; }            
     public virtual string Description { get; set; }    
     public virtual string Subject { get; set; }       
     public virtual string Type { get; set; }          
     public virtual Guid OrgVersionId { get; set; }     
     public virtual Guid WfVersionId { get; set; }      
     public virtual Guid DocTypeId { get; set; }        
     public virtual DateTime CreatedDate { get; set; }  

     public virtual WfVersion WfVersion { get; set; }   
     public virtual WfDocType DocType { get; set; }     

}
public WfTaskDetailMap()
    {
        Table("WfTaskDetail");
        Id(m => m.Id).Column("Id");
        Map(m => m.DocId);
        Map(m => m.Description).Length(1000);
        Map(m => m.Subject).Length(1000);
        Map(m => m.Type).Length(1000);
        Map(m => m.OrgVersionId);
        Map(m => m.CreatedDate);
        References(x => x.DocType).Column("DocTypeId").Cascade.All();
        References(x => x.WfVersion).Column("WfVersionId").Cascade.All();

    }

i run this query in sql server , it executes quickly i also want to know if there is a way to write the query in nhibernate criteria.

- you don't seem to be using `...join d in...`. Replace `actionDic[statusId]` with a variable which should be set before running the query. what does `actorId == new Guid()` mean ? Name and PositionName should involve processing. They should not be in the select, as they are only useful for info displayed on the current page. — jbl, Oct 30 '13 at 12:51

score 0 · Answer 1 · answered Oct 30 '13 at 08:36

Since we dont know your schema and mapping we cant efficiently help you.
I suggest that you go simpler and translate you linq query to sql query and run it.
if the runtimes are equals, it means that you need to maybe make better indexs on the database or devide the query in to 2 or more queries.

if not, it means that the linq query that you wrote does not meet your real life need, so you will have to re-build it.

score 0 · Answer 2 · answered Oct 30 '13 at 13:19

Do you have the SQL output NHibernate generates? Is it a huge bunch of SQL queries, or one big query?

Using LINQ .Count() like

total = fdata.Count();

on an IEnumerable with deferred execution is always bad, since it really counts by resolving the expression (possibly with DB access), instead of returning a known number of items, as List.Count; or Array.Length would do.

time of executing query nhibernate

2 Answers2