vendredi 7 août 2015

EF 5 cold query taking too long to execute

There has been a weird issue I have been facing with entity framework. I have inherited a project which I have to enhance and I am facing a weird issue. The first time the visit model is accessed and when the virtual property Reports is accessed, it takes like a minute to run. After that any subsequent times this property is called, it is fast(1-1.5 seconds). The models in question are as below:

//Visit model
public class Visit : ModelTrackingBase
{

    [Column("VISITDATE")]
    [RequiredDateTimeAttribute(ErrorMessage = "Date can't be blank")]
    public DateTime VisitDate { get; set; }

    [Column("STATUS")]
    public long Status { get; set; }

    [Column("OWNERID")]
    public long OwnerId { get; set; }

    [Column("COORDID")]
    public long? CoordinatorId { get; set; }

    [Column("REPORTID")]
    public long ReportId { get; set; }

    [Column("ASSETID")]
    [Range(1, long.MaxValue, ErrorMessage = "Asset can't be blank")]
    public long AssetId { get; set; }

    [Column("ASSETTYPE")]
    [MaxLength(50)]
    public string AssetType { get; set; }

    public virtual Report Report { get; set; }//this is the one that causes the lag

}

Then we have the model reports which is as follows:

//model reports
public abstract class Report : ModelTrackingBase
{
    [Column("TYPENAME")]
    [MaxLength(60)]
    public string TypeName { get; set; } 

    public virtual ICollection<Crewship> Crewships { get; set; }
    public IEnumerable<User> KeyPersonnel
    {
        get { return Crewships.Select(c => c.User); }
    }

    public virtual ICollection<Reviewship> Reviewships { get; set; }
    public IEnumerable<User> ReviewTeam
    {
        get { return Reviewships.Select(r => r.User); }
    }    
}

I will also show the subsequent models that the code gets data from:

public class ModelTrackingBase : EntityIdBase
{
    protected ModelTrackingBase()
    {
        CreatedAt = UpdatedAt = DateTime.Now;
    } 

    [Column("CREATEDAT")]
    public DateTime CreatedAt { get; set; }
    [Column("UPDATEDAT")]
    public DateTime UpdatedAt { get; set; } 
}

Then we have the reviewships and crewships models:

 public class Reviewship : ModelTrackingBase
{
    [Column("USERID")]
    public long UserId { get; set; }

    [Column("REPORTID")]
    public long ReportId { get; set; }

    [Column("NOTIFYADDED")]
    public DateTime? NotifyAdded { get; set; } 

    public virtual User User { get; set; }
    public virtual Report Report { get; set; }
}

Crewships is same except the notifyadded property. It takes a minute to access the property Visits.Report whenever that model is called to data-bind on the view-model, any subsequent calls are 1 second long. Any help is appreciated as this has been bugging me for a week now.

Aucun commentaire:

Enregistrer un commentaire