Connection pooling question

[fniles]

I am using VS2003 and connecting to MS Access database.
When using a connection pooling (every time I open the OLEDBCONNECTION I use
the exact matching connection string),
1. how can I know how many connection has been used ?
2. If the maximum pool size has been reached, what happens when I call the
method Open to open the connection ? Will I get an error ? MSDN says the
request is queued, but will I get an error in the open method ?

ConnectionDemoOLE = New OleDb.OleDbConnection
sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";OLE DB
Services=-1"
With ConnectionDemoOLE
.ConnectionString = sPath
.Open() --> what happens here when the maximum pool size has been
reached ?

Thanks

 

[William \(Bill\) Vaughn]

I don't think there are any system counters exposed by the JET provider or
by OLE DB to monitor the CP.
If you exhaust the pool you should get a timeout exception.
However, (and Ginny please correct me here), if you're working with JET and
an ASP application, you've got your wires crossed. JET is not designed to
provide data for more than one user. Sure, you can share a JET .MDB database
over a LAN, but each user gets its own JET engine to access the file. Using
it in a web application that requires one JET engine to access the data is
problematic at best. I suggest using a DBMS designed for the web--SQL
Express.

hth

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

 

[fniles]

I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
Access.
In SQL Server, in the connection string you can set the max pool size, can
you do that in MS Access ?

You mentioned that "If you exhaust the pool you should get a timeout
exception." So, you will get an error, right ?
I mean if you put try-catch-end try, it will go to the Cath section, right ?
In my other posting I posted that sometimes I get "Unspecified error" on the
Open method when opening up the OleDBConnection. This does not happen all
the time, only sometimes, which makes me think that maybe the maximum pool
size has been reached ? Is it possible that the "Unspecified error" on the
Open method caused by the maximum pool size has been reached ?

Dim swError As StreamWriter
Dim sSub As String
Try
sSub = "1"
ConnectionOLE = New OleDb.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = g_dbPath
sSub = "2"
.Open() --> IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
TO THE Catch section below ?
sSub = "3"
End With
Catch ex As Exception
swError = New StreamWriter(Application.StartupPath &
"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
swError.Write(Now & " OpenDBOLE - error = " & ex.Message & "
sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try

Thank you.

 

[William \(Bill\) Vaughn]

See >>>>>

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------

 

[Cor Ligthert [MVP]]

Bill,

Although I agree with your conclusion.

Using Access over the Web means to have an ASP or an ASPX application which
runs on a Server. (Mostly the same as the webapplication runs on). In fact
there is one user (ASPUSER), which uses the ASP or ASPX application.

What is not possible is using Access over the web by using by instance its
IP address as it is by real databaseservers is possible (I thought that this
possibility is removed in SQLExpress) .

I assume that you understand that there is not any connection pooling
problem at all. Probably you know this, however to be complete for others.
Concurrency problems stay because the webprogram is sending data to its
clients, which works in a 3 tier way using the browsers as non intelligent
workstations as we called them in past (VT100 etc).

Cor

 

[fniles]

Thank you.
I do use try/catch, but I get the "Unspecified error".

When the maximum pool size has been reached and I get an error, how can I
loop and wait until a connection is available again ?

Try
ConnectionOLE = New OleDb.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = g_dbPath
.Open() --> IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
to the Catch. How can I loop and wait to open the db until a connection is
available again ?
End With
Catch ex As Exception
Try

Thank you.

 

[William \(Bill\) Vaughn]

Ah, I doubt if this will help. It assumes that the engine has enough idle
time to do it's work. The fundamental issue is clear. If the cause of your
problem is the CP and the pool is filling then something is overloading the
engine or your code is not releasing/closing connections in a timely
fashion. Again, JET is not designed for this kind of work. I think you're
beating a dead horse.

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------

 

[fniles]

Thank you.

If the cause of your problem is the CP

What did you mean by CP ?

JET is not designed for this kind of work.

So, if I use SQL Server (and assuming I use the code like below, except
using SqlConnection instead of OLEDbConnection), most likely I will not have
the problem where the pool is filling like in Access ? Is the maximum pool
size in Access smaller than in SQL Server (where the default is 100) ?

I close the connection right after I fill the dataset like shown below. Can
I close the connection faster then the way I do it ?
This code is called everytime somebody login to the application.

Dim cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub

Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try

 

[William \(Bill\) Vaughn]

CP::Connection Pool
The differences between JET and SQL Server are ... well, dramatic. They are
designed very differently. JET is a throw-back to shared-file ISAM (dBASE)
database engines (circa 1970's) while SQL Server is a service-based engine
designed to handle many, many users and far more secure and scalable
database requirements. I characterize JET as a "home" database and I don't
recommend it for any (serious) business applications--despite the fact that
it's in very wide use all over the world in lots of businesses. It makes a
sad web DBMS engine. While it can work, you're likely to see more and more
serious (unsolvable) problems with JET when used incorrectly.

Will SQL Server tolerate code that does not properly close connections?
Nope, its connection pool will overflow if you don't write the application
correctly. Will it expose more counters and trace metrics to let you inspect
the CP status? Sure and then some. Is it designed for use in a web site?
Absolutely.

I looked at your code (again) and I see that you're manually opening the
connection. You don't have to. In your case I suggest that you don't. The
Fill method opens the connection (if it's not already open), runs the query,
populates the DataSet and closes the connection (if it was opened by Fill).

I still suspect you might be over-running the ability of JET to handle the
workload. Remember JET can't stop work on a query and service another
request. All requests are handled serially (unlike SQL Server).

I discuss all of this and more in my latest book.

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------

 

[Paul Clement]

¤ I am using VS2003 and connecting to MS Access database.
¤ When using a connection pooling (every time I open the OLEDBCONNECTION I use
¤ the exact matching connection string),
¤ 1. how can I know how many connection has been used ?
¤ 2. If the maximum pool size has been reached, what happens when I call the
¤ method Open to open the connection ? Will I get an error ? MSDN says the
¤ request is queued, but will I get an error in the open method ?
¤
¤ ConnectionDemoOLE = New OleDb.OleDbConnection
¤ sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";OLE DB
¤ Services=-1"
¤ With ConnectionDemoOLE
¤ .ConnectionString = sPath
¤ .Open() --> what happens here when the maximum pool size has been
¤ reached ?

While connection pooling is supported in Jet, it's really of little value and there isn't really any
point in attempting to monitor it. But to answer your prior question, it's enabled by default and
the connection pools are maintained on each client that opens the database because that is where the
database engine in running.

If instead you want to monitor the users in your database there are tools to do this:

How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2000
http://support.microsoft.com/default.aspx?scid=kb;EN-US;198755


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[fniles]

Thank you one more time. You are very helpful.
You suggested to not open the connection manually. How do you do that ?
Don't I need to assign a connection to the OleDbCommand ?
You mentioned the Fill method opens the connection, how does it know what
connection ?

Will my code than look something like below :

Dim cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
'----DO NOT NEED THIS CODE ----- bDBSuccess =
OpenDBDemoOLE(ConnectionDemoOLE) ------'
'----If bDBSuccess Then
.Connection = ConnectionDemoOLE '????
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try

Thank you.

 

[fniles]

I have a question.
If for example application A and B uses the same database (either Access or
SQL Server).
Will the maximum connection pooling apply per application ? In other words,
if maximum connection pooling is 100, application A uses 10 connetions, will
application B has 90 or 100 connections left ?

Thank you.

 

[Paul Clement]

¤ I have a question.
¤ If for example application A and B uses the same database (either Access or
¤ SQL Server).
¤ Will the maximum connection pooling apply per application ? In other words,
¤ if maximum connection pooling is 100, application A uses 10 connetions, will
¤ application B has 90 or 100 connections left ?

Connection pools are create per process (or app pool) and per unique connection string. So unless
application A and application B are in the same app pool (such as a web appl) they will each have
their own connection pool.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[fniles]

I notice when the application uses an Access db on my machine (where no
other application access that database) my application does not reach the
maximum pool connection as fast as when I use an Access db on our server
(where many other applications access that database).
Is this because even though my application say has maximum of 100
connections, because other applications access the same database, there may
not be connection available ?

Thanks.

 

[fniles]

Also, 1 more question.
The way I do connection pooling is the following:
In the main form load I open a connection using a connection string that I
stored in a global variable g_sConnectionString and leave this connection
open and not close it until it exits the application.
Then on each thread I create a local OleDBConnection variable, open the
connection using the exact same connection string as the main form (stored
in global variable g_sConnectionString), and close it after populating a
DataSet.
Is this correct ?

Thank you.

 

[Cor Ligthert [MVP]]

Fniles,

Why not buy the book from Bill, it does not help you much in a high
theoretical question without answer.

However for those practical questions that you have now it is very good.

(I had it on my desk Bill, however my collegues took it away)

Cor

 

[William \(Bill\) Vaughn]

Instantiating a Connection object and opening the connection to the data
source are two different steps.
You can create a global Connection object that's visible to the entire
application. In some situations this makes sense--especially in Windows
Forms applications.
Opening the connection changes the State, links the application to the data
source (like opening a file), and permits the application to send queries to
the data source (like a DBMS engine).

Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.


--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------

 

[fniles]

Thank you.
I am sorry I am still not clear on how to call the Fill method without
opening the connection manually before hand.
Do you have to open the connection sometime before hand ?

Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.

If for example application A and B uses the same database (either Access or
SQL Server). If maximum connection pooling is 100, each application will get
100 maximum connection pooling, is this correct ?

I notice when the application uses an Access db on my machine (where no
other application access that database) my application does not reach the
maximum pool connection as fast as when I use an Access db on our server
(where many other applications access that database).
Is this because even though my application say has maximum of 100
connections, because other applications access the same database, there may
not be connection available ?

 

[William \(Bill\) Vaughn]

No. Fill opens the connection automatically and closes it afterwards.
However, if for any reason the connection has already been opened, Fill does
not change the connection state.

See >>>

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------

Thank you.
I am sorry I am still not clear on how to call the Fill method without
opening the connection manually before hand.
Do you have to open the connection sometime before hand ?

If for example application A and B uses the same database (either Access
or
SQL Server). If maximum connection pooling is 100, each application will
get 100 maximum connection pooling, is this correct ?

Connection pools are maintained on a process/application basis. Pools are
not shared between processes. Again, this assumes that the JET OLE DB
provider implements pooling. While I suspect it does, the issue has never
come up because JET when used in a Windows Forms application does not need a
pool unless you have implemented the application incorrectly.

I notice when the application uses an Access db on my machine (where no
other application access that database) my application does not reach the
maximum pool connection as fast as when I use an Access db on our server
(where many other applications access that database).
Is this because even though my application say has maximum of 100
connections, because other applications access the same database, there
may
not be connection available ?

In a JET architecture where Windows Forms applications are opening the
database, each client/user gets its own copy of JET to manage the shared
file. Each client has its own pool and are not sharing any other
applications' pool. Just because your application opens more than one
connection, it does not impact the other applications' pool. It DOES impact
the performance of the application as each JET engine must perform physical
IO over the LAN to share the database file. This means if your (or any)
application opens a lot of connections to the datatabase, this puts an
enormous load on the system and the ability to share the data.

Remember that JET was designed to support a few users with light load. It
breaks down quickly when stressed (as you have discovered).

Again, this is all covered in my earlier books. Considering that MS
encourages developers to get off of JET for more suitable DBMS engines, I
suggest you follow their suggestions (and mine).

 

[fniles]

No. Fill opens the connection automatically and closes it afterwards.
I am sorry, but how does Fill know which connection to use ?
Do you have a sample code on how to just do fill without assigning a
connection to the OLEDbCommand object ? Thanks so much for your help.
These are my original codes:
Dim cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
: