DataView.RowFilter() Vs DataTable.Rows.Find() Performance Test - Filter on Primary Key Field

In this post DataView.RowFilter() Vs DataTable.Rows.Find() Performance Test  - Filter on Primary Key Field  we, shall compare the performance of DataView.RowFilter () and DataTable.Rows.Find(), in performing a filter based on a Primary Key Field of the table.

Asp.Net provides a number of ways to filter data, like DataTable.Select, DataTable.Rows.Find, DataView.RowFilter etc, each one has its own advantages and disadvantages, we shall now see the difference in the execution time between DataView.RowFilter() and DataTableRows.Filter(), in filtering data based on a Primary Key column

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 50 iterations with both DataView.RowFilter() and DataTable.Rows.Find(); the results of the test are as follows.

DataView.RowFilter () Average time:   320.09 Microseconds
DataTable.Rows.Find() Average time:  273.80  Microseconds

Inference
The average values indicate that the DataTable.Rows.Find() performs better than the DataView.RowFilter(), however that is an interesting finding here, the DataView.RowFilter takes more time for the first filter there after it is much faster than the DataTable.Rows.Find(), refer to the below table for the time measured for each of the iteration.

Iteration	DataView.RowFilter()	DataTable.Rows.Find()
1	3816.52	297.54
2	179.67	265.50
3	178.59	268.38
4	174.44	261.90
5	171.94	260.81
6	175.18	344.26
7	173.91	346.23
8	171.16	280.43
9	178.28	265.94
10	170.55	258.89
11	174.88	272.29
12	175.55	258.57
13	169.13	260.11
14	176.76	263.58
15	175.90	265.97
16	169.57	264.26
17	175.61	262.17
18	175.90	275.02
19	170.72	268.06
20	176.31	260.12
21	175.61	262.41
22	173.67	288.02
23	175.00	261.15
24	170.95	264.41
25	176.32	268.97
Averarge	320.09	273.80

From the data it is clear that DataView.RowFilter takes more time for the 1^st filter, but the successive filters are much faster, this is because the Index is formed during the first filter, and the index is re-used for the successive filters, hence the conclusion is

If you need to perform a single search based on the Primary key column then use DataTable.Rows.Find()

If you need to perform multiple searches based on the Primary Key (or even non-primary key) columns then go for a DataView.RowFilter()

The code used to perform the test is as follows

SqlConnection objConn;

SqlCommand objCmd;

SqlDataAdapter objDA;

DataSet dsEmployee;

Stopwatch timer;

DataView dvEmployee;

string strQuery = string.Empty;

//

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 = "ID";

    //

    Response.Write("Test Block 1
");

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

    {

        TestBlock1();

    }

    //

    Response.Write("

Test Block 2
");

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

    {

        TestBlock2();

    }

}

//

private void TestBlock1()

{

    timer = new Stopwatch();

    timer.Start();

    //

    dvEmployee.RowFilter = "ID = 2515";

    grdEmployees.DataSource = dvEmployee;

    grdEmployees.DataBind();

    //

    timer.Stop();

    //

    Response.Write((1e6 * timer.ElapsedTicks / (double)Stopwatch.Frequency).ToString() + "
");

}

//

private void TestBlock2()

{

    timer = new Stopwatch();

    timer.Start();

    //

    DataRow[] drEmp = new DataRow[1];

    drEmp[0] = dsEmployee.Tables["dtEmployee"].Rows.Find(2515);

    grdEmployees.DataSource = drEmp.CopyToDataTable();

    grdEmployees.DataBind();

    //

    timer.Stop();

    //

    Response.Write((1e6 * timer.ElapsedTicks / (double)Stopwatch.Frequency).ToString() + "
");

}

That’s it we have seen the performance difference between the DataView.RowFilter () and the DataTable.Rows.Find() in filtering data based on Primary Key.

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

DataTable.Select() Vs DataTable.Rows.Find() Performance Test - Filter on Primary Key