DataTable 透過 DataView 來做 Distinct 會很慢

前言

一般要將 DataTable 重覆的資料，可以透過 DataView 的 ToTable Method 中的 distinct 參數設定為 true ，就可以回傳 Distinct 過的 DataTable。例如，

DataView dv1 = new DataView(dt);
DataTable dtNew1 = dv1.ToTable(true, "Id", "Name", "Role", "DataColumn1", "DataColumn2", "DataColumn3", "DataColumn4", "DataColumn5", "DataColumn6", "DataColumn7");

但如果資料多時，就會很慢很慢。 那怎麼辦呢?

解法

我們可以參考 DataView ToTable Vs Linq to find distinct ，改用 Linq 的 Distinct Method
測試資料及結果如下，

using System.Data;

class Program
{
    static System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
    static void Main(string[] args)
    {
        var totalRows = 1000 * 1000;
        DataTable dtSample = GenerateTable(totalRows, 80000);
        Console.WriteLine(string.Format("Total Count:{0:n0}", totalRows));
        var withoutDataView = NormalDataSetWithOutDataView(dtSample);
        Console.WriteLine("========== Distinc Without DataView ========");
        Console.WriteLine(string.Format("Row Count:{0:n0}, Time Spend:{1:n0} Milliseconds", withoutDataView.Item1, withoutDataView.Item2));
        var withoutDataViewGroupBy = NormalDataSetWithOutDataViewGroupBy(dtSample);
        Console.WriteLine("========== Distinc Without DataView Use Group By ========");
        Console.WriteLine(string.Format("Row Count:{0:n0}, Time Spend:{1:n0} Milliseconds", withoutDataViewGroupBy.Item1, withoutDataViewGroupBy.Item2));
        var withDataView = NormalDataSetWithDataView(dtSample);
        Console.WriteLine("========== Distinc With DataView ========");
        Console.WriteLine(string.Format("Row Count:{0:n0}, Time Spend:{1:n0} Milliseconds", withDataView.Item1, withDataView.Item2));
        Console.ReadLine();
    }

    private static Tuple<int, long> NormalDataSetWithDataView(DataTable dt)
    {
        StopWatchEnable(watch, true);
        DataView dv1 = new DataView(dt);
        DataTable dtNew1 = dv1.ToTable(true, "Id", "Name", "Role", "DataColumn1", "DataColumn2", "DataColumn3", "DataColumn4", "DataColumn5", "DataColumn6", "DataColumn7");
        return new Tuple<int, long>(dtNew1.Rows.Count, StopWatchEnable(watch, false));
    }
    private static Tuple<int, long> NormalDataSetWithOutDataView(DataTable dt)
    {
        StopWatchEnable(watch, true);
        var items = dt.AsEnumerable().Distinct(System.Data.DataRowComparer.Default).ToList();
        return new Tuple<int, long>(items.Count, StopWatchEnable(watch, false));
    }

    private static Tuple<int, long> NormalDataSetWithOutDataViewGroupBy(DataTable dt)
    {
        StopWatchEnable(watch, true);
        var items = dt.AsEnumerable()
                        .GroupBy(d => string.Join(",", d.ItemArray))
                        .Select(d => d.First())
                        .ToList();

        return new Tuple<int, long>(items.Count, StopWatchEnable(watch, false));
    }

    private static DataTable GenerateTable(int noOfRows, int modules)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Id", typeof(int));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Role", typeof(string));
        dt.Columns.Add("DataColumn1", typeof(string));
        dt.Columns.Add("DataColumn2", typeof(string));
        dt.Columns.Add("DataColumn3", typeof(string));
        dt.Columns.Add("DataColumn4", typeof(string));
        dt.Columns.Add("DataColumn5", typeof(string));
        dt.Columns.Add("DataColumn6", typeof(string));
        dt.Columns.Add("DataColumn7", typeof(string));
        for (int i = 0; i < noOfRows; i++)
        {
            int id = i % modules;
            dt.Rows.Add(new object[]
                          { id,
                                "Test" + id.ToString(),
                                "Test" + id.ToString(),
                                "Test" + id.ToString(),
                                "Test" + id.ToString(),
                                "Test" + id.ToString(),
                                "Test" + id.ToString(),
                                "Test" + id.ToString(),
                                "Test" + id.ToString(),
                                "Test" + id.ToString() });
        }
        return dt;
    }

    private static long StopWatchEnable(System.Diagnostics.Stopwatch watch, bool enable)
    {
        if (enable)
        {
            watch.Restart();
            return 0;
        }
        else
        {
            watch.Stop();
            return watch.ElapsedMilliseconds;
        }
    }
}

以下測試環境為 i7-8569U CPU @ 2.80GHz, 16G RAM, SSD
資料筆數為 100,000
資料欄位為 10
分別使用 AsEnumerable().Distinct , AsEnumerable().GroupBy 及 DataView.ToTable(true,... ，結果如下，

可以發現，使用 DataView.ToTable 真的粉慢

• 註: 如果要將 IEnumerable 轉成 DataTable ，可以使用 CopyToDataTable() 這個 Extension Method哦! 例如上述範例，可以改成

private static Tuple<int, long> NormalDataSetWithOutDataView(DataTable dt)
{
    StopWatchEnable(watch, true);
    DataTable dtNew1 =  dt.AsEnumerable().Distinct(DataRowComparer.Default).CopyToDataTable<DataRow>();
    return new Tuple<int, long>(dtNew1.Rows.Count, StopWatchEnable(watch, false));
}

參考資料

DataView ToTable Vs Linq to find distinct
CopyToDataTable()