Return to HomePage

---

Checklist: ADO.NET Performance

Source: http://msdn.microsoft.com/library/en-us/dnpag/html/scalenetcheck01.asp

J.D. Meier, Srinath Vasireddy, Ashish Babbar, Rico Mariani, and Alex Mackman

Design Considerations

* Design your data access layer based on how the data is used.
* Cache data to avoid unnecessary work.
* Connect by using service accounts.
* Acquire late, release early.
* Close disposable resources.
* Reduce round trips.
* Return only the data you need.
* Use Windows authentication.
* Choose the appropriate transaction type.
* Use stored procedures.
* Prioritize performance, maintainability, and productivity when you choose how to pass data across layers.
* Consider how to handle exceptions.
* Use appropriate normalization.
* Dispose issues

Microsoft® .NET Framework Data Providers

* Use System.Data.SqlClient for Microsoft SQL Server™ 7.0 and later.
* Use System.Data.OleDb for SQL Server 6.5 or OLE DB providers.
* Use System.Data.ODBC for ODBC data sources.
* Use System.Data.OracleClient for Oracle.
* Use SQLXML managed classes for XML data and SQL Server 2000.

Connections

* Open and close the connection in the method.
* Explicitly close connections.
* When using DataReaders, specify CommandBehavior.CloseConnection.
* Do not explicitly open a connection if you use Fill or Update for a single operation.
* Avoid checking the State property of OleDbConnection.
* Pool connections.

Commands

* Validate SQL input and use Parameter objects.
* Retrieve only the columns and rows you need.
* Support paging over large result sets.
* Batch SQL statements to reduce round trips.
* Use ExecuteNonQuery for commands that do not return data.
* Use ExecuteScalar to return single values.
* Use CommandBehavior.SequentialAccess for very wide rows or for rows with binary large objects (BLOBs).
* Do not use CommandBuilder at run time.

Stored Procedures

* Use stored procedures.
* Use CommandType.Text with OleDbCommand.
* Use CommandType.StoredProcedure with SqlCommand.
* Consider using Command.Prepare.
* Use output parameters where possible.
* Consider SET NOCOUNT ON for SQL Server.

Parameters

* Use the Parameters collection when you call a stored procedure.
* Use the Parameters collection when you build SQL statements.
* Explicitly create stored procedure parameters.
* Specify parameter types.
* Cache stored procedure SqlParameter objects.

DataReader

* Close DataReader objects.
* Consider using CommandBehavior.CloseConnection to close connections.
* Cancel pending data.
* Consider using CommandBehavior.SequentialAccess with ExecuteReader.
* Use GetOrdinal when using an index-based lookup.

DataSet

* Reduce serialization.
* Use primary keys and Rows.Find for indexed searching.
* Use a DataView for repetitive non-primary key searches.
* Use the optimistic concurrency model for datasets.

XML and DataSet Objects

* Do not infer schemas at run time.
* Perform bulk updates and inserts by using OpenXML.

Types

* Avoid unnecessary type conversions.

Exception Management

* Use the ConnectionState property.
* Use try/finally to clean up resources.
* Use specific handlers to catch specific exceptions.

Transactions

* Use SQL transactions for server controlled-transactions on a single data store.
* Use ADO.NET transactions for client-controlled transactions on a single data store.
* Use Distributed Transaction Coordinators (DTC) for transactions that span multiple data stores.
* Keep transactions as short as possible.
* Use the appropriate isolation level.
* Avoid code that can lead to deadlock.
* Set the connection string Enlist property to false.

Binary Large Objects

* Use CommandBehavior.SequentialAccess and GetBytes to read data.
* Use READTEXT to read from SQL Server 2000.
* Use OracleLob.Read to read from Oracle databases.
* Use UpdateText to write to SQL Server databases.
* Use OracleLob.Write to write to Oracle databases.
* Avoid moving binary large objects repeatedly.

---

Return to HomePage