Checklist: ADO.NET Performance

 

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Improving .NET Application Performance and Scalability

J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
Microsoft Corporation

May 2004

Related Links

Home Page for Improving .NET Application Performance and Scalability

Send feedback to Scale@microsoft.com

patterns & practices Library

How to Use This Checklist

This checklist is a companion to Chapter 12, "Improving ADO.NET Performance"

Design Considerations

Check	Description
	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.

Microsoft® .NET Framework Data Providers

Check	Description
	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

Check	Description
	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

Check	Description
	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

Check	Description
	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

Check	Description
	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

Check	Description
	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

Check	Description
	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

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

Types

Check	Description
	Avoid unnecessary type conversions.

Exception Management

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

Transactions

Check	Description
	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

Check	Description
	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.

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

© Microsoft Corporation. All rights reserved.