Optimizing the Performance of Database Access in IIS
Data access and retrieval is often the most challenging performance area for the Web application developer.
Many of the scalability and performance concerns that arise around data access are not in your control, as a Web application developer. However, there are some techniques that you can use to maximize your performance:
Cache results from data sources that are stable, or that vary predictably. You can cache either the recordset returned by a query to the data source, or cache the pure HTML output that was created using the results of the query.
For instance, if you are using ADO to populate a listbox that will contain the cities in which you have offices, the first caller to ADO can insert the ADO query results into Application scope. Subsequent requests for that listbox information could be fulfilled from the Application object, instead of an expensive call, through ADO, to a data source.
If you wish to cache the resultant recordset directly, you should use a client-side cursor, and disassociate the recordset from the Command object by setting the ADO ActiveConnection property to Nothing.
For more information about data caching, see Caching Data.
In general, avoid putting ADO connections in session state, because ODBC (version 3.0 and later) automatically does connection pooling for you, and OLE DB provides session pooling.
Use the native OLE DB connection strings as much as possible. Native OLE DB connection strings are generally faster than most ODBC DNSs. In addition, OLE DB connection strings are more flexible because your application will be able to make use of any OLE DB provider.
If using a data source that supports them, such as Microsoft SQL Server, use stored procedures whenever possible. A query executed from a stored procedure is faster than a query passed through a SQL query string.
To optimize performance, avoid using the ADO record addition and deletion methods, such as AddNew and Delete. If your application adds and deletes records intensively, your application will perform better if it uses direct SQL statements, such as INSERT.
Set the ADO CacheSize property to a larger number than the default (1). By forcing ADO to retrieve multiple records in one transaction with the data source, you will eliminate a portion of the overhead involved in that transaction, and your application may become more scalable. Generally, you are most likely to see benefits if you set CacheSize to equal to either the number of records expected, or 100, whichever is less.
Use the ADO 2.0 AdExecuteNoRecords flag when executing commands that don't return data rows, or that return rows that you don't need to access or save. This new feature, introduced in ADO 2.0, was created to reduce the amount of overhead incurred by ADO, and thus increase performance and scalability.
Disable temporary stored procedures, if your data source supports them.