Entity Framework (6) Performance Optimisation advice

.net c# entity-framework entity-framework-6 performance

Question

My application has an ADO.Net Data Access layer that employs fundamental ADO.Net along with CRUD stored procedures (one for each operation, such as Select myTable and Insert myTable). As you may imagine, a big system (like ours) calls for a lot of DB objects for the DA layer.

I've been considering whether I could convert the layer classes into EF POCO classes. Although I was able to achieve this, it becomes horrendous when I try to performance test. 100000 runs of data loading using the class below (create object, set Key to desired value, call dataselect) only takes approximately 47 seconds (there are only a handful of records in the DB). The Stored Proc approach needs roughly 7 seconds, however.

I'm seeking for suggestions on how to improve this, but I cannot alter the functionality that layer exposes; I can only alter how it implements the methods (for example, I cannot transfer context ownership to the BO layer).

Thanks

public class DAContext : DbContext
{
    public DAContext(DbConnection connection, DbTransaction trans)
        : base(connection, false)
    {
        this.Database.UseTransaction(trans);

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        //Stop Pluralising the Object names for table names.
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        //Set any property ending in "Key" as a key type.
        modelBuilder.Properties().Where(prop => prop.Name.ToLower().EndsWith("key")).Configure(config => config.IsKey());

    }

    public DbSet<MyTable> MyTable{ get; set; }
}

public class MyTable : DataAccessBase
{

    #region Properties

    public int MyTableKey { get; set; }

    public string Name { get; set; }
    public string Description { get; set; }
    public bool Active { get; set; }
    public int CreatedBy { get; set; }
    public DateTime CreatedDate { get; set; }
    public int ModifiedBy { get; set; }
    public DateTime ModifiedDate { get; set; }

    #endregion

    #region constructors

    public MyTable()
    {
        //Set Default Values.
        Active = true;
        Name = string.Empty;
        CreatedDate = DateTime.MinValue;
        ModifiedDate = DateTime.MinValue;
    }

    #endregion

    #region Methods

    public override void DataSelect(System.Data.SqlClient.SqlConnection connection, System.Data.SqlClient.SqlTransaction transaction)
    {
        using (DAContext ctxt = new DAContext(connection, transaction))
        {

            var limitquery = from C in ctxt.MyTable
                             select C;


            //TODO: Sort the Query
            limitquery = FilterQuery(limitquery);

            var limit = limitquery.FirstOrDefault();

            if (limit != null)
            {
                this.Name = limit.Name;
                this.Description = limit.Description;
                this.Active = limit.Active;
                this.CreatedBy = limit.CreatedBy;
                this.CreatedDate = limit.CreatedDate;
                this.ModifiedBy = limit.ModifiedBy;
                this.ModifiedDate = limit.ModifiedDate;
            }
            else
            {
                throw new ObjectNotFoundException(string.Format("No MyTable with the specified Key ({0}) exists", this.MyTableKey));
            }
        }
    }

    private IQueryable<MyTable1> FilterQuery(IQueryable<MyTable1> limitQuery)
    {
        if (MyTableKey > 0) limitQuery = limitQuery.Where(C => C.MyTableKey == MyTableKey);
        if (!string.IsNullOrEmpty(Name)) limitQuery = limitQuery.Where(C => C.Name == Name);
        if (!string.IsNullOrEmpty(Description)) limitQuery = limitQuery.Where(C => C.Description == Description);
        if (Active) limitQuery = limitQuery.Where(C => C.Active == true);
        if (CreatedBy > 0) limitQuery = limitQuery.Where(C => C.CreatedBy == CreatedBy);
        if (ModifiedBy > 0) limitQuery = limitQuery.Where(C => C.ModifiedBy == ModifiedBy);
        if (CreatedDate > DateTime.MinValue) limitQuery = limitQuery.Where(C => C.CreatedDate == CreatedDate);
        if (ModifiedDate > DateTime.MinValue) limitQuery = limitQuery.Where(C => C.ModifiedDate == ModifiedDate);

        return limitQuery;
    }

    #endregion


}
1
0
6/20/2013 2:35:01 PM

Popular Answer

When tracking is enabled, selects are slow. Without a doubt, stop tracking and take another measurement.

Check out my benchmarks

http://netpl.blogspot.com/2013/05/yet-another-orm-micro-benchmark-part-23_15.html

1
6/20/2013 4:07:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow