實體框架(6)性能優化建議

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

我的應用程序中有一個ADO.Net數據訪問層,它使用基本的ADO.Net和CRUD存儲過程(每個操作一個,例如Select_myTable,Insert_myTable)。可以想像,在大型系統(如我們的系統)中,DA層所需的DB對像數量非常大。

我一直在研究將層類重構為EF POCO類的可能性。我已經設法做到這一點,但是當我嘗試進行性能測試時,它變得非常可怕。使用下面的類(創建對象,將Key設置為所需的值,調用dataselect),100000次數據加載僅需要大約47秒(數據庫中只有少數記錄)。而Stored Proc方法大約需要7秒鐘。

我正在尋找關於如何優化這一點的建議 - 作為一個注意事項,我不能改變層的暴露功能 - 只是它如何實現方法(即我不能將上下文所有權的責任傳遞給BO層)

謝謝

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


}

熱門答案

跟踪時選擇很慢。你絕對應該關閉跟踪並再次測量。

看看我的基準測試

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




許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因