ASP.NET and SQL Server Performance Tips
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). It can be used to access SQL Serve 7.0, SQL Server 2000, and SQL Server 2005 databases. [7.0, 2000, 2005] Updated 3-6-2006
*****
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. [7.0, 2000, 2005] Updated 3-6-2006
*****
Take full advantage of connection pooling. To get the most out of connection pooling in ADO.NET, keep the following in mind when developing your ASP.NET applications:
- Be sure that your connections use the same connection string each time. Connection pooling only works if the connection string is the same. If the connection string is different, then a new connection will be opened, and connection pooling will not be used.
- Only open a connection when you need it, not before.
- Close your connection as soon as you are done using it.
- Do not leave a connection open if it is not being used.
- Be sure to drop any temporary objects before closing a connection.
- Be sure to close any user-defined transactions before closing a connection.
- Do not use application roles if you want to take advantage of connection pooling.
[7.0, 2000, 2005] Updated 3-6-2006
*****
If you will be selecting data from a SQL Server database for non-interactive display only on a web page, you should consider using the SQLDataReader for best performance if you are not using a stored procedure. When using a SQLDataReader, you should select records from your SQL Server database using a SQLCommand query and create a SQLDataReader that is returned from the SQLCommand object’s ExecuteReader method. [7.0, 2000, 2005] Updated 3-6-2006
*****
If you need to retrieve data from multiple tables from a SQL Server database, the most efficient way is to retrieve it using a stored procedure that retrieves and joins the data as necessary, and then sending the results to the application for formatting. This is much more efficient that sending two or more queries to SQL Server, returning two or more tables at the application, and then combining the results of the two or more tables there. [7.0, 2000, 2005] Updated 3-6-2006