ADO.NET 1.1 Performance Guidelines

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, 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

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

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 BLOBs
• Do not use CommandBuilder at run time

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

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

Exception Management

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

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

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

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

XML and DataSet Objects

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

References

• See Improving ADO.NET Performance at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/ScaleNetChapt12.asp