ADO.NET Performance Best Practices

Performance Best Practices for ADO.NET

Focus Areas

Following are the focus areas of improvising the Performance of ADO.NET.
o Configuration and Connection Pooling
o Data Retreival Techniques
o Query Execution Options
o DataSets
o DataSets vs DataReader


Configuration and Connection Pooling
1. SQL Managed provider will automatically pool database connections
a. Based of the connection string
b. The connection strings have to match exactly
c. Minor differences like extra spaces do matter
2. Specify the following parameter and values in the SQL connection string to configure connection pooling
a. Max Pool Size
b. Min Pool Size
3. Remember to close the connection to return it to the pool

Data Retrieval Techniques

1. ADO.NET provides multiple data retrieval techniques when a query is executed.
2. It is important to be aware of the results that a query will return so that the appropriate ADO.NET data retrieval technique can be used.

Query Execution Options

Execute Options
a. ExecuteScalar Returns the first row, column value in the result set, useful for processing results for count, sum queries
b. ExecuteNonQuery Returns the result set of the query execution; Used with queries like insert, update, delete and DDL Queries which dont return results.
c. ExecuteReader - Returns a fast, forward-only stream of results.
d. DataAdapter.Fill Fills a Dataset with the results of the query.

DataSets
1. DataSet can store a local copy of the query results in a hierarchical format.
a. Great for working Disconnected because client applications can treat the Dataset as a local database
b. Great for client side caching
2. DataSets are well suited for client side applications, where frequent data lookups are to be performed without querying the database.
a. Save Database Round Trips

DataSet Vs DataReaders
1. DataSets are heavier than a DataReader for Simple, forward only read Operations.
2. DataSets are more functional than DataReaders
3. DataSets can be used as a Store for Query results and act as a Cache
4. Rich clients can use DataSets to avoid Round trips to the database and use Data-readers for Forward-only read access.