Implement Session Pooling For Multiple Users With Ado

Re: Implement Session Pooling For Multiple Users With Ado

Readfida, Hi
Did some more research today. Hear is what I found.

When a user Closes his connection, his connection path is not destroyed, but is sent to a connection pool on the server available to the next user. The ADO at the server controls Connection Pooling. A request for connection, by default, seeks a connection from the pool. If no connection matching his ConnectionString is available, a new connection is established. Connection Pooling allows reuse of an established connections by any user requesting a connection with an identical ConnectionString as the ConnectionString that established the original connection.

In your code always close the connection. This does not destroy it, but releases it to the Connection Pool.

Do not set the connection to Nothing. If you set the connection to Nothing it destroys your connection and the like connections in the Connection Pool.

You could force a new connection with every connection request by setting the property Pooling:= False, but this has no advantage as already established connections would not be used and it takes time to establish a new connection.

Reference Articles (Easy Reads)
http://www.codeproject.com/use…NET_ConnectionPooling.asp
http://www.ddj.com/windows/184407887
http://www.wintellect.com/Arti…DO%20NET%20Connection.pdf

Hope this helps you[hr]*[/hr] Auto Merged Post;[dl]*[/dl]PS Almost every reference on the web is for ADO.Net. But the same principals apply

Re: Implement Session Pooling For Multiple Users With Ado

each computer would have it have it's own Connection set up...

if you used commands you could set timeouts for them like this, which will help out.

Dim adoCon As ADODB.Connection 
Sub OpenConnection() 
    Dim sCon As String 
     'Open connection
    sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=\\ALLAN\Databases\TrackIDs.mdb" 
    Set adoCon = New ADODB.Connection 
    adoCon.CursorLocation = adUseClient
    adoCon.CommandTimeout = 0
    adoCon.Open sCon 
End Sub 


cnnSQL.CursorLocation = adUseClient

Re: Implement Session Pooling For Multiple Users With Ado

Readefy,

You should always “Close” the connection at the end of the process, but do not set it to “Nothing”. The connection will remain active and be placed in the pool. The next process (either the same workbook or another workbook, not sure here if the workbooks are on different computers if the pool takes this into consideration and treats them as separate platforms) should still use the same exact open procedure. The server will first look to the pool of active connections. If it finds one with the exact same string it will assign it to the request (without having to go through the full connection process). If it does not find one it will set up a new connection.

If you do not Close the connection it will close itself after a timeout. I believe the timeout is 15 seconds but not sure. This ties up the connection from being assigned to the pool.

If you set the connection to Nothing then it destroys the connection and the active connections in the pool.

The only way you can till if the pooling is working is by timing the connection process. The second attempt to connect should be faster than the first. You could use the same or different workbooks to test this. Of course the test has to be within the timeout of the pool.

There is a default timeout for the active connection to remain in the pool. These timeouts can be set via the open process. One of the web articles I posted explains this.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

adoCon.CommandTimeout = 0

Setting the time out to zero is doable (It means never timeout) but dangerous. If there is a malfunction of the connection attempted I believe your process will hang.

Re: Implement Session Pooling For Multiple Users With Ado

Yeah timeout to 0 means infinite Default is 30 seconds...

i've not come across a timeout hanging on me as of yet... 180 (3 minutes) is usually the standard these days...

Re: Implement Session Pooling For Multiple Users With Ado

Yes. Five seconds is way too long. AFAIK on the versions of ADO you are using pooling should be the automatic default therefore std practice would be - open the connection, run the query and close connection. This sounds like another problem to me.

Have you compacted and repaired the database recently ? What sort of line do you have to the db. Any hanging mapped drives ? Can you try it directly on the machine with the db ?

For pooling see
http://msdn2.microsoft.com/en-us/library/ms810829.aspx
and for pooling myths see

http://www.sql-server-performance.com/articles/per/connection_pooling_myths_p1.aspx

Re: Implement Session Pooling For Multiple Users With Ado

Hi Mate,

It is faster to keep the connection open but AFAIK it is dangerous and wasteful. I would think it is particularly dangerous with MS Access because it does not handle dropped connections/locking issues so well. I now run compact and repairs on Access databases as a daily routine and still have to restart servers occasionally due to database locking issues. As for wasteful I guess you do not care about multi user connection limits if you only expect a couple of users.

Normally I only keep the connection open if I need to run a series of queries, like filling combos on startup.

I just tried a test on a local and remote db - Excel getting data from Access. I attach the Excel and db.

I can open and close about 20 connections a second across the network (pulling a two line table into a recordset in the middle). With an SQL 05 db (same conditions) I get around 80 connections a second. On the other hand with an open connection I get 900 (Access) and 2000 (SQL) trips per second.

Your connection time of 5 seconds for Access seems v strange.

Can you try to repeat the test ? Save the db to C:\ and run the subs in the Excel test module.

Re: Implement Session Pooling For Multiple Users With Ado

Ok. So according to the test you can connect, fill a recordet and disconnect one hundred times in 10 to 15 seconds across the wireless network. That is about 10 times a second yet it takes 5 seconds to just connect and disconnect on the other database. Am I reading all that correctly ?

Hmmmm. Dunno. Can you start with
1. Compact and repair on the db maybe those open connections have caused problems already
2. If that doesn't work try importing all the objects into a new database.
3. Add my test table to your db and try the test again directed at your db ?

Re: Implement Session Pooling For Multiple Users With Ado

Understood. "TestConnectionOpenClose" connects and disconnects from the database one hundred times so if it took 10 to 15 seconds over your wireless it is not too bad. This suggests you have something wrong with your particular database not your network. Try :-above.

1. Open your db and do Tools > Database Utilities > Compact and Repair. Then test again your five second connection.

2. Open your db. file > Get External Data > Import then navigate to my test database and import the table (Table1). In the Excel change the connection in the ADO module to your database and repeat my tests.

3. Open an empty db file > Get External Data > Import navigate to your db and import everything then test against the new db.

Hope that makes sense.

Cheers

carl

Re: Implement Session Pooling For Multiple Users With Ado

Quote

Carl, your help is much appreciated, I hope im not passing on any headaches!!

No problem. We are all learning here.

As far as Excel is concerned as a rule you should not query an open workbook. There is a thread in the SQL forum which discusses the issue a few days ago. If you query a closed workbook then I can not imagine it would change it's size.

In general an MS Access mdb file just keeps increasing in size because unlike other databases there is no concept of a separate log file. Every time you do something it increases even if you delete data. You therefore need to keep running the compact and repair process to keep the size down. In my experience size does effect performance with Access but I don't think you would notice until you are over 100mb at least. I have one db that swells over 600mb during the day and compacts down to about 250mb at night. You can feel the performance difference (the real data is about the same - just lots of deletes and inserts) and if you let it go behond 1Gb it real starts to drag. A database upgrade is way overdue.