DataTable.Select is Slow?

Introduction

When using DataTable, we generally use Find or Select method for searching specific data row.

Find method uses primary key and index, so it's fastest. But if the filter condition is not in primary key column, there is no option but we have to use Select method or DataView.RowFilter.

Geneally we know DataTable.Select is slower than Find, because DataTable.Select scans all the records.

In here, I'll explain how to increase DataTable.Select performace.

Background

DataView class is very useful when we bind DataTable to grid control. By DataView, we can give some filter and sort condition. Moreover, DataView constructs an index so finds rows quickly and efficiently.

Using the code

For the performance test, create sample DataTable instance. it has ID primary key. column.

DataTable Table = new DataTable();

[TestInitialize]
public void Init()
{
    var idColumn = Table.Columns.Add("ID", typeof(int));
    Table.Columns.Add("ParentID", typeof(int));
    Table.Columns.Add("Name", typeof(string));

    for (int i = 0; i < 3000; i++)
    {
        Table.Rows.Add(i, (int)Math.Floor((double)i / 10), i.ToString());
    }

    Table.PrimaryKey = new DataColumn[] { idColumn };
    Table.AcceptChanges();
}

Make some search method using DataTable.Select. Unfortunately, cannot use DataTable.Rows.Find method Because of searching by ParentID column.

IEnumerable<DataRow> GetDescendants(int id)
{
    foreach (DataRow item in Table.Select("ParentID=" + id + " AND ID<>" + id))
    {
        yield return item;

        foreach (var subItem in GetDescendants((int)item["ID"]))
        {
            yield return subItem;
        }
    }
}

Run GetDescendants() performance test.

[TestMethod]
public void TestMethod1()
{
    Stopwatch watch;

    watch = Stopwatch.StartNew();
    var list = GetDescendants(0).ToArray();
    Debug.Print("DataTable.Select Elapsed : " + watch.Elapsed);

It takes about 8 sec on my PC.

How can it be improved? The key is on the DataView. Don't forget above background that DataView constructs an index.

Now let's add DataView using ParentID column for sorting. It will create ParentID index.

DataView view = new DataView(Table);
view.Sort = "ParentID";

And run again above test code

watch = Stopwatch.StartNew();
var list3 = GetDescendants(0).ToArray();
Debug.Print("DataTable.Select Elapsed : " + watch.Elapsed);

Surprisingly, it takes only 0.026 sec based on my PC.

I guess DataTable.Select use DataView's index.

This is my full test code.

[TestMethod]
public void TestMethod1()
{
    Stopwatch watch;

    watch = Stopwatch.StartNew();
    var list = GetDescendants(0).ToArray();
    Debug.Print("DataTable.Select Elapsed : " + watch.Elapsed);

    watch = Stopwatch.StartNew();
    var list2 = GetDescendants(0).ToArray();
    Debug.Print("DataTable.Select Elapsed : " + watch.Elapsed);

    DataView view = new DataView(Table);
    view.Sort = "ParentID";

    watch = Stopwatch.StartNew();
    var list3 = GetDescendants(0).ToArray();
    Debug.Print("DataTable.Select Elapsed : " + watch.Elapsed);

    /* Output

    DataTable.Select Elapsed : 00:00:08.2301008
    DataTable.Select Elapsed : 00:00:08.2629208
    DataTable.Select Elapsed : 00:00:00.0268323

     */
}

I tested GetDescendants two times before adding DataView. They took almost same time.

But after adding DataView, you can see GetDescendants method improved amazingly.

Points of Interest

How to improve DataTable.Select performance.

History

2014-08-15 Initial Release.