Re: Question about Connection Pooling in ASP

• Previous message: Bob Barrows [MVP]: "Re: Set password on database"
• In reply to:(deleted message) Dr. Know: "Question about Connection Pooling in ASP"
• Next in thread: Dr. Know: "Re: Question about Connection Pooling in ASP"
• Reply:(deleted message) Dr. Know: "Re: Question about Connection Pooling in ASP"
• Messages sorted by: [ date ] [ thread ]

Date: Sat, 22 Jan 2005 13:48:46 -0500

Dr. Know wrote:
> I have completed an Web App that accesses numerous databases and want
> to ensure that connection pooling is being fully utilized

In ASP, you almost have to deliberately turn off connection pooling in order
not to use it. It's on by default. However, there are some considerations
that you need to be aware of:

Here are some articles that provide some background:
http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp
http://support.microsoft.com/?scid=kb;en-us;Q176056
http://support.microsoft.com/default.aspx?scid=kb;en-us;191572
http://support.microsoft.com/default.aspx?scid=kb;en-us;324686

connection failures caused by pooling disablement
http://support.microsoft.com/default.aspx?scid=kb;en-us;328476

******implicit connections:
http://support.microsoft.com/?kbid=271128

> Will static connection string definitions declared as constants in the
> head of the ASP page itself work?

Doesn't matter

> Avoiding Server.CreateObject?

Using Server.CreateObject causes MTS to be used, which maintains its own
separate pool of connections. However, in IIS5 and above it's no longer
necessary to use it, and avoiding its use will improve performance. But
again, it's not really relevant to session pooling which is on by default in
ASP.

> Const PROVIDER = "Provider=vfpoledb;Data Source=C:\DBFILESLOC\"
> ' ( Or should this be stored in an application variable?? )

As long as you are talking about storing a string in Application, then
there's no reason not to. Using a global string will help you ensure that
pooling is used for all connections using that string.

Do not store ADO objects in Application or Session: they are not
free-threaded by default.
http://www.aspfaq.com/2053

> set objConn = Createobject("ADODB.Connection")
> objConn.ConnectionString = PROVIDER
> objConn.Open ' PROVIDER <-- is it preferable to use here??

Irrelevant. There is no difference to either way of doing it.

> Do some recordset stuff....

Basically, as long as you close connections as soon as you are finished with
them, releasing them to the session pool, you will be taking full advantage
of pooling. If you do things to prolong connections, such as recordset loops
using connected recordsets, instead of utilizing GetString, GetRows
(http://www.aspfaq.com/show.asp?id=2467) or disconnected recordsets, you
will not get the full benefit from pooling, even though it is still turned
on.

Bob Barrows

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 

• Previous message: Bob Barrows [MVP]: "Re: Set password on database"
• In reply to:(deleted message) Dr. Know: "Question about Connection Pooling in ASP"
• Next in thread: Dr. Know: "Re: Question about Connection Pooling in ASP"
• Reply:(deleted message) Dr. Know: "Re: Question about Connection Pooling in ASP"
• Messages sorted by: [ date ] [ thread ]