1. Return Multiple Resultsets
Using a single Data Reader we can execute multiple queries simultaneously.
http://www.ondotnet.com/pub/a/dotnet/2002/12/16/multiresultsets_1202.html?page=1
2. Paged Data Access through SP
3. Connection Pooling and Object Pooling
Connection pooling is a useful way to reuse connections for multiple requests, rather than paying the overhead of opening and closing a connection for each request. It's done implicitly, but you get one pool per unique connection string. If you're generating connection strings dynamically, make sure the strings are identical each time so pooling occurs. Also be aware that if delegation is occurring, you'll get one pool per user. There are a lot of options that you can set for the connection pool, and you can track the performance of the pool by using the Perfmon to keep track of things like response time, transactions/sec, etc.
4. Parameters while Inserting/Updating
5. Use SqlDataReader Instead of Dataset wherever it is possible
a. For retrieving large data from DB
b. Data with no relations.
6. Turn off Features You Don't Use
A pooled transactional object must enlist its connection into the current transaction manually. To enable it to do so, you must disable automatic transaction enlistment by setting the connection string Enlist property to False.
Turn off automatic transaction enlistment if it's not needed. For the SQL Managed Provider, it's done via the connection string:
SqlConnection conn = new SqlConnection("Server=mysrv01;Integrated Security=true; Enlist=false");
When filling a dataset with the data adapter, don't get primary key information if you don't have to (e.g. don't set MissingSchemaAction.Add with key):
public DataSet SelectSqlSrvRows(DataSet dataset,string connection,string query)
{
SqlConnection conn = new SqlConnection(connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(query, conn);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(dataset);
return dataset;
}
7. Keep Your Datasets Lean
Only put the records you need into the dataset. Remember that the dataset stores all of its data in memory, and that the more data you request, the longer it will take to transmit across the wire.
8. Use Sequential Access as Often as Possible
With a data reader, use CommandBehavior.SequentialAccess. This is essential for dealing with blob data types since it allows data to be read off of the wire in small chunks. While you can only work with one piece of the data at a time, the latency for loading a large data type disappears. If you don't need to work the whole object at once, using Sequential Access will give you much better performance.
9. CommandBehavior.CloseConnection
We don't need to specify explicitly something like close because we specified this in SqlCommand.ExecuteReader as CommandBehavior.CloseConnection. This option automatically closes the connection after we have finished with our work. Any exceptions thrown will be caught and displayed in the error message.
http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
10. Inefficient queries. Queries that process and then return more columns or rows than necessary waste processing cycles that could best be used for servicing other requests. Queries that do not take advantage of indexes may also cause poor performance.
11. Retrieving too much data. Too much data in your results is usually the result of inefficient queries. The SELECT * query often causes this problem. You do not usually need to return all the columns in a row. Also, analyze the WHERE clause in your queries to ensure that you are not returning too many rows. Try to make the WHERE clause as specific as possible to ensure that the least number of rows are returned.
12. Inefficient or missing indexes. Query efficiency decreases when indexes are missing because a full table scan must be performed. Also, as your data grows, tables may become fragmented. Failure to periodically rebuild indexes may also result in poor query performance.
13. Unnecessary round trips. Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance. Keep round trips to an absolute minimum.
14. Too many open connections. Connections are an expensive and scarce resource, which should be shared between callers by using connection pooling. Opening a connection for each caller limits scalability. To ensure the efficient use of connection pooling, avoid keeping connections open and avoid varying connection strings.
15. Failure to release resources. Failing to release resources can prevent them from being reused efficiently. If you fail to close connections before the connections fall out of scope, they are not reclaimed until garbage collection occurs for the connection. Failing to release resources can cause serious resource pressure and lead to shortages and timeouts.
16. Transaction misuse. If you select the wrong type of transaction management, you may add latency to each operation. Additionally, if you keep transactions active for long periods of time, the active transactions may cause resource pressure. Transactions are necessary to ensure the integrity of your data, but you need to ensure that you use the appropriate type of transaction for the shortest duration possible and only where necessary.
17. Overnormalized tables. Overnormalized tables may require excessive joins for simple operations. These additional steps may significantly affect the performance and scalability of your application, especially as the number of users and requests increases.
18. Avoid Moving Binary Large Objects Repeatedly
Avoid moving BLOB data more than one time. For example, if you build a Web application that serves images, store the images on the file system and the file names in the database instead of storing the images as BLOBs in the database.
Storing the images as BLOBs in the database means that you must read the BLOB from the database to the Web server and then send the image from the Web server to the browser. Reading the file name from the database and having the Web server send the image to the browser reduces the load on the database server. It also reduces the data that is sent between the database and the Web server. This can significantly affect performance and scalability.
18. Use the ConnectionState property.
Avoid relying on an error handler to detect connection state availability. When you can, use the ConnectionState.Open or ConnectionState.Close method to check the state before use.
19. Reduce Serialization
Dataset serialization is more efficiently implemented in .NET Framework version 1.1 than in version 1.0. However, Dataset serialization often introduces performance bottlenecks. You can reduce the performance impact in a number of ways:
a. Use column name aliasing. The serialized data contains column names so that you can use column name aliasing to reduce the size of the serialized data.
b. Avoid serializing multiple versions of the same data. The DataSet maintains the original data along with the changed values. If you do not need to serialize new and old values, call AcceptChanges before you serialize a DataSet to reset the internal buffers.
c. Reduce the number of DataTable objects that are serialized. If you do not need to send all the DataTable objects contained in a DataSet, consider copying the DataTable objects you need to send into a separate DataSet.
20. Cancel Pending Data
When you call the Close method, the method does not return until all the remaining data has been fetched. If you know you have pending data when you want to close your DataReader, you can call the Cancel method before you call Close to tell the server to stop sending data.
This approach does not always result in a performance improvement, because Cancel is not guaranteed to make the server stop sending data. Control information is still exchanged after the call to Cancel, and the control information may or may not be interleaved with leftover data. Therefore, before you restructure your code to call Cancel before Close, test Cancel to learn if it actually helps in your particular scenario and to learn if you really need the extra performance at the expense of readability.
Note If you need output parameters, do not call Close until you have retrieved the output parameters. After you retrieve the output parameters, you can then call Close.
21. Consider SET NOCOUNT ON for SQL Server
When you use SET NOCOUNT ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When you use SET NOCOUNT OFF, the count is returned. Using SET NOCOUNT ON can improve performance because network traffic can be reduced. SET NOCOUNT ON prevents SQL Server from sending the DONE_IN_PROC message for each statement in a stored procedure or batch of SQL statements.
For example, if you have eight operations in a stored procedure, eight messages are returned to the caller. Each message contains the number of rows affected by the respective statement. When you use SET NOCOUNT ON, you reduce the processing that SQL Server performs and the size of the response that is sent across the network.
Note: In Query Analyzer, the DONE_IN_PROC message is intercepted and displayed as "N rows affected.
22. Do Not Use CommandBuilder at Run Time
CommandBuilder objects such as SqlCommandBuilder and OleDbCommandBuilder automatically generate the InsertCommand, UpdateCommand, and DeleteCommand properties of a DataAdapter. The CommandBuilder objects generate these properties based on the SelectCommand property of the DataAdapter. CommandBuilder objects are useful when you are designing and prototyping your application. However, you should not use them in production applications. The processing required to generate the commands affects performance. Manually create stored procedures for your commands, or use the Visual Studio® .NET design-time wizard and customize them later if necessary.
23. Pool Connections
Creating database connections is expensive. You reduce overhead by pooling your database connections. Make sure you call Close or Dispose on a connection as soon as possible. When pooling is enabled, calling Close or Dispose returns the connection to the pool instead of closing the underlying database connection.
You must account for the following issues when pooling is part of your design:
a. Share connections. Use a per-application or per-group service account to connect to the database. This creates a single pool or a small number of pools, and it enables many client requests to share the same connections.
b. Avoid per-user logons to the database. Each logon creates a separate pooled connection. This means that you end up with a large number of small pools. If you need a different user for each connection, disable pooling or set a small maximum size for the pool.
c. Do not vary connection strings. Different connection strings generate different connection pools. For example, using different capitalization, extra spaces, or different ordering of attributes causes connections to go to different pools. The SQL Server .NET Data Provider performs a byte-by-byte comparison to determine whether connection strings match.
d. Release connections. Do not cache connections. For example, do not put them in session or application variables. Close connections as soon as you are finished with them. Busy connections are not pooled.
e. Passing connections. Do not pass connections between logical or physical application layers.
f. Consider tuning your pool size if needed. For example, in the case of the .NET Framework Data Provider for SQL Server, the default minimum pool size is zero and the maximum is 100. You might need to increase the minimum size to reduce warm-up time. You might need to increase the maximum size if your application needs more than 100 connections.
g. Connection pools are managed by the specific database provider. SqlClient, OleDB client, and third-party clients may provide different configuration and monitoring options.
The following list details the pooling mechanisms that are available, and it summarizes pooling behavior for the .NET Framework data providers:
h. The .NET Framework Data Provider for SQL Server pools connections by using a pooling mechanism implemented in managed code. You control pooling behaviors such as lifetime and pool size through connection string arguments.
i. The .NET Framework Data Provider for Oracle also pools connections by using a managed code solution.
j.The .NET Framework Data Provider for OLE DB automatically uses OLE DB session pooling to pool connections. You control pooling behavior through connection string arguments.
k. The .NET Framework Data Provider for ODBC uses ODBC connection pooling.
24. Do Not Explicitly Open a Connection if You Use Fill or Update for a Single Operation
If you perform a single Fill or Update operation, do not open the connection before you call the Fill method, because the DataAdapter automatically opens and closes the connection for you. The following code fragment shows how to call Fill.
The SqlDataAdapter automatically opens the connection, runs the selected command, and then closes the connection when it is finished. This enables the connection to be open for the shortest period of time.
Note that if you need to perform multiple file or update operations, you need to open the connection before the first Fill or Update method and close it after the last one. Alternatively, you could wrap multiple Fill or Update operations inside a C# using block to ensure that the connection is closed after the last use
25. Explicitly Close Connections
Explicitly call the Close or Dispose methods on SqlConnection objects as soon as you finish using them to release the resources that they use. Do not wait for the connection to fall out of scope. The connection is not returned to the pool until garbage collection occurs. This delays the reuse of the connection and negatively affects performance and scalability. The following are guidelines to consider. These guidelines are specific to SqlConnection because of the way it is implemented. These guidelines are not universal for all classes that have Close and Dispose functionality.
a. Using either the Close method or the Dispose method is sufficient. You do not have to call one method after the other. There is no benefit to calling one method after the other.
b. Dispose internally calls Close. In addition, Dispose clears the connection string.
c. If you do not call Dispose or Close, and if you do not use the using statement, you are reliant upon the finalization of the inner object to free the physical connection.
d. Use the using statement, instead of Dispose or Close, when you are working with a single type, and you are coding in Visual C#®. Dispose is automatically called for you when you use the using statement, even when an exception occurs.
e. If you do not use the using statement, close connections inside a finally block. Code in the finally block always runs, regardless of whether an exception occurs.
f. You do not have to set the SqlConnection reference to null or Nothing because there is no complex object graph. Setting object references to null or to Nothing is usually done to make a graph of objects unreachable.
Note: Closing a connection automatically closes any active DataReader objects that are associated with the connection.
26. Using the "using" statement
The using statement can be used to specify a boundary for the object outside of which, the object is destroyed automatically. The runtime invokes the Dispose method of the objects that are specified within this statement when the control comes out of this block. This is why this is a preferred choice when using exceptions for managing resources in .NET. Refer to the following code that uses the "using" statement:
string connectionString = ...; // Some connection string
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
//Some code
}
Note that when the end of the using block would be encountered, the Dispose () method will be immediately called on the instance. Note that when the Dispose() method is called on this connection instance, it checks to see if the connection is opened; if open it would close it implicitly prior to disposing off the instance.
27. The EnforceConstraints property of the dataset to false to turn off the constraints checking. Then use BeginLoadData and EndLoadData methods to turn off the index maintenance for faster processing in a dataset that needs to be populated from the database.
28. When you are no longer using an object it can be disposed, which is when the dispose method is called. When an object is disposed it should release any resources it is holding. I.e. closing database connections, destroying large structures it might be holding like a Data Table etc. The object itself is still in memory though and periodically .net will "garbage collect" which is when it removes all unused (ie disposed) objects from memory. It is this garbage collection that is when the finalize event is called, ie just before it is to be physically removed from memory.
