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.CreateOb ject("ADODB.Con nection")
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]]

Re: Open DB connection several times on a page

DP wrote:[color=blue]
> 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?[/color]

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.
[color=blue]
>
> ----------------------------------------------------------
> Set DataConn = Server.CreateOb ject("ADODB.Con nection")
> 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.[/color]

??? Why not use a global variable on your page?
[color=blue]
> Does this have any effect on performance or is my connection kept in
> cache anyway?[/color]

Your connection is kept in the pool, but there is some impact from closing
and reopening it.
[color=blue]
> I read somewhere that this cache is automaticly set to 30 seconds.[/color]

No, it's 60 seconds.
[color=blue]
> Does this mean a page wich loads 2 seconds only opens and closes a
> connection 1 time for real?[/color]

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.
[color=blue]
> how can I check how long a connection is being cached?[/color]
Don't worry about it. However:

http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Jeff Cochran]

Re: Open DB connection several times on a page

On 24 Nov 2004 07:12:33 -0800, dpnoordenbos@ho tmail.com (DP) wrote:
[color=blue]
>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?[/color]

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.
[color=blue]
>----------------------------------------------------------
> Set DataConn = Server.CreateOb ject("ADODB.Con nection")
> 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?[/color]

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

Jeff

[DP]

Re: Open DB connection several times on a page

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message news:<OKhluNk0E HA.1524@TK2MSFT NGP09.phx.gbl>. ..[color=blue]
> DP wrote:[color=green]
> > 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?[/color]
>
> 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.
>[color=green]
> >
> > ----------------------------------------------------------
> > Set DataConn = Server.CreateOb ject("ADODB.Con nection")
> > 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.[/color]
>
> ??? Why not use a global variable on your page?
>[color=green]
> > Does this have any effect on performance or is my connection kept in
> > cache anyway?[/color]
>
> Your connection is kept in the pool, but there is some impact from closing
> and reopening it.
>[color=green]
> > I read somewhere that this cache is automaticly set to 30 seconds.[/color]
>
> No, it's 60 seconds.
>[color=green]
> > Does this mean a page wich loads 2 seconds only opens and closes a
> > connection 1 time for real?[/color]
>
> 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.
>[color=green]
> > how can I check how long a connection is being cached?[/color]
> Don't worry about it. However:
> http://msdn.microsoft.com/library/en...l/pooling2.asp
>
> Bob Barrows[/color]



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

[Bob Barrows [MVP]]

Re: Open DB connection several times on a page

DP wrote:[color=blue]
>
> Thanks. Exactly what I needed to know. Does the whole story also
> counts for asp.NET or are there some differences?[/color]

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.publi c.dotnet.framew ork.adonet.

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

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Paxton]

Re: Open DB connection several times on a page

Bob Barrows [MVP] wrote:[color=blue]
> DP wrote:
>[color=green]
>>Thanks. Exactly what I needed to know. Does the whole story also
>>counts for asp.NET or are there some differences?[/color]
>
>
> 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.publi c.dotnet.framew ork.adonet.
>
> All the .Net newsgroups contain "dotnet" in their names.
>
> Bob Barrows
>[/color]

Bob,

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

[Bob Barrows [MVP]]

Re: Open DB connection several times on a page

Paxton wrote:[color=blue]
>
> Out of curiosity, which .Net compatible language are you starting
> with? VB.Net or C#?[/color]

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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Lehmann]

Re: Open DB connection several times on a page

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

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:eu%23czpz0 EHA.1564@TK2MSF TNGP09.phx.gbl. ..[color=blue]
> Paxton wrote:[color=green]
> >
> > Out of curiosity, which .Net compatible language are you starting
> > with? VB.Net or C#?[/color]
>
> I haven't decided. I'm thinking the learning curve will be less for[/color]
VB.Net,[color=blue]
> but C# may be more useful in the long run.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>[/color]

[Paxton]

Re: Open DB connection several times on a page

Bob Lehmann wrote:[color=blue]
> 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
>
> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:eu%23czpz0 EHA.1564@TK2MSF TNGP09.phx.gbl. ..
>[color=green]
>>Paxton wrote:
>>[color=darkred]
>>>Out of curiosity, which .Net compatible language are you starting
>>> with? VB.Net or C#?[/color]
>>
>>I haven't decided. I'm thinking the learning curve will be less for[/color]
>
> VB.Net,
>[color=green]
>>but C# may be more useful in the long run.
>>
>>Bob Barrows
>>
>>--
>>Microsoft MVP - ASP/ASP.NET
>>Please reply to the newsgroup. This email account is my spam trap so I
>>don't check it very often. If you must reply off-line, then remove the
>>"NO SPAM"[/color][/color]

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