DataView Performance
This topic discusses the performance benefits of using the Find and FindRows methods of the DataView class, and of caching a DataView in a Web application.
Find and FindRows
DataView constructs an index. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure that enables the DataView to find the row or rows associated with the key values quickly and efficiently. Operations that use the index, such as filtering and sorting, see signifcant performance increases. The index for a DataView is built both when the DataView is created and when any of the sorting or filtering information is modified. Creating a DataView and then setting the sorting or filtering information later causes the index to be built at least twice: once when the DataView is created, and again when any of the sort or filter properties are modified. For more information about filtering and sorting with DataView, see Filtering with DataView (LINQ to DataSet) and Sorting with DataView (LINQ to DataSet).
If you want to return the results of a particular query on the data, as opposed to providing a dynamic view of a subset of the data, you can use the Find or FindRows methods of the DataView, rather than setting the RowFilter property. The RowFilter property is best used in a data-bound application where a bound control displays filtered results. Setting the RowFilter property rebuilds the index for the data, adding overhead to your application and decreasing performance. The Find and FindRows methods use the current index without requiring the index to be rebuilt. If you are going to call Find or FindRows only once, then you should use the existing DataView. If you are going to call Find or FindRows multiple times, you should create a new DataView to rebuild the index on the column you want to search on, and then call the Find or FindRows methods. For more information about the Find and FindRows methods, see Finding Rows (ADO.NET).
The following example uses the Find method to find a contact with the last name "Zhu".
Dim contacts As DataTable = dataSet.Tables("Contact")
Dim query = _
From contact In contacts.AsEnumerable() _
Order By contact.Field(Of String)("LastName") _
Select contact
Dim view As DataView = query.AsDataView()
Dim found As Integer = view.Find("Zhu")
DataTable contacts = dataSet.Tables["Contact"];
EnumerableRowCollection<DataRow> query = from contact in contacts.AsEnumerable()
orderby contact.Field<string>("LastName")
select contact;
DataView view = query.AsDataView();
// Find a contact with the last name of Zhu.
int found = view.Find("Zhu");
The following example uses the FindRows method to find all the red colored products.
Dim products As DataTable = dataSet.Tables("Product")
Dim query = _
From product In products.AsEnumerable() _
Order By product.Field(Of Decimal)("ListPrice"), product.Field(Of String)("Color") _
Select product
Dim view As DataView = query.AsDataView()
view.Sort = "Color"
Dim criteria As Object() = New Object() {"Red"}
Dim foundRowsView As DataRowView() = view.FindRows(criteria)
DataTable products = dataSet.Tables["Product"];
EnumerableRowCollection<DataRow> query = from product in products.AsEnumerable()
orderby product.Field<Decimal>("ListPrice"), product.Field<string>("Color")
select product;
DataView view = query.AsDataView();
view.Sort = "Color";
object[] criteria = new object[] { "Red"};
DataRowView[] foundRowsView = view.FindRows(criteria);
ASP.NET
ASP.NET has a caching mechanism that allows you to store objects that require extensive server resources to create in memory. Caching these types of resources can significantly improve the performance of your application. Caching is implemented by the Cache class, with cache instances that are private to each application. Because creating a new DataView object can be resource intensive, you might want to use this caching functionality in Web applications so that the DataView does not have to be rebuilt every time the Web page is refreshed.
In the following example, the DataView is cached so that the data does not have to be re-sorted when the page is refreshed.
If (Cache("ordersView") = Nothing) Then
Dim dataSet As New DataSet()
FillDataSet(dataSet)
Dim orders As DataTable = dataSet.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of Boolean)("OnlineOrderFlag") = True _
Order By order.Field(Of Decimal)("TotalDue") _
Select order
Dim view As DataView = query.AsDataView()
Cache.Insert("ordersView", view)
End If
Dim ordersView = CType(Cache("ordersView"), DataView)
GridView1.DataSource = ordersView
GridView1.DataBind()
if (Cache["ordersView"] == null)
{
// Fill the DataSet.
DataSet dataSet = FillDataSet();
DataTable orders = dataSet.Tables["SalesOrderHeader"];
EnumerableRowCollection<DataRow> query =
from order in orders.AsEnumerable()
where order.Field<bool>("OnlineOrderFlag") == true
orderby order.Field<decimal>("TotalDue")
select order;
DataView view = query.AsDataView();
Cache.Insert("ordersView", view);
}
DataView ordersView = (DataView)Cache["ordersView"];
GridView1.DataSource = ordersView;
GridView1.DataBind();