ADO.NET 2.0 Performance Guidelines - DataSet
From Guidance Share
- J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
DataSet serialization is more efficiently implemented in .NET Framework version 1.1 than in version 1.0. However, DataSet serialization often introduces performance bottlenecks. You can reduce the performance impact in a number of ways:
- Use column name aliasing. The serialized data contains column names so that you can use column name aliasing to reduce the size of the serialized data.
- Avoid serializing multiple versions of the same data. The DataSet maintains the original data along with the changed values. If you do not need to serialize new and old values, call AcceptChanges before you serialize a DataSet to reset the internal buffers.
- Reduce the number of DataTable objects that are serialized. If you do not need to send all the DataTable objects contained in a DataSet, consider copying the DataTable objects you need to send into a separate DataSet.
Use Primary Keys and Rows.Find for Indexed Searching
If you need to search a DataSet by using a primary key, create the primary key on the DataTable. This creates an index that the Rows.Find method can use to quickly find the records that you want. Do not use DataTable.Select because DataTable.Select does not use indices.
Use a DataView for Repetitive Non-Primary Key Searches
If you need to repetitively search by using non-primary key data, create a DataView that has a sort order. This creates an index that can be used to perform the search. This is best suited to repetitive searches because there is some cost to creating the index.
The DataView object exposes the Find and FindRows methods so that you can query the data in the underlying DataTable. If you are only performing a single query, the processing that is required to create the index reduces the performance that is gained by using the index.
When you create a DataView object, use the DataView constructor that takes the Sort, RowFilter, and RowStateFilter values as constructor arguments along with the underlying DataTable. Using the DataView constructor ensures that the index is built once. If you create an empty DataView and set the Sort, RowFilter, or RowStateFilter properties afterwards, the index is built at least two times.
Use the Optimistic Concurrency Model for Datasets
There are two concurrency models that you can use when working with datasets in an environment that has multiple users. These two models are the pessimistic and optimistic models. When you read data and use the pessimistic model, locks are established and held until updates are made and the locks are released. Holding locks on server resources, in this case database tables, leads to contention issues. It is best to use granular locks for very short durations.
The optimistic model does not lock the data when the data is read. The optimistic model locks the data just before the data is updated and releases the lock afterwards. There is less contention for data with the optimistic model, which is good for shared server scenarios; however, you should take into account the scenarios for managing the concurrency violations. A common technique you can use to manage concurrency violations is to implement a timestamp column or to verify against the original copy of data.
- For more information about how to implement optimistic concurrency solutions, see "Optimistic Concurrency" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconOptimisticConcurrency.asp.