Thursday, May 31, 2012

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 1st 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

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