ADO.NET 1.1 Performance Checklist
From Guidance Share
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]