One way by which you improve your performance is by determining whether to use DataSet or DataReader. Both of them has advantages & disadvantages, one can say Dataset as a Temperary Database for each client, filtered by that client on the server working on a disconnected architecture. Where as Datareader is similar to a Recordset of VB 6.0 or Clasical ASP with Forwardonly cursor. While working with Dataset Connection is closed where as in Datareader Connection is maintained.
When to consider using a DataReader:
•The DataReader is a better choice for applications that require optimized read-only and forward-only access to data such as binding to a DataGrid control. The sooner the data is off-loaded from the DataReader and the connection is closed the better the application performance.
•The DataReader is a better choice when you are planning to make repeated calls to retrieve small amounts of information or the data you are retrieving must be as up to date as possible each time it is used.
When to consider using a DataSet:
•The DataSet is a better choice for applications that will not off-load the query result immediately, or when there is extensive processing such as complex business logic involved between data accesses. The DataSet will retrieve the data, off-load the data into memory and return the database connection to the connection pool, where as a DataReader would keep the connection locked open until processing is complete. This could easily cause a high traffic application to run out of available database connections.
•The DataSet is a better choice when you need to navigate through the data more than once. For example, if you have multiple controls you need to build off the same data, then a DataSet is the better answer because a DataReader can only be read once so it can only be bound to a single control and would require the data to be retrieved for use with each control.
•The DataSet is a better choice when the data does not change frequently enough to warrant always retrieving it from the database or is specific to the user requesting the data. A DataSet can be stored in Session or Application variables or cached through the System.Web.Caching.Cache class to improve application performance by not having to retrieve the data from the database each time it is needed.
•The DataSet is a better choice when building a Web service that will return the retrieved data. Since a DataSet is serializable it can serve as the return value. Since a DataReader requires a persistent database connection, it cannot be used as a return type from a Web service.
Some more tips (extracted from http://www.sql-server-performance.com/tips/asp_net_performance_p1.aspx) hav ea look at this site to get more details, real good one!
Take advantage of the SQL Server .NET data provider to access SQL Server data using ADO.NET, as it offers the best overall performance (as compared to previous technologies).
Whenever accessing SQL Server data, consider always using a stored procedure if you want maximum performance. This is true whether you are selecting, inserting, updating, or deleting data. Avoid using ADO.NET's methods to access SQL Server data, as they will always be slower than accessing SQL Server data directly with a stored procedure.
Take full advantage of connection pooling
from the Patterns & Practices "Improving .NET Application Performance and Scalability" guide:
Checklist: SQL Server Performance - http://msdn2.microsoft.com/en-us/library/ms979169.aspx
Checklist: ASP.NET Performance -
http://msdn2.microsoft.com/en-us/library/ms998596.aspx