ADO.NET 2.0 Performance Guidelines

New in 2.0

• Use SqlBulkCopy for Faster Transfer or Upload of Bulk Data
• Use Asynchronous Command Execution
• Use Data Adapter Update Batching to Reduce Number of Server Round-trips
• Use MARS as an alternative to opening several connections
• Explicitly Open Connection While Executing Data Adapter's Fill Method Multiple Times
• Use ADO.NET Binary Serialization while Exchanging DataSet Over Network
• Specify CloseConnection For DataReaders To Close Associated Connection Along With The DataReader
• Use SequentialAccess For Very Wide Rows Or For Rows With BLOBs
• Use Asynchronous Commands for Parallel Non-Blocking Executions

---

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