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
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
|
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
");
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
