473,241 Members | 1,611 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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?
Jul 22 '05 #1
8 2027
DP wrote:
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...l/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.
Jul 22 '05 #2
On 24 Nov 2004 07:12:33 -0800, dp**********@hotmail.com (DP) wrote:
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
Jul 22 '05 #3
DP
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:<OK**************@TK2MSFTNGP09.phx.gbl>...
DP wrote:
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...l/pooling2.asp

Bob Barrows


Thanks. Exactly what I needed to know. Does the whole story also
counts for asp.NET or are there some differences?
Jul 22 '05 #4
DP wrote:

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

--
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"
Jul 22 '05 #5
Bob Barrows [MVP] wrote:
DP wrote:
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


Bob,

Out of curiosity, which .Net compatible language are you starting with?
VB.Net or C#?
Jul 22 '05 #6
Paxton wrote:

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

--
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"
Jul 22 '05 #7
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eu****************@TK2MSFTNGP09.phx.gbl...
Paxton wrote:

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

--
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"

Jul 22 '05 #8
Bob Lehmann wrote:
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eu****************@TK2MSFTNGP09.phx.gbl...
Paxton wrote:
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

--
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"


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
Jul 22 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Glenn Davy | last post by:
Q. How can I 'see' the number of table id's access is using for tracking recordsets? Details: I have some ado code that itterates through a recordset and calls a procedure on each record. This...
6
by: karim | last post by:
I have an asp.net page that stopped running properly giving the error below. The app uses a SQL Server 2000 on another server. Enterprise Manager and Query analyzer on the web server can connect to...
2
by: Michael | last post by:
Hello, In my past ASP pages, at the top I used an include file to open an ado connection for the entire page, then at the bottom, I would have another include file to close the connection. ...
4
by: Susan Baker | last post by:
PHP Newbie here - I apologize if any of my questions appear daft or obvious ... Does PHP (or maybe the web server - Apache in my case), support (database) connection pooling?. It seems terribly...
7
by: Brian Kitt | last post by:
I frequently get the above error on my website. It happens only for short periods of times, then the error goes away. I cannot recreate this. I have an error trap that picks up this error. When...
7
by: Brad | last post by:
I have several web apps which download content to client (pdf, tif, etc) by writing out content in an aspx page. I've recently upgraded these from 1.1 to 2.0....and they all seem to work fine...
8
by: Imran Aziz | last post by:
Hello All, Like in C++ I tried to use constructor to open a database connection and distructor to close the database connection, it now turns out that one cannot create distrutors in C# classes. ...
6
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I am thinking about doing this since I got several cases that some of our internal users open more than one browser at the same time from our server. When one of the transactions was not...
64
by: Mika | last post by:
Hello, we understand you guys may be able to help. We have a page which has been working great for over a year and gets many hits. However recently something got changed that we cannot seem to...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.