Export (0) Print
Expand All

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

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifDesign your data access layer based on how the data is used.
Ff647694.checkbox(en-us,PandP.10).gifCache data to avoid unnecessary work.
Ff647694.checkbox(en-us,PandP.10).gifConnect by using service accounts.
Ff647694.checkbox(en-us,PandP.10).gifAcquire late, release early.
Ff647694.checkbox(en-us,PandP.10).gifClose disposable resources.
Ff647694.checkbox(en-us,PandP.10).gifReduce round trips.
Ff647694.checkbox(en-us,PandP.10).gifReturn only the data you need.
Ff647694.checkbox(en-us,PandP.10).gifUse Windows authentication.
Ff647694.checkbox(en-us,PandP.10).gifChoose the appropriate transaction type.
Ff647694.checkbox(en-us,PandP.10).gifUse stored procedures.
Ff647694.checkbox(en-us,PandP.10).gifPrioritize performance, maintainability, and productivity when you choose how to pass data across layers.
Ff647694.checkbox(en-us,PandP.10).gifConsider how to handle exceptions.
Ff647694.checkbox(en-us,PandP.10).gifUse appropriate normalization.

Microsoft® .NET Framework Data Providers

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

Connections

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifOpen and close the connection in the method.
Ff647694.checkbox(en-us,PandP.10).gifExplicitly close connections.
Ff647694.checkbox(en-us,PandP.10).gifWhen using DataReaders, specify CommandBehavior.CloseConnection.
Ff647694.checkbox(en-us,PandP.10).gifDo not explicitly open a connection if you use Fill or Update for a single operation.
Ff647694.checkbox(en-us,PandP.10).gifAvoid checking the State property of OleDbConnection.
Ff647694.checkbox(en-us,PandP.10).gifPool connections.

Commands

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifValidate SQL input and use Parameter objects.
Ff647694.checkbox(en-us,PandP.10).gifRetrieve only the columns and rows you need.
Ff647694.checkbox(en-us,PandP.10).gifSupport paging over large result sets.
Ff647694.checkbox(en-us,PandP.10).gifBatch SQL statements to reduce round trips.
Ff647694.checkbox(en-us,PandP.10).gifUse ExecuteNonQuery for commands that do not return data.
Ff647694.checkbox(en-us,PandP.10).gifUse ExecuteScalar to return single values.
Ff647694.checkbox(en-us,PandP.10).gifUse CommandBehavior.SequentialAccess for very wide rows or for rows with binary large objects (BLOBs).
Ff647694.checkbox(en-us,PandP.10).gifDo not use CommandBuilder at run time.

Stored Procedures

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

Parameters

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifUse the Parameters collection when you call a stored procedure.
Ff647694.checkbox(en-us,PandP.10).gifUse the Parameters collection when you build SQL statements.
Ff647694.checkbox(en-us,PandP.10).gifExplicitly create stored procedure parameters.
Ff647694.checkbox(en-us,PandP.10).gifSpecify parameter types.
Ff647694.checkbox(en-us,PandP.10).gifCache stored procedure SqlParameter objects.

DataReader

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifClose DataReader objects.
Ff647694.checkbox(en-us,PandP.10).gifConsider using CommandBehavior.CloseConnection to close connections.
Ff647694.checkbox(en-us,PandP.10).gifCancel pending data.
Ff647694.checkbox(en-us,PandP.10).gifConsider using CommandBehavior.SequentialAccess with ExecuteReader.
Ff647694.checkbox(en-us,PandP.10).gifUse GetOrdinal when using an index-based lookup.

DataSet

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifReduce serialization.
Ff647694.checkbox(en-us,PandP.10).gifUse primary keys and Rows.Find for indexed searching.
Ff647694.checkbox(en-us,PandP.10).gifUse a DataView for repetitive non-primary key searches.
Ff647694.checkbox(en-us,PandP.10).gifUse the optimistic concurrency model for datasets.

XML and DataSet Objects

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifDo not infer schemas at run time.
Ff647694.checkbox(en-us,PandP.10).gifPerform bulk updates and inserts by using OpenXML.

Types

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifAvoid unnecessary type conversions.

Exception Management

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

Transactions

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

Binary Large Objects

CheckDescription
Ff647694.checkbox(en-us,PandP.10).gifUse CommandBehavior.SequentialAccess and GetBytes to read data.
Ff647694.checkbox(en-us,PandP.10).gifUse READTEXT to read from SQL Server 2000.
Ff647694.checkbox(en-us,PandP.10).gifUse OracleLob.Read to read from Oracle databases.
Ff647694.checkbox(en-us,PandP.10).gifUse UpdateText to write to SQL Server databases.
Ff647694.checkbox(en-us,PandP.10).gifUse OracleLob.Write to write to Oracle databases.
Ff647694.checkbox(en-us,PandP.10).gifAvoid 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.

Show:
© 2014 Microsoft