DataSet Performance Tips with Indicies

Each of these tips relate to speeding up the searching of data in a dataset. Although indexes are created in a dataset they are not exposed. Knowing how to make ADO .net generate them gives you the power to enhance the performance of you DataBase application.

Tip #1: ADO .net generates indexes on the primary key(s).

If you are searching on primary key fields instead of using DataTable.Select(), use DataTable.Rows.Find(). The Find method will use the index to find the row. Knowing this may also make you think differently when defining primary keys.

Tip #2: Use a DataView.

DataViews will generate an index for a sorted column. If you find you are searching for data using a particular column. It would be a good idea to create a Dataview sorted by the column(s) you need to search. Then use the Dataview.FindRows() method to get at your data. The DataView will have created an index for the sort column, and FindRows() will use the index.

Tip #3: Be leary of Tip #2.

If you are trying to take advantage of DataView indexes it's important to know when the indexes are created. ADO .net generates an index each time you set the filter, sort, and rowversion properties of the DataView. So if you create a DataView and set these properties individually the indexes will be created serveral times. To avoid this be sure to use the DataView constructor that takes the table, filter, sort and rowversion as arguments. (See example below) This way the indexes will be created correctly the first time.

private void MakeDataView(DataSet ds)
   DataView dv = new DataView(ds.Tables["Suppliers"], "Country = 'CA'", "CompanyName", DataViewRowState.CurrentRows);
   dv.AllowEdit = true;
   dv.AllowNew = true;
   dv.AllowDelete = true;