ADO.NET 1.1 Performance Guidelines
From Guidance Share
- J.D. Meier, Srinath Vasireddy, Ashish Babbar, 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
[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]
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
[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]
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]
Exception Management
- Use the ConnectionState property
- Use try/finally to clean up resources
- Use specific handlers to catch specific exceptions
[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]
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]
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
[edit]
XML and DataSet Objects
[edit]
References
- See Improving ADO.NET Performance at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/ScaleNetChapt12.asp