Click here to Skip to main content
Click here to Skip to main content

Tagged as

DataTable.Select is Slow?

, 15 Aug 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
DataTable.Select performance test and how to improve it

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

techcap
Web Developer
Korea (Republic Of) Korea (Republic Of)
No Biography provided

Comments and Discussions

 
QuestionVery good Pinmembernguyenhuuson18-Nov-14 23:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 15 Aug 2014
Article Copyright 2014 by techcap
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid