[C#.NET][Entity Framework] 查詢大資料性能比較

[C#.NET][Entity Framework] 查詢大資料性能比較

有許多的人對 EF 的查詢效能有所疑慮,其實只要使用得當,EF 的效能是很棒的,這裡並不想要挑起 ORM Solution 的戰爭,其實是自己只對 EF 熟悉(逃~),我只需要用讀取資料最快的 DataReader 方式來比較差異性,就可以用來決定是否Buy-In EF

  1. 本篇使用的資料庫是 http://msftdbprodsamples.codeplex.com/releases/view/105902
  2. 資料表 FactProductInventory,它裡面有 776286 筆
  3. .Net Framework 4.5
  4. Entity Framework 版本 6.1.3
  5. Windows 8.1 x64

為了方便寫測試程式碼,有了下面兩個類別

https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/IFlowDataAccess.cs

資料存取合約:

{
    int RowCount { get; set; }

    object GetAllInventory();
}

 

 

共用 SqlConnect 物件:

https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/SqlFlowDataAccessBase.cs

{
    private static string s_connectString = ConfigurationManager.ConnectionStrings["AdventureWorksDW2012DbContext"].ConnectionString;
    protected static SqlConnection s_connection = null;
    private static bool s_disposed = false;

    static SqlFlowDataAccessBase()
    {
        s_connection = new SqlConnection(s_connectString);
        s_connection.Open();
    }

    ~SqlFlowDataAccessBase()
    {
        Dispose(false);
    }

    public void Dispose()
    {
        // Dispose of unmanaged resources.
        Dispose(true);
        // Suppress finalization.
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (s_disposed)
            return;

        if (disposing)
        {
            // Free any other managed objects here.
            //
            if (s_connection != null)
            {
                if (s_connection.State == ConnectionState.Open)
                {
                    s_connection.Close();
                }
                s_connection.Dispose();
                s_connection = null;
            }
        }

        // Free any unmanaged objects here.
        //
        s_disposed = true;
    }
}

 


Dapper 是一款高效的 ORM,我最喜歡的是它的 Dynamic Parameter 撰寫方式,個人認為它用起來比 SqlParameter 還要直覺,這是Dapper的性能比較程式碼:https://github.com/StackExchange/dapper-dot-net/blob/master/Tests/PerformanceTests.cs

本範例的完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/Dapper/DapperDAO.cs

核心片斷程式碼如下:

{
    public int RowCount { get; set; }

    public object GetAllInventory()
    {
        var query = s_connection.Query<FactProductInventory>("SELECT * FROM FactProductInventory");
        this.RowCount = query.Count();
        return query;
    }
}

 

 

 

EF 在預設情況下會 Tracking,這將耗費掉許多的資源,但這在網路資源很匱乏的時候很有用

完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/Dapper/DapperDAO.cs

核心片斷程式碼如下:

{
    public int RowCount { get; set; }

    public object GetAllInventory()
    {
        using (AdventureWorksDbContext db = new AdventureWorksDbContext())
        {
            var query = db.FactProductInventories.ToList();
            this.RowCount = query.Count();
            return query;
        }
    }
}

 

 

 

使用 AsNoTracking,可得到單次查詢命令的最高效率

完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/EF/EntityNoTrackDAO.cs

核心片斷程式碼如下:

{
    public int RowCount { get; set; }

    public object GetAllInventory()
    {
        using (AdventureWorksDbContext db = new AdventureWorksDbContext())
        {
            var query = db.FactProductInventories.AsNoTracking().ToList();
            this.RowCount = query.Count();
            return query;
        }
    }
}

 

個人已經很久沒有用 DataTable 了,不過還是將它們列入比較

DataReader 不將資料存放在記憶體,我只是用它來做一個比較基準,現實上這樣寫是雞肋功能不切實際

完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/DataReader/DataReaderDAO.cs

核心片斷程式碼如下:

{
    public int RowCount { get; set; }

    public object GetAllInventory()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection);

        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

        int count = 0;
        while (reader.Read())
        {
            object[] items = new object[reader.FieldCount];

            reader.GetValues(items);
            count++;
        }

        this.RowCount = count;
        return reader;
    }
}

 

 

 

SqlDataAdapter 的 Fill,將資料倒到 DataTable

完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/DataAdapter/DataAdapterDAO.cs

核心片斷程式碼如下:

{
    SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection);

    DataTable tableReader = new DataTable();
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    adapter.Fill(tableReader);
    this.RowCount = tableReader.Rows.Count;
    return tableReader;
}

 

 

 

用 DataTable.Load 把資料載入

完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/DataReader/DataReaderLoadDAO.cs

核心片斷程式碼如下:

{
    public int RowCount { get; set; }

    public object GetAllInventory()
    {
        SqlCommand command = new SqlCommand("SELECT * FROM FactProductInventory", s_connection);
        DataTable tableReader = new DataTable();
        SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

        tableReader.Load(reader);

        this.RowCount = tableReader.Rows.Count;
        return tableReader;
    }
}

 

 

 

用 DataTable.LoadDataRow 把資料載入

完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/DataReader/DataReaderLoadDataRowDAO.cs

核心片斷程式碼如下:

{
    public int RowCount { get; set; }

    public object GetAllInventory()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection);

        DataTable tableReader = new DataTable();
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

        for (int i = 0; i < reader.FieldCount; i++)
            tableReader.Columns.Add(reader.GetName(i), reader.GetFieldType(i));

        while (reader.Read())
        {
            object[] items = new object[reader.FieldCount];

            reader.GetValues(items);
            tableReader.LoadDataRow(items, true);
        }

        this.RowCount = tableReader.Rows.Count;
        return tableReader;
    }
}

 

 

 

使用  Reflection 把 DataReader 轉成強型別,Reflection 反射效能不是很好

完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/DataReader/DataReaderReflectMappingDAO.cs

核心片斷程式碼如下:

{
    public int RowCount { get; set; }

    public object GetAllInventory()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection);

        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

        var result = ReflectRowMapping(reader);

        this.RowCount = result.Count;
        return result;
    }

    private List<FactProductInventory> ReflectRowMapping(IDataReader reader)
    {
        List<FactProductInventory> inventoryList = new List<FactProductInventory>();
        var type = typeof(FactProductInventory);
        while (reader.Read())
        {
            FactProductInventory inventory = new FactProductInventory();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                var fieldName = reader.GetName(i);
                var value = reader.GetValue(i);
                PropertyInfo property = inventory.GetType().GetProperty(fieldName);
                property.SetValue(inventory, reader.IsDBNull(i) ? "null" : value);
            }
            inventoryList.Add(inventory);
        }
        return inventoryList;
    }
}

 

 

 

使用  Expression 把 DataReader 轉成強型別

完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/DataReader/DataReaderExpressionMappingDAO.cs

核心片斷程式碼如下:

{
    public int RowCount { get; set; }

    public object GetAllInventory()
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM FactProductInventory", s_connection);
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
        var result = DynamicRowMapping(reader);
        this.RowCount = result.Count;
        return result;
    }

    private List<FactProductInventory> DynamicRowMapping(IDataReader reader)
    {
        List<FactProductInventory> inventoryList = new List<FactProductInventory>();

        var mapping = new DynamicProperty<FactProductInventory>();
        while (reader.Read())
        {
            FactProductInventory inventory = new FactProductInventory();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                var fieldName = reader.GetName(i);
                var value = reader.GetValue(i);
                mapping.SetValue(inventory, fieldName, value);
            }
            inventoryList.Add(inventory);
        }
        return inventoryList;
    }
}

 

 

測試片斷程式碼

public void TestMethod1()
{
    var runTimes = 1;
    IFlowDataAccess dapper = new DapperDAO();
    IFlowDataAccess ef = new EntityDAO();
    IFlowDataAccess efNoTrack = new EntityNoTrackDAO();
    IFlowDataAccess dataReaderLoad = new DataReaderLoadDAO();
    IFlowDataAccess dataReaderLoadDataRow = new DataReaderLoadDataRowDAO();
    IFlowDataAccess dataReaderDynamicMappingDAO = new DataReaderExpressionMappingDAO();
    IFlowDataAccess dataReaderReflectMappingDAO = new DataReaderReflectMappingDAO();
    IFlowDataAccess dataReaderDAO = new DataReaderDAO();
    IFlowDataAccess dataAdapterDAO = new DataAdapterDAO();

    var test1 = new TestInfo(() =>
    {
        var datas = dapper.GetAllInventory();
        return dapper;
    }, "Dapper");
    test1.Run(runTimes);

    var test2 = new TestInfo(() =>
    {
        var datas = ef.GetAllInventory();
        return ef;
    }, "EF");
    test2.Run(runTimes);

    var test3 = new TestInfo(() =>
    {
        var datas = efNoTrack.GetAllInventory();
        return efNoTrack;
    }, "EF No Track");
    test3.Run(runTimes);

    var test4 = new TestInfo(() =>
    {
        var datas = dataReaderLoad.GetAllInventory();
        return dataReaderLoad;
    }, "DataReader for DataTable.Load");
    test4.Run(runTimes);

    var test5 = new TestInfo(() =>
    {
        var datas = dataReaderLoadDataRow.GetAllInventory();
        return dataReaderLoadDataRow;
    }, "DataReader for DataTable.LoadDataRow");
    test5.Run(runTimes);

    var test6 = new TestInfo(() =>
    {
        var datas = dataReaderDynamicMappingDAO.GetAllInventory();
        return dataReaderDynamicMappingDAO;
    }, "DataReader for Expression Mapping");
    test6.Run(runTimes);

    var test7 = new TestInfo(() =>
    {
        var datas = dataReaderReflectMappingDAO.GetAllInventory();
        return dataReaderReflectMappingDAO;
    }, "DataReader for Reflect Mapping");
    test7.Run(runTimes);

    var test8 = new TestInfo(() =>
    {
        var datas = dataReaderDAO.GetAllInventory();
        return dataReaderDAO;
    }, "DataReader");
    test8.Run(runTimes);

    var test9 = new TestInfo(() =>
    {
        var datas = dataAdapterDAO.GetAllInventory();
        return dataAdapterDAO;
    }, "DataAdapter use Fill");
    test9.Run(runTimes);
    Console.Write("");

    Console.WriteLine("排名,最快的在前面:");

    List<TestInfo> testInfos = new List<TestInfo>()
    {
        test1,
        test2,
        test3,
        test4,
        test5,
        test6,
        test7,
        test8,
        test9
    };

    var storts = testInfos.OrderBy(t => t.CostTime);
    var index = 1;
    foreach (var info in storts)
    {
        Console.WriteLine("第{0}名,{1}", index, info.Message);
        index++;
    }
}

 

 

上述方法會用到 TestInfo 類別

https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/UnitTestProject1/TestInfo.cs

 

執行測試結果:

image

 

 

 

跑一次還不夠,跑個10次看看

image

 

 

 

結論:

  1. 強型別在 Vistual Studio 有 Intellisense,可幫助我們在開發階段提示錯誤,弱型別當然就沒有,有人說弱型別好寫,我個人認為那不叫好寫,而叫鬆散,鬆散所帶來的副作用,就是後續引發除錯成本是很高的
  2. 除了 EF 之外,其餘的寫法都不是使用強型別物件操作,而是弱型別的字串,這是天差地遠的撰寫方式,硬是要把 Dapper | ADO.NET 跟 EF 擺在一塊比,對 EF 不公平,畢竟 EF 提供的功能比它們還要多很多;
  3. 它能有單次查詢高效能的表現,但也能有最低效能的表現,全看你的需求決定是否需要 Tracking
  4. Expression 的強型別對應已經直逼 Dapper 與 EF,未來會將這段程式碼收錄下來
  5. 若不能用 EF,Dapper 會是我處理資料庫的首選
  6. DataAdapter 居然可以比 DataReader 快

為什麼我不用 DataTable:

  1. 它預設是弱型別
  2. 雖然它可以有強型別(DataSet),它的物件操作方式與一般物件不同,對我來講不夠直覺,參考 http://www.dotblogs.com.tw/yc421206/archive/2014/07/14/145944.aspx
  3. 較耗損網路資源

 

 


本文出自:http://www.dotblogs.com.tw/yc421206/archive/2015/03/16/150743.aspx

專案連結:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.Performance/Simple.ORM.Performance/

若有謬誤,煩請告知,新手發帖請多包涵


Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET

Image result for microsoft+mvp+logo