Question about Connection Pooling in ASP

Post removed (X-No-Archive: yes)

Re: Question about Connection Pooling in ASP

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;1915 72
http://support.microsoft.com/default.aspx?scid=kb;en-us;3246 86

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

******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"

Re: Question about Connection Pooling in ASP

Post removed (X-No-Archive: yes)

Re: Question about Connection Pooling in ASP

Dr. Know wrote:
> Bob,
>
> Thanks! And as always - great info.
> I search MSDN for answers before asking here, but many time I come up
> with 100's of hits, even with fairly specific queries. You must keep
> a database of "favorite best references" in your quick launch bar...
>
>> 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.
>
> I am assuming that an open such as:
>
> objRst.Open "SELECT TOP 1 [highnumber] FROM [dbffile] _
> ORDER BY [highnumber] DESC", _
> objConn, adOpenStatic, adLockreadOnly, adCmdText
>
> var = CLng(objRst("highnumber"))
>
> objRst.Close
> set objRst = Nothing
>
> would see no performance penalty, and may possibly be superior due
> to the lack of string handling, over using GetRows or GetString -
> since you are only retrieving one value and closing the recordset?

Even better would be to use the default forwardonly cursor instead of the
static cursor, but frankly, I doubt you would see the difference.


>
> Reading your other references leaves me with the impression that
> different access modes should be declared specifically as different
> connections - or does the connection pool manager handle this
> transparently in the background?

Different access modes will force new connections. But i do not think we are
talking about the same thing ...

>
> And although this doesn't apply to my current scenario, this implies
> that two recordsets which are open at the same time should also be
> declared as different PROVIDER connections - rather than trying to
> share one connection definition.
>
> In other words, should a connection to a recordset with a readonly,
> forward cursor to a specific database should be declared as
> PROVIDER_1, and a dynamic recordset with optimistic locking be
> declared as PROVIDER_2 - rather than a single non-specific declaration
> that relies on the recordset open options to determine the cursor
> mode. Or does it not matter.
>

No, it does not matter. There is no point to opening two connections. The
cursor type and location, and locktype are properties of the recordset, not
the connection. The properties that are set at the connection level simply
control the default settings of the same properties of the individual
recordsets.

>
> Const PROVIDER = "Provider=vfpoledb;Data Source=C:\DBFILESLOC\;
>
> VS:
>
> Const PROVIDER_1 = "Provider=vfpoledb; _
> Data Source=C:\DBFILESLOC\; _
> Mode=ReadOnly | Share DenyNone;"
>
> Const PROVIDER_2 = "Provider=vfpoledb; _
> Data Source=C:\DBFILESLOC\; _
> Mode=ReadWrite | Share DenyWrite;"


This (Mode) is the setting that requires separate connections, but i would
never do this. if i am opening several recordsets, I will use a single
connection.

>
>
> And the last question...
> Will I *ever* learn enough to be a truly masterful programmer? :-|
>

Maybe. I'm not a masterful one yet ... (believe me, i am not being modest)

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"