Ten points to consider to increase performance in ADO.NET

Here is a consolidated list of how you can improve the performance of your .NET apps:

1. Design your data access layer based on how the data is used.

In larger applications, it’s always better to go for a separate Data Access Layer (DAL) to abstract the underlying data store complexity and to provide a logical separation. Having the data access logic in the same presentation layer may increase performance but at the cost of maintainability.

You can use the Microsoft Application Blocks for simplifying your tasks of Data Access and Exception handling. (I personally prefer this)

2. Cache your data to avoid unnecessary round trips and network overhead.

Try to cache data that is used across your application, in the layer that is close to the consumer of data. This will reduce the latency in network to fetch data. Note that if your data in cache needs to be updated too frequently, then better you don’t cache that data.

3. Acquire late, release early.

Open database connections right only when you need them. Close the database connections as soon as you are finished. Acquire locks late, and release them early.

4. Close disposable resources.

Make sure that you call either the Dispose or Close method on resources that are disposable, as soon as you are finished with using the resource.

5. Reduce round trips.

· If you have some 3 or 4 SQL statements, try to use batch sql statements in a stored procedure to decrease round trips.
· Use ExecuteScalar method for getting a single result.
· Use ExecuteNonQuery method when you want to execute any DDL statement.
· Use connection pooling to help avoid extra round trips. By reusing connections from a connection pool, you can avoid the round trips that are associated with connection establishment and authentication.

6.Return only the data you need.

Evaluate the data that your application actually requires and return only that data. This will minimize the bandwidth consumption in the network.

7. Use Windows authentication.

From a security perspective, use Windows authentication instead of SQL authentication. This ensures that credentials are not passed over the network, database connection strings do not contain credentials, and you can apply standard Windows security policies to accounts. Remember to use connection pooling with your connection.

8. Use stored procedures.

Avoid embedded SQL statements and use Store Procedures (SP) instead. This has the following advantages:
· A Logical separation of Data access code from your Business Logic code
· Queries can be optimized for performance from SQL server
· Deployment becomes easier as, for any change in SP you don’t need to redeploy you application.
· SPs allow the batch execution of SQL commands
· You can impose specific restrictions on selected stored procedures for security reasons. This is very difficult to be done from embedded SQL.
· You can avoid the most dangerous SQL Injection by using parameterized SPs.

9. Consider how to handle exceptions.

You can use try/finally blocks to ensure that connections and other resources are closed, regardless of whether exceptions are generated or not. The best way for abstracting all exceptions from user is to log them to a file or Windows Event log.

10. Use appropriate normalization.

You may want a normalized database to minimize data duplication but be aware that you don’t over-normalize. This can affect the performance and scalability of your application

The above list is purely based on my working experience and there are many more points to add to this, which I’ll be adding in the next part of this article.