Click here to Skip to main content
12,449,919 members (40,625 online)
Click here to Skip to main content
Add your own
alternative version

Stats

92.8K views
26 bookmarked
Posted

Why Caching A DataBase Connection Is A Bad Idea

, 10 Feb 2002
Rate this:
Please Sign up or sign in to vote.
Caching Data in Application or Session objects is a good idea. Caching Database connections in Application or Session objects however is not a good idea. This article explains why and how to best use your database connections.

While caching data in the Application or Session object can be a good idea, caching database connections is usually a bad one.  Take for example the Connection object, if you store a connection in a Session object, you no longer have the benefit of connection pooling. Connection pooling is beneficial when connections are shared across multiple clients and resources are in use only as long as they are needed i.e. If the Connection object is stored in the ASP Session object, then a database connection will be created for every user. Similarly, 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. This 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 strSQL, strProv

' Now disconnect the recordset from the data provider and data source
rs.ActiveConnection = Nothing    ' step 2

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Ashish Patel
Web Developer
United States United States
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralConnection pooling Pin
Anonymous11-Feb-02 10:55
memberAnonymous11-Feb-02 10:55 
GeneralRe: Connection pooling Pin
Anonymous12-Feb-02 0:16
memberAnonymous12-Feb-02 0:16 
GeneralI must respectfully disagree... Pin
Anonymous11-Feb-02 3:53
memberAnonymous11-Feb-02 3:53 
GeneralRe: I must respectfully disagree... Pin
Softomatix11-Feb-02 5:05
memberSoftomatix11-Feb-02 5:05 
GeneralRe: I must respectfully disagree... Pin
Ian Griffiths11-Feb-02 6:15
memberIan Griffiths11-Feb-02 6:15 
GeneralRe: I must respectfully disagree... Pin
Anonymous11-Feb-02 18:09
memberAnonymous11-Feb-02 18:09 
GeneralRe: I must respectfully disagree... Pin
Anonymous19-Feb-02 2:38
memberAnonymous19-Feb-02 2:38 
GeneralA great tip! Pin
tommy skaue11-Feb-02 3:21
membertommy skaue11-Feb-02 3:21 
GeneralAnother reason. Pin
Daniel Turini11-Feb-02 2:31
memberDaniel Turini11-Feb-02 2:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160826.1 | Last Updated 11 Feb 2002
Article Copyright 2002 by Ashish Patel
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid