DataTable.Select() Vs DataView.RowFilter() Performance Test – Filtering Rows

In this post DataTable.Select() Vs DataView.RowFilter() Performance Test – Filtering Rows we, shall compare the performance of DataTable.Select() and DataView.RowFilter() in populating a GridView.

Asp.Net provides a number of ways to fill a GridView, like DataReader, DataTable, DataView etc, each one has its own advantages and disadvantages, we shall now see the difference in the execution time between DataTable.Select() and DataView.RowFilter()

The configuration of the system used to perform the evaluation is as follows.

OS	Windows XP Professional 2002 SP3
Processor	Pentium® D 2.66 GHz
RAM	3 GB

The performance test was carried out for 50iterations with both DataTable and DataView; the results of the test are as follows.

Total Records in Table: 3000

Matching Records: 643

DataTable.Select() Average time:       50.26 Milliseconds
DataView.RowFilter() Average time:   43.88  Milliseconds

Inference
DataView.RowFilter() performs better than DataTable.Select() when the DataView is sorted based on the column to be filtered, this is because when the DataView is sorted, an index is created based on the sorted column, hence it is advisable to SORT a dataview and use the same for filtering especially when we want to perform multiple filters based on the same column.

The code used to perform the test is as follows.

protected void Page_Load(object sender, EventArgs ea)

{

    string strConn = ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();

    objConn = new SqlConnection(strConn);

    strQuery = "SELECT * FROM Employee";

    objCmd = new SqlCommand(strQuery, objConn);

    objDA = new SqlDataAdapter(objCmd);

    dsEmployee = new DataSet();

    objDA.Fill(dsEmployee, "dtEmployee");

    //

    DataColumn[] arrPrimaryKey = new DataColumn[1];

    arrPrimaryKey[0] = dsEmployee.Tables["dtEmployee"].Columns["ID"];

    dsEmployee.Tables["dtEmployee"].PrimaryKey = arrPrimaryKey;

    //

    dvEmployee = new DataView(dsEmployee.Tables["dtEmployee"]);

    dvEmployee.Sort = "Name";

    //

    Response.Write("Test Block 1
");

    for (int i = 0; i < 50; i++)

    {

        TestBlock1();

    }

    //

    Response.Write("

Test Block 2
");

    for (int i = 0; i < 50; i++)

    {

        TestBlock2();

    } 

}

//

private void TestBlock1()

{

    timer = new Stopwatch();

    timer.Start();

    //

    dvEmployee.RowFilter = "Name='John'";

    grdEmployees.DataSource = dvEmployee;

    grdEmployees.DataBind();

    //

    timer.Stop();

    //

    Response.Write(timer.ElapsedMilliseconds.ToString() + "
");

}

//

private void TestBlock2()

{

    timer = new Stopwatch();

    timer.Start();

    //

    DataRow[] drEmp = dsEmployee.Tables["dtEmployee"].Select("Name='John'");

    //

    grdEmployees.DataSource = drEmp.CopyToDataTable();

    grdEmployees.DataBind();

    timer.Stop();

    //

    Response.Write(timer.ElapsedMilliseconds.ToString() + "
");

}

That’s it we have seen the performance difference between the DataTable.Select() and the DataView.RowFilter() in loading a GridView.

Related Posts

ADO.Net - DataTable.Select() Vs DataTable.Rows.Find Vs DataView.

ADO.Net - Using DataTable.Select to Filter Rows

Binding a DataTable to GridView

ADO.Net - DataTable.Select() Vs DataTable.Rows.Find Vs DataView.

Asp.Net provides many options to FILTER and SORT records in the client side, the available options are as follows.

DataTable.Rows.Find
DataTable.Select
DataView. RowFilter

Each of these options have their own advantages and disadvantages, we will have to use the right options at the right place to maximize performance.

Let us not see the Best Practices for using these options.

DataTable.Rows.Find
The DataTable.Rows.Find options is best suited wile filtering specific rows, based on the PrimaryKey of the Table. This option gives the best performance for filtering limited rows with straight forward filter Option on the PrimaryKey.

ADO.Net builds an index based on the PrimaryKey column, and this option works like an Index Seek to reach out the exact row using the Index, this is the fastest option to select rows based on PrimaryKey.

Recommended while filtering a single/very few rows based on the PrimaryKey
Not Recommended for complex filters and filter returning more number of rows

Example
// Set the PrimaryKey of the DataTable
DataColumn[] arrPrimaryKey = new DataColumn[1];
arrPrimaryKey[0] = dsEmpInfo.Tables["dtEmpInfo"].Columns["KEY_ID"];
dsEmpInfo.Tables["dtEmpInfo"].PrimaryKey = arrPrimaryKey;
//
//Filter rows based on the PrimaryKey
DataRow dr1 = dsEmpInfo.Tables["dtEmpInfo"].Rows.Find("1");

The above experssion will return a single DataRow, whoose PrimaryKey value (KEY_ID) is 1.

DataTable.Select
The Datable.Select method is a good options when you want to perform only a single or very few filters on a large DataTable, this methods scans through the entire table and retrieves the rows based on the filter condition.

The DataTable.Select method can be used to perform multiple and complex filters on a DataTable

Example
dsEmpInfo.Tables["dtEmpInfo"].Select("KEY_ID = '1'");
The above expression will return the employee details whose ID is 1.

dsEmpInfo.Tables["dtEmpInfo"].Select("Name Like 'A%'");
The above expression will return a list of employees whose name starts with A.

This option works much like a Table Scan, best suited when the filter is expected to return a large number of rows.

Recommended when performing a single (or very few) filter from a DataTable, which is expected to return more number of rows.
Not recommended to filter based on PrimaryKey & for performing multiple filters on the same DataTable

DataView.RowFilter
This option is similar to the DataTable.Select method, the differences is that the view first creates and index of the data based on the SORT order, and reuses the view to filter out the data. The Index creating is a one time activity, this will take time, but the subsequent filters will be quick.

Recommended when we need to perform multiple filters on the same DataTable.
Not Recommended for just a single filter, as there is an overhead in creating the Index

Example
DataView dvEmpInfo = new DataView(dsEmpInfo.Tables["dtEmpInfo"]);
dvEmpInfo.RowFilter = "KEY_ID = 1";
dvEmpInfo.Sort = "KEY_ID ASC";

That's it, we have compared the Pros and Cons of the various options available for filtering and sorting data from a DataTable in ADO.Net