ADO.NET 1.1 Performance Checklist

From Guidance Share

Jump to: navigation, search

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

Contents

[edit]

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
[edit]

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.
[edit]

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.
[edit]

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.
[edit]

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.
[edit]

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.
[edit]

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.
[edit]

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.
[edit]

XML and DataSet Objects

  • Do not infer schemas at run time.
  • Perform bulk updates and inserts by using OpenXML.
[edit]

Types

  • Avoid unnecessary type conversions.
[edit]

Exception Management

  • Use the ConnectionState property.
  • Use try/finally to clean up resources.
  • Use specific handlers to catch specific exceptions.
[edit]

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.
[edit]

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.
[edit]

Resources

Retrieved from "http://guidanceshare.com/wiki/ADO.NET_1.1_Performance_Checklist"
Personal tools