Caching ADO Connections is usually a bad strategy. If one Connection object is stored in the Application object and used on all pages, then all pages will contend for use of this connection. If the Connection object is stored in the ASP Session object, then a database connection will be created for every user. This defeats the benefits of connection pooling and puts unnecessarily high stress on both the Web server and the database.
Instead of caching database connections, create and destroy ADO objects on every ASP page that uses ADO. This is efficient because IIS has database connection pooling built in. More accurately, IIS automatically enables OLEDB and ODBC connection pooling. This ensures that creating and destroying connections on each page will be efficient.
Since connected recordsets store a reference to a database connection, it follows that you should not cache connected recordsets in the Application or Session objects. However, you can safely cache disconnected recordsets, which don't hold a reference to their data connection. To disconnect a recordset, take the following two steps:
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.CursorLocation = adUseClient ' step 1
' Populate the recordset with data
rs.Open strQuery, strProv
' Now disconnect the recordset from the data provider and data source
rs.ActiveConnection = Nothing ' step 2
More information about connection pooling can be found in the ADO and SQL Server references.
Now that we've espoused the virtues of caching in Applications and Sessions, we're going to suggest avoiding the Session object. Sessions have several pitfalls when used with busy sites, as we'll discuss. By busy, we generally mean sites requiring hundreds of pages a second or thousands of simultaneous users. This tip is even more important for sites that must scale horizontally—that is, those sites that utilize multiple servers to accommodate load or implement fault tolerance. For smaller sites, such as intranet sites, the conveniences of Sessions are worth the overhead.
To recap, ASP automatically creates a Session for every user that hits a Web server. Each Session has about 10 KB of memory overhead (on top of whatever data is stored in the Session), and slows all requests down a bit. The Session stays alive until a configurable timeout period, usually 20 minutes.
The biggest issue with Sessions is not performance but scalability. Sessions don't span Web servers; once a Session is created on one server, its data stays there. This means that if you use Sessions in a Web farm, you have to devise a strategy for each user's requests to always be directed to the server on which the user's Session exists. This is referred to as "sticking" a user to a Web server. The term "sticky sessions" derives from this. "Stuck" users will lose their Session state if the Web server crashes, since Sessions are not persisted to disk.
Strategies for implementing sticky sessions include hardware and software solutions. Solutions such as Network Load Balancing in Windows 2000 Advanced Server and Cisco's Local Director can implement sticky sessions, at the cost of some scalability. These solutions are not perfect. Rolling your own software solution at this point of time is not recommended (we used to use ISAPI filters and URL mangling and such).
The Application object doesn't span servers either; if you need to share and update Application data across the Web farm, you'll need to use a back-end database. Read-only Application data is still useful in Web farms, however.
Most mission-critical sites will want to deploy at least two Web servers, if for no other reason than increasing uptime (handling failover and server maintenance). Therefore, in designing your mission-critical application, you'll need to either implement "sticky sessions," or simply avoid Sessions, as well as any other state-management technique that stores user state on individual Web servers.
If you are not using Sessions, be sure to turn them off. You can do this for your application through the Internet Services Manager (see the ISM documentation). If you decide to use Sessions, you can minimize their performance impact in several ways.
You can move content that doesn't require Sessions (such as Help screens, visitor areas, and so forth.) into a separate ASP application that has Sessions turned off. On a page-by-page basis, you can provide a hint to ASP that you won't need the Session object on a given page; use the following directive placed at the top your ASP page:
<% @EnableSessionState=False %>
One good reason to use this directive is that Sessions create an interesting problem with framesets. ASP guarantees that only one request from a Session will be executing at any time. This insures that if a browser requests multiple pages for one user, only one ASP request will touch the Session at a time; this avoids multithreading problems when accessing the Session object. Unfortunately, as a result, all pages in a frameset will be painted in a serialized manner, one after another, rather than simultaneously. The user may have to wait a long time for all of the frames. The moral of the story: if certain frameset pages have no reliance on the Session, be sure to tell ASP, using the
As an alternative to using the Session object, there are many options for managing Session state. For small amounts of state (less than 4 KB), we usually recommend using Cookies, QueryString variables, and hidden-form variables. For larger amounts of data such as shopping carts, a back-end database is the most appropriate choice. A lot has been written about state-management techniques in Web server farms. See the Session state references for more details.