Re: Question Regarding ADO Connection Pooling


"Bill N" wrote in message
> I have a web app that runs on windows web server with IIS 5.0. I'm using
> an
> Oracle db and the msora provider. Our dba says that we're not using
> connection pooling.

You should ask your DBA if he can prove that. (It's easy to confirm this:
write a small loop that opens and closes the connection about 50 times. If
the number of concurrent Oracle connection is greater than 1 or 2 then you
have a problem.)

ADO does little for pooling connection resources on its own. It is in fact
the OLEDB provider that does it. In your case, it is the MSORA provider.
For it to be a legitimate provider, MSORA *has* to implement the pooling
interfaces that the OLEDB provider framework imposes. So, yes, if you use
ADO and OLEDB, pooling should happen automatically.

> I've also came accross an article
> (http://www.sqlservercentral.com/columnists/lplatt/adogotcha_printversion.as
> p) that says IIS does connection pooling. Can anyone shed some light
> here?

The guy is right in a way, but it sounds like IIS needs to be configured to
do so. On the contrary. OLEDB Resource pooling (connection pooling)
happens automatically in the client process space. So, if IIS is the main
process, you will see the benefits of resource pooling within *that* IIS
process. If you have your own Delphi executable that uses ADO, then you
will automatically get connection pooling within your exe process.

> Does ADO (version 2.6) do connection pooling automatically?

Technically no, but since you are using an OLEDB provider, the answer is
yes.

> If so, is there
> some settings for maximum number of connections? Queue Length? etc?

Yes there are. You can play around with these settings in the connection
string. (Create a UDL file and investigate.) There are provider specific
settings too. If you set the following at the end of your connection string
"OLEDB SERVICES=-4", you actually switch off connection pooling. So make
sure that entry isn't there!

It is interesting to note that the Java community behaves differently where
they *expect* the process spaces such as (web) application servers to
configure pooling. In fact, JDBC does no pooling at all and neither do the
specific providers. Pooling happens in the application server spaces.
(Maybe your DBA is confusing this issues??? Just guessing.)

HTH




.

Relevant Pages

  • Re: question about proper production db/ado usage
    ... The SQL Server provider supports pooling directly. ... create a new connection (pools are based on the connection strings being the ... > In our J2EE projects we create/use a connection pool in WebLogic, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Losing connection to DB for 10 minutes!
    ... No i haven't turned off pooling, ... Referring to "losing connection" i should have explained a bit more. ... server replicated to a passive db server. ... IIS health settings and max / min pools in the connection strings. ...
    (microsoft.public.data.ado)
  • Re: Connection pooling parameters not working for Oracle connection
    ... While I don't profess to be an Oracle expert, I do know that each provider ... I tried to add connection pooling parameters like "Connection Timeout", ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Losing connection to DB for 10 minutes!
    ... server replicated to a passive db server. ... connection from my applications. ... IIS health settings and max / min pools in the connection strings. ... You're not doing anything to disable pooling are you? ...
    (microsoft.public.data.ado)
  • Re: Losing connection to DB for 10 minutes!
    ... No i haven't turned off pooling, not intentionally anyway;-) I can confirm ... Referring to "losing connection" i should have explained a bit more. ... server replicated to a passive db server. ... IIS health settings and max / min pools in the connection strings. ...
    (microsoft.public.data.ado)