Thursday, May 31, 2012

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


In this post DataTable.Select() Vs DataTable.Rows.Find() Performance Test - Filter on Primary Key we, shall compare the performance of DataTable.Select() and DataTable.Rows.Find(), in performing a filter based on the Primary Key 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 DataTable.Select() and DataTableRows.Filter()

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 DataTable.Select() and DataTable.Rows.Find(); the results of the test are as follows.

DataTable.Select() Average time:         31.86 Microseconds
DataTable.Rows.Find() Average time:   4.24  Microseconds



Inference
DataTable.Rows.Find() performs far better than DataTable.Select(), when it comes to filtering data based on Primary Key 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;
    //
    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();
    //
    DataRow[] drEmp = dsEmployee.Tables["dtEmployee"].Select("ID=2515");
    //
    timer.Stop();
    //
    Response.Write((1e6 * timer.ElapsedTicks / (double)Stopwatch.Frequency).ToString() + "
"
);
}
//
private void TestBlock2()
{
    timer = new Stopwatch();
    timer.Start();
    //
    DataRow drEmp = dsEmployee.Tables["dtEmployee"].Rows.Find(2515);
    //
    timer.Stop();
    //
    Response.Write((1e6 * timer.ElapsedTicks / (double)Stopwatch.Frequency).ToString() + "
"
);
}

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



Related Posts

Search other Products:
Flipkart.com

No comments: