Open DB connection several times on a page

[DP]

I read some articles and post how to optimize te speed of asp pages
regarding opening and closing DB connections but I am still not sure
about this.

Is it true that it doesn't matter how many times I open and destroy a
DB connection on 1 single page?

----------------------------------------------------------
Set DataConn = Server.CreateObject("ADODB.Connection")
Call DataConn.Open (...)

DataConn.Close()
DataConn = nothing
----------------------------------------------------------

I can open and close a connection at the start and end of a page but I
prefer to put it inside each function which will result in having the
code above several times in my page.
Does this have any effect on performance or is my connection kept in
cache anyway?
I read somewhere that this cache is automaticly set to 30 seconds.
Does this mean a page wich loads 2 seconds only opens and closes a
connection 1 time for real?
how can I check how long a connection is being cached?

 

[Bob Barrows [MVP]]

I read some articles and post how to optimize te speed of asp pages
regarding opening and closing DB connections but I am still not sure
about this.

Is it true that it doesn't matter how many times I open and destroy a
DB connection on 1 single page?

With connection pooling, the impact will be minimized, but there will still
be some effect. My practice is to reuse a connection on a single page rather
than closing and reopening it.

----------------------------------------------------------
Set DataConn = Server.CreateObject("ADODB.Connection")
Call DataConn.Open (...)

DataConn.Close()
DataConn = nothing
----------------------------------------------------------

I can open and close a connection at the start and end of a page but I
prefer to put it inside each function which will result in having the
code above several times in my page.

??? Why not use a global variable on your page?

Does this have any effect on performance or is my connection kept in
cache anyway?

Your connection is kept in the pool, but there is some impact from closing
and reopening it.

I read somewhere that this cache is automaticly set to 30 seconds.

No, it's 60 seconds.

Does this mean a page wich loads 2 seconds only opens and closes a
connection 1 time for real?

Sort of. When you close the connection, it is released to the pool. This
takes some processor cycles. When you open a new connection, the pool needs
to be checked for available connections and any available connections need
to be retrieved. This will again take some unnecessary processor cycles.
Also, if another thread "steals" your connection, then a new connection
needs to be created and opened, which will have a much larger effect on your
page's performance.

how can I check how long a connection is being cached?

Don't worry about it. However:
http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp

Bob Barrows

 

[Jeff Cochran]

I read some articles and post how to optimize te speed of asp pages
regarding opening and closing DB connections but I am still not sure
about this.

Is it true that it doesn't matter how many times I open and destroy a
DB connection on 1 single page?

Matter how? You have the overhead in opening the connection each
time, which may or may not be an issue. You also have the issue of
code readability and portability, in that a deleted section may have
you erroring out.

----------------------------------------------------------
Set DataConn = Server.CreateObject("ADODB.Connection")
Call DataConn.Open (...)

DataConn.Close()
DataConn = nothing
----------------------------------------------------------

I can open and close a connection at the start and end of a page but I
prefer to put it inside each function which will result in having the
code above several times in my page.
Does this have any effect on performance or is my connection kept in
cache anyway?
I read somewhere that this cache is automaticly set to 30 seconds.
Does this mean a page wich loads 2 seconds only opens and closes a
connection 1 time for real?
how can I check how long a connection is being cached?

Create your page both ways and test it. Use what *you* find is best
for *your situation*.

Jeff

 

[Bob Barrows [MVP]]

Thanks. Exactly what I needed to know. Does the whole story also
counts for asp.NET or are there some differences?

I've just started learning asp.Net (you should actually be asking about
ado.net, not asp.net) so I can't say for sure, but I suspect the answer
would be the same. Opening and closing connections unnecessarily will impair
performance to some extent. I can't quantify that extent, but I do know tere
will be an effect, even if it's not perceptible to you or your users.

There is a newsgroup devoted to ado.net if you wish to get a definitive
answer to this:
microsoft.public.dotnet.framework.adonet.

All the .Net newsgroups contain "dotnet" in their names.

Bob Barrows

 

[Paxton]

I've just started learning asp.Net (you should actually be asking about
ado.net, not asp.net) so I can't say for sure, but I suspect the answer
would be the same. Opening and closing connections unnecessarily will impair
performance to some extent. I can't quantify that extent, but I do know tere
will be an effect, even if it's not perceptible to you or your users.

There is a newsgroup devoted to ado.net if you wish to get a definitive
answer to this:
microsoft.public.dotnet.framework.adonet.

All the .Net newsgroups contain "dotnet" in their names.

Bob Barrows

Bob,

Out of curiosity, which .Net compatible language are you starting with?
VB.Net or C#?

 

[Bob Barrows [MVP]]

Out of curiosity, which .Net compatible language are you starting
with? VB.Net or C#?

I haven't decided. I'm thinking the learning curve will be less for VB.Net,
but C# may be more useful in the long run.

Bob Barrows

 

[Bob Lehmann]

FWIW - I chose VB, for the reason you stated. C# however, seems to be the
language sought by employers, both contract & long-term.

This shouldn't make a difference for an independent, but there still seems
to be a bias against VB (toy language) even though the code compiles to the
same IL.

Go figure?

Bob Lehmann

 

[Paxton]

FWIW - I chose VB, for the reason you stated. C# however, seems to be the
language sought by employers, both contract & long-term.

This shouldn't make a difference for an independent, but there still seems
to be a bias against VB (toy language) even though the code compiles to the
same IL.

Go figure?

Bob Lehmann

Seems to me to be a fair amount of snobbery when it comes to C#. Maybe
it arises from the fact that the majority of C# programmers came up
through C/C++, and are thought of as *proper* programmers. Whatever -
I've already started ensuring case-consistency in my VBScript as good
practice while I get to grips with C# (even though it makes no
difference to vbs).

P