opening en closing Connection takes too much time!!!

  • Thread starter Thread starter DraguVaso
  • Start date Start date
D

DraguVaso

Hi,

When I was looking for the reason my application was runnong so slow I
suddenly noticed that it was the opening and closing of the connection that
took almost all the time. Like in most of the samples I found I opened the
connection everytime I needed it, and closed it after my action.

But closing and opening everytime seemed to take very much time, so I opened
my connection at the start of the application, and closed it at the end: it
runned up to 20 times faster (before 1 action took 3-4 seconds, now I have 5
actions per second).

So now my question: what's wrong with opening it one time and closing it at
the end? Looking at the smaples I found this seems 'not done'? Does it
really take so much resources to leave the connection the whole time open?

Isn't the use of a property like this a nicer way to handle the
database-connection?
Property MyConn() As OleDbConnection
Get
If Not MyConn2.State = ConnectionState.Open Then
MyConn2.Open()
End If
MyConn = MyConn2
End Get
Set(ByVal Value As OleDbConnection)
'nothing?
End Set
End Property

or maybe just before every use a:
If Not MyConn.State = ConnectionState.Open Then
MyConn.Open()
End If


Any help, any idea's, any remarks or reflections would be nice. I just
thinks the much better performance justifies to leave the connection open.
Is there any offical documentation about this?

Thanks in advance!

Pieter
 
Hi,

It seems that your connection is not being cached (normally you should open
the connection just before db operation and close it immediately).
Try also, each time you need a connection creating a new oledbconnection
object with the same connection string.
 
How do I cache a connection?
And why god do we have to open and close the connection the whole time?
hehe. I just don't see the benefit of it. Especially when the performance is
20 times lower!
 
Hi,

DraguVaso said:
How do I cache a connection?
Click to expand...

It should be automatically done by ado.net.
And why god do we have to open and close the connection the whole time?
hehe. I just don't see the benefit of it. Especially when the performance is
20 times lower!
Click to expand...

As I said, this not normal behaviour.
You should not keep connections open because each open connection uses
resources on server.
This is not so obvious with one connection, but imagine having 10 threads or
10 simultaneous actions:
In your way, you'll keep 10 connections open all the time.
While if you use pooling they will be opened just for the time being (of the
operation) and they'll recycle and so on (depends on settings).
 
You write "depends on settings": where can I find these settings? Can I
somewhere find the behaviour of caching connections or I don't know what to
get it faster? Because like this it takes me almost 4 minutes to do a normal
job done, and with the 1-connection-never-closed it takes only 10 seconds...

I really have to find a solution!

Thanks in advance! :-)
 
¤ Hi,
¤
¤ When I was looking for the reason my application was runnong so slow I
¤ suddenly noticed that it was the opening and closing of the connection that
¤ took almost all the time. Like in most of the samples I found I opened the
¤ connection everytime I needed it, and closed it after my action.
¤
¤ But closing and opening everytime seemed to take very much time, so I opened
¤ my connection at the start of the application, and closed it at the end: it
¤ runned up to 20 times faster (before 1 action took 3-4 seconds, now I have 5
¤ actions per second).
¤
¤ So now my question: what's wrong with opening it one time and closing it at
¤ the end? Looking at the smaples I found this seems 'not done'? Does it
¤ really take so much resources to leave the connection the whole time open?
¤
¤ Isn't the use of a property like this a nicer way to handle the
¤ database-connection?
¤ Property MyConn() As OleDbConnection
¤ Get
¤ If Not MyConn2.State = ConnectionState.Open Then
¤ MyConn2.Open()
¤ End If
¤ MyConn = MyConn2
¤ End Get
¤ Set(ByVal Value As OleDbConnection)
¤ 'nothing?
¤ End Set
¤ End Property
¤
¤ or maybe just before every use a:
¤ If Not MyConn.State = ConnectionState.Open Then
¤ MyConn.Open()
¤ End If
¤
¤
¤ Any help, any idea's, any remarks or reflections would be nice. I just
¤ thinks the much better performance justifies to leave the connection open.
¤ Is there any offical documentation about this?
¤

What kind of database are you working with? Unless you only required a connection on occasion you
should be able to take advantage of connection pooling (in a database server environment).

The disadvantage to using persistent connections is that, in a multi-user environment, considerably
more connection resources are consumed.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
It's just a simple Access Database located on a Map Netwerk Drive. I make
the connection with OleDBConnection and 4.0 Jet Provider for the
connectionstring.
It works with a loop which have to do some actions with a record taken from
a DataView, do some actions/evaluiations with the record and then insert it
into a tabel.

The 'funny' thing is that the database-actions (insert, update, select)
doesn't take mucht time, but really all those "If MyConn.State =
ConnectionState.Open Then MyConn2.Close()"...

It takes almost one second to do that single line...

 
DraguVaso said:
It's just a simple Access Database located on a Map Netwerk Drive. I make
the connection with OleDBConnection and 4.0 Jet Provider for the
connectionstring.
Click to expand...

In this situation, your OleDbConnection wrappers a file handle to a
networked file.

This makes this situation very special.

Everything else you read about connections and connection pools and opening
and closing connections assumes that you are using a database server, or at
least a local file. Ignore it.

Opening these connections can be extremely expensive, and writing to your
MDB over multiple connections can cause corruption. So treat your
connections like gold.

Open one connection, keep it open, don't even let the connection pool manage
it. If you have multiple threads, serialize access to your one connection.



David
 
If you must keep the connection open for performance reasons, try
implementing some kind of connection manager that supplies open connections
to your application, and then keeps the connection open for 10 seconds or so
after your app disposes it.

This way you have bave the best of both worlds - you never keep a connection
open for a long time, and you have the performance of being able to use the
same connection for multiple operations. The only delay may be when you
haven't used the connection until it has timed out.

Hope this helps,

Trev.
 
Codemonkey said:
If you must keep the connection open for performance reasons, try
implementing some kind of connection manager that supplies open connections
to your application, and then keeps the connection open for 10 seconds or so
after your app disposes it.


This way you have bave the best of both worlds - you never keep a connection
open for a long time, and you have the performance of being able to use the
same connection for multiple operations. The only delay may be when you
haven't used the connection until it has timed out.
Click to expand...

That would be fine, but I would recommend a very long timeout. More like 10
minutes than 10 seconds.

David
 
That would be fine, but I would recommend a very long timeout. More like
10
minutes than 10 seconds.
Click to expand...

Not necessarily as the timeout would be reset every time the application
asks for a new connection. This will prevent the connection being closed
during a large batch job or something. If the user leaves the applcation
alone for a while, there's no sense in keeping the connection open. Users
are usually prepared to wait a few extra seconds when saving something
anyway.

Trev.
 
Thanks a lot Trev and David! These are some nice suggestions that helped me
a lot!

Pieter
 
Pieter,

I suspect you have encountered the reason "Microsoft recommends the
SQL Server database or MSDE for use with the .NET Framework"

I am just guessing here (based upon some casual experience with Access
tables and ADO.NET) but I don't think connection pooling applies in
this instance.

Also, the main reason not to hold the database open is to reduce the
potential for database corruption.

You might look at MSDE if the project justifies the effort.
 
Hi,

I have to admit that I didn't yet investigate the possiblity's of MSDE, but
for small applications that doesn't ask to much of the database I prefer to
use Access, because it offers easy possiblity's to the users to make
themselves some reports etc. And it's very transparent and always accesible.

Although I wouldn't know what MSDE has to offer, hehe.
 
Although I wouldn't know what MSDE has to offer, hehe.


A lot better stability over a network ;)
 
Access can easily connect to MSDE/SQL Server and allow users to create
reports just as if they were creating them for a MDB file.

One advantage is that the user's workstation does not have to have carnal
knowledge of the actual database files.

All manipulation of the data is handled by the MSSQLServer service running
on the database server. Only the requested data and updates need to traverse
the network.
 
Hm, I'm alreaddy convinced for 50%, hehe.
Some other important reason why I choose for Access in this specific
application: The users have to be able to put their selves some Excel-sheets
into some database-tables. I now do this by the Import-Wizards. Is something
like that available in MSDE? Or maybe better doing this also via an
Access-database which is linked to the MSDE?
 
Back
Top