Connection Pooling

[CLW]

I have always been under the impression that connection pooling (or more
appropriately called resource pooling) in ADO object is automatic when
connecting through OLE DB.

However, from MS Article
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html
/pooling2.asp, it suggests that one needs to keep one ADO connnection alive
for connection pooling to work under OLE DB.

On the other hand, in every other MS documentation, it is suggested that
connection should be closed explicitly as soon as possible for performance
reasons.

I connect to data base using the following method

dim conn as ADO.Connection
...

set conn=new ADO.Connection

with conn
.provider="SQLOLEDB"
.connectionstring="User ID=MyID; Password=MyPassword; Data
Source=MySource; Initial Catalog=MyDatabase"
.open
end with

Am I taking advantage of connection pooling? or am I better off connecting
using ODBC for OLEDB? How do I connect using ADO via ODBC?

Thanks in advance

[Cakkie]

This should be an answer to both your posts

As you said before, connection pooling is done automatically. Once you open 1 connection, you are pooling connections, cause when you close the connection via code (cnn.close), OLEDB will keep the connection open for some time (i believe it is somewhere about 30 seconds). During that time, if a new connection is needed (with the same security-context), this connection will be used. I've heard that you can limit the number of connections, but don't ask how. Also this would affect the etire computer, so the maximum size for a connection pool is the same for all pools in that machine. Since the server is not yours (but from your provider), I don't think this is an option.
I mentioned 'same security-context' there. What that means is the combination of provider, server, database, userid and some other things. Only if all are the same, connection pooling can be used. If used via Web, the connection is typically made with the same userid/password and stuff, so you should be connectionpooling.

If you try to open a connection when the maximum number of connections is used, you will get an error saying something like "open method of connection failed" (don't know the exact number). In some cases (depending on the backend database) you could get a message stating "connection refused" or "maximum connectioncount exceeded", but this should not be the case for SQL server. What to do? Well, what I usually do is retry for a number of times (like say 5). After that, show an errormessage. In a lot of cases, this will not show an error, but then you really should close your connection as fast as possible. This can be done very good using disconnected recordsets, which allow you to close the connection after the recordset is openend, still allowing you to loop through the recordset. You should read some articles about this (MSDN).

About the MSDN article that say to keep one connection open, well, I think you misunderstood that. What they (probably) meant is that you will need at least one connection in your pool, else there is no pool, and there is no connection pooling. This however, is done automatically, when you open a connection, OLEDB will lok for the pool. If no pool is found, it will create one. If the last connection in the pool is closed, the pool disappears, and we can't actually talk about connectionpooling anymore.

Finally OLEDB vb ODBC, go for OLEDB. OLEDB is the faster one, definitly for SQL server. ODBC is good when dealing with very large amounts of data, like say you want to retrieve 1000000 records, but if the number is limited (and 100000 still is), I would most definitly advice you to usez OLEDB.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[sanjo]

I have read in many places that Connection Pooling is enabled ONLY if you connect thru ODBC Drivers but not thru OLEDB.

I have an application that uses DSNLESS connections to the Database thru OLEDB. I am using the same connection string including UserId and password. So I just wanted to know in my DSN less connection to MSSQL server with OLEDB, connection pooling is automatically enabled or not. If not, what I need to do to enable it.

Please adivse.

Thanks,
San.

[Cakkie]

By default Connection Pooling is enabled using OLEDB, as a matter of fact, it would take a whole lot of work to disable it.

[venkat_murthy]

Hi cakkie,

Sorry that, its does not at all take a whole lot work to disable connection pooling while you use OLEDB. (at least when you use VB. I dunno VC ++ :( )

Actually, OLEDB uses ODBC api (more advanced version ofcourse) to make db connections. and 'Resource dispenser' takes care when you try to make connections thro COM (ie dll.)

Ok. I ll be clear. Simply, put, when you say conn.open, then you are explicitly initiating a connection pool. else, when you use objectcontext object, then connection pooling is automatically taken care off !!! ;)

[Cakkie]

Sorry, but that's not the way I have been told...
Connection pooling in VB always occurs automatically, whenever you open a connection with the same login credentials. This really doesn't matter if you are making use of object contexts or not.

Also, OLEDB DOES NOT USE ODBC calls. OLEDB has a complete different set of providers for each datasource. Note that ADO is not the same as OLEDB. You can call ODBC drivers through ADO, and you can call OLEDB providers through ADO, but you can't call ODBC drivers through OLEDB, they are just two different things.

[sabhhrt]

There is one way you can turn off the default connection pooling.

In the connection string you pass to create your connection, append this piece of code along with ur DSN, uid and password

;OLE DB Services = -2

// This turns off the OLE Db Session pooling and
//transaction enlistment services for the connection

May be this wud help you.

-Sam

[sabhhrt]

am sorry this works for c++ and i have found this in msdn but am not sure whether this helps in vb.

I am sorry if i am talking nonsense here..:)

-Sam

[coolbiz]

Interesting discussion. Well I went and read more on this from MSDN library:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

It has good information regarding pooling. And just to emphasize on Cakkie's comment, OLEDB <> ADO and also OLEDB does not use ODBC calls.

-Cool Bizs