Miscellaneous tips for improving ASP performance and robustness

Here’s a collection of quick-and-dirty tips for improve the performance and the robustness of your ASP applications:

Use the Option Explicit directive to ensure that all variables are correctly declared and scoped. Mistyping a variable name is one of the most frequent causes of bugs inside ASP and VB applications.

Set objects to Nothing as soon as you don’t need them any longer. This is especially effective under IIS5, that immediately releases the memory taken by the object.

Explicitly close all ADO Connection and Recordset objects as soon as you don’t need them any longer. As explained above, setting an object to Nothing under IIS4 and previous versions doesn’t automatically release the object, therefore you can’t count on ADO closing the Connection or the Recordset automatically when you set it to Nothing (unlike what happens in a regular VB application).

Use SQL Server 7.0 or another real database engine, instead of Microsoft Access, which is simply not ready for intensive concurrent access, such as those that most ASP sites have.

Use ADO Command objects for queries that must be run more than once in a given ASP file. When working with SQL Server 7, Command objects build temporary stored procedures that are compiled once and reused any number of times, until the Command object is set to Nothing. However, you should never reuse the same Command object for two different queries, because this can lead to system crashes, at least when working with parameterized stored procedures under ADO 2.0.

Use forward-only, read-only ADO Recordsets whenever possible. When working under ASP it makes sense to use a server-side or client-side cursor-based Recordset, and firward-only, read-only Recordsets – the so called firehose cursor-less Recordsets – are nearly always the most efficient choice.

If you must use a cursor-based Recordset, don’t ask for more features than you actually need. For example, always specify LockType = adLockReadOnly if you don’t plan to update the database (or if you can do it through direct UPDATE or INSERT SQL statements). Don’t create a server-side dynamic cursor if you can be satisfied with a keyset cursor. And so on.

Limit the data you read from the database by specifying column names in the SELECT statement. Stay clear of the SELECT * FROM table syntax and carefully list only the fields that you’re actually going to use. This is probably one of the simplest and most effective optimization techniques.

Use native OLE DB providers if possible. Usually they are faster and more robust than the OLE DB provider for ODBC sources used with the corresponding ODBC driver. This point is especially important because the OLE DB provider for ODBC is the default ADO provider, and will be used unless you explicitly specify a different provider.

Ensure that you use the same connection string for all the connections to your database. It is especially important that the string is identical, because ADO performs a character-by-character comparison before deciding whether it can reuse a connection in the connection pool, which can save you several seconds any time you open a new connection. The best way to ensure that you’re always using the same connection string is to store it into an Application variable in Global.asa, or use an include file that is referenced from inside all the ASP scripts that open a connection.

Don’t store ADO objects – such as a Connection or a Recordset object – inside an Application or Session variable. While doing so doesn’t raise any error, it has an adverse effect on the scalability of your application, because all the requests to the Connection or Recordset are executed serially, therefore an ASP script using the object must wait until the object isn’t being used by any other ASP page. It’s far better create a new Connection or Recordset page in each page that use it, and destroy it before the pages completes its execution.

When using the OLE DB Provider for ODBC sources, use DSN-less connennection strings, which are more efficient and scalable than User and System DSNs, which in turn are faster than File DSNs. When using native OLE DB Providers, use connection string that don’t reference an UDL files.