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.

patterns & practices Developer Center

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
Ff647694.checkbox(en-us,PandP.10).gif Design your data access layer based on how the data is used.
Ff647694.checkbox(en-us,PandP.10).gif Cache data to avoid unnecessary work.
Ff647694.checkbox(en-us,PandP.10).gif Connect by using service accounts.
Ff647694.checkbox(en-us,PandP.10).gif Acquire late, release early.
Ff647694.checkbox(en-us,PandP.10).gif Close disposable resources.
Ff647694.checkbox(en-us,PandP.10).gif Reduce round trips.
Ff647694.checkbox(en-us,PandP.10).gif Return only the data you need.
Ff647694.checkbox(en-us,PandP.10).gif Use Windows authentication.
Ff647694.checkbox(en-us,PandP.10).gif Choose the appropriate transaction type.
Ff647694.checkbox(en-us,PandP.10).gif Use stored procedures.
Ff647694.checkbox(en-us,PandP.10).gif Prioritize performance, maintainability, and productivity when you choose how to pass data across layers.
Ff647694.checkbox(en-us,PandP.10).gif Consider how to handle exceptions.
Ff647694.checkbox(en-us,PandP.10).gif Use appropriate normalization.

Microsoft® .NET Framework Data Providers

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Use System.Data.SqlClient for Microsoft SQL Server™ 7.0 and later.
Ff647694.checkbox(en-us,PandP.10).gif Use System.Data.OleDb for SQL Server 6.5 or OLE DB providers.
Ff647694.checkbox(en-us,PandP.10).gif Use System.Data.ODBC for ODBC data sources.
Ff647694.checkbox(en-us,PandP.10).gif Use System.Data.OracleClient for Oracle.
Ff647694.checkbox(en-us,PandP.10).gif Use SQLXML managed classes for XML data and SQL Server 2000.

Connections

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Open and close the connection in the method.
Ff647694.checkbox(en-us,PandP.10).gif Explicitly close connections.
Ff647694.checkbox(en-us,PandP.10).gif When using DataReaders, specify CommandBehavior.CloseConnection.
Ff647694.checkbox(en-us,PandP.10).gif Do not explicitly open a connection if you use Fill or Update for a single operation.
Ff647694.checkbox(en-us,PandP.10).gif Avoid checking the State property of OleDbConnection.
Ff647694.checkbox(en-us,PandP.10).gif Pool connections.

Commands

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

Stored Procedures

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Use stored procedures.
Ff647694.checkbox(en-us,PandP.10).gif Use CommandType.Text with OleDbCommand.
Ff647694.checkbox(en-us,PandP.10).gif Use CommandType.StoredProcedure with SqlCommand.
Ff647694.checkbox(en-us,PandP.10).gif Consider using Command.Prepare.
Ff647694.checkbox(en-us,PandP.10).gif Use output parameters where possible.
Ff647694.checkbox(en-us,PandP.10).gif Consider SET NOCOUNT ON for SQL Server.

Parameters

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Use the Parameters collection when you call a stored procedure.
Ff647694.checkbox(en-us,PandP.10).gif Use the Parameters collection when you build SQL statements.
Ff647694.checkbox(en-us,PandP.10).gif Explicitly create stored procedure parameters.
Ff647694.checkbox(en-us,PandP.10).gif Specify parameter types.
Ff647694.checkbox(en-us,PandP.10).gif Cache stored procedure SqlParameter objects.

DataReader

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Close DataReader objects.
Ff647694.checkbox(en-us,PandP.10).gif Consider using CommandBehavior.CloseConnection to close connections.
Ff647694.checkbox(en-us,PandP.10).gif Cancel pending data.
Ff647694.checkbox(en-us,PandP.10).gif Consider using CommandBehavior.SequentialAccess with ExecuteReader.
Ff647694.checkbox(en-us,PandP.10).gif Use GetOrdinal when using an index-based lookup.

DataSet

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Reduce serialization.
Ff647694.checkbox(en-us,PandP.10).gif Use primary keys and Rows.Find for indexed searching.
Ff647694.checkbox(en-us,PandP.10).gif Use a DataView for repetitive non-primary key searches.
Ff647694.checkbox(en-us,PandP.10).gif Use the optimistic concurrency model for datasets.

XML and DataSet Objects

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Do not infer schemas at run time.
Ff647694.checkbox(en-us,PandP.10).gif Perform bulk updates and inserts by using OpenXML.

Types

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Avoid unnecessary type conversions.

Exception Management

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Use the ConnectionState property.
Ff647694.checkbox(en-us,PandP.10).gif Use try/finally to clean up resources.
Ff647694.checkbox(en-us,PandP.10).gif Use specific handlers to catch specific exceptions.

Transactions

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

Binary Large Objects

Check Description
Ff647694.checkbox(en-us,PandP.10).gif Use CommandBehavior.SequentialAccess and GetBytes to read data.
Ff647694.checkbox(en-us,PandP.10).gif Use READTEXT to read from SQL Server 2000.
Ff647694.checkbox(en-us,PandP.10).gif Use OracleLob.Read to read from Oracle databases.
Ff647694.checkbox(en-us,PandP.10).gif Use UpdateText to write to SQL Server databases.
Ff647694.checkbox(en-us,PandP.10).gif Use OracleLob.Write to write to Oracle databases.
Ff647694.checkbox(en-us,PandP.10).gif Avoid moving binary large objects repeatedly.

patterns & practices Developer Center

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.