Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Close connection after each call?

Reply
Thread Tools

Close connection after each call?

 
 
dw
Guest
Posts: n/a
 
      12-16-2003
Which is the most efficient way to hit the database: 1) to open/close the
connection for each call to the database on a page (we have Subs that do
this); 2) or use the same connection and close it at the end? My feeling is
that 2 is better, but just wanted to confirm with the experts. Thanks in
advance.


 
Reply With Quote
 
 
 
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      12-16-2003
I use 2). However, your environment may be different... you may have more
or less overhead when initiating a connection, you might be doing a *lot* of
processing between calls, etc.

When you're curious about "most efficient" - find out for yourself, rather
than asking opinions. Almost all opinions you get will certainly leave out
some variable you've forgotten to mention.

Some tips on timing code:

http://www.aspfaq.com/2092
http://www.aspfaq.com/2245

Stress testing:

http://www.aspfaq.com/2139

Measuring SQL Server performance:

http://www.aspfaq.com/2513

General suggestions for efficient ASP development:

http://www.aspfaq.com/2424

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/




"dw" <(E-Mail Removed)> wrote in message
news:OOQog7#(E-Mail Removed)...
> Which is the most efficient way to hit the database: 1) to open/close the
> connection for each call to the database on a page (we have Subs that do
> this); 2) or use the same connection and close it at the end? My feeling

is
> that 2 is better, but just wanted to confirm with the experts. Thanks in
> advance.
>
>



 
Reply With Quote
 
 
 
 
dw
Guest
Posts: n/a
 
      12-16-2003
Thanks, Aaron. Also thanks for the useful links

"Aaron Bertrand - MVP" <(E-Mail Removed)> wrote in message
news:%23JeB%23G$(E-Mail Removed)...
> I use 2). However, your environment may be different... you may have more
> or less overhead when initiating a connection, you might be doing a *lot*

of
> processing between calls, etc.
>
> When you're curious about "most efficient" - find out for yourself, rather
> than asking opinions. Almost all opinions you get will certainly leave

out
> some variable you've forgotten to mention.
>
> Some tips on timing code:
>
> http://www.aspfaq.com/2092
> http://www.aspfaq.com/2245
>
> Stress testing:
>
> http://www.aspfaq.com/2139
>
> Measuring SQL Server performance:
>
> http://www.aspfaq.com/2513
>
> General suggestions for efficient ASP development:
>
> http://www.aspfaq.com/2424
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
>
>
> "dw" <(E-Mail Removed)> wrote in message
> news:OOQog7#(E-Mail Removed)...
> > Which is the most efficient way to hit the database: 1) to open/close

the
> > connection for each call to the database on a page (we have Subs that do
> > this); 2) or use the same connection and close it at the end? My feeling

> is
> > that 2 is better, but just wanted to confirm with the experts. Thanks in
> > advance.
> >
> >

>
>



 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      12-16-2003
I'll typically use on connection.

Sub OpenData()
''create ado object with global scope
End Sub

Sub CloseData()
'''closes and destroys ado object
End Sub



OpenData
Set rs1 = adoObject.Execute(whatever)
''code, code, code
Set rs2 = adoObject.Execute(whateverElse)
CloseData

Ray at work


"dw" <(E-Mail Removed)> wrote in message
news:OOQog7%(E-Mail Removed)...
> Which is the most efficient way to hit the database: 1) to open/close the
> connection for each call to the database on a page (we have Subs that do
> this); 2) or use the same connection and close it at the end? My feeling

is
> that 2 is better, but just wanted to confirm with the experts. Thanks in
> advance.
>
>



 
Reply With Quote
 
dw
Guest
Posts: n/a
 
      12-16-2003
Thanks, Ray. That's a very smart way of doing ADO.

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:uf4VsL$(E-Mail Removed)...
> I'll typically use on connection.
>
> Sub OpenData()
> ''create ado object with global scope
> End Sub
>
> Sub CloseData()
> '''closes and destroys ado object
> End Sub
>
>
>
> OpenData
> Set rs1 = adoObject.Execute(whatever)
> ''code, code, code
> Set rs2 = adoObject.Execute(whateverElse)
> CloseData
>
> Ray at work
>
>
> "dw" <(E-Mail Removed)> wrote in message
> news:OOQog7%(E-Mail Removed)...
> > Which is the most efficient way to hit the database: 1) to open/close

the
> > connection for each call to the database on a page (we have Subs that do
> > this); 2) or use the same connection and close it at the end? My feeling

> is
> > that 2 is better, but just wanted to confirm with the experts. Thanks in
> > advance.
> >
> >

>
>



 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      12-16-2003
If you Dim the variable outside of the subroutine, it will have global
scope. Example:


Dim oADO

Sub OpenData()
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open ConnectionString
End Sub

Sub CloseData()
oADO.Close
Set oADO = Nothing
End Sub


Ray at work




"Peter Foti" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> news:uf4VsL$(E-Mail Removed)...
> > I'll typically use on connection.
> >
> > Sub OpenData()
> > ''create ado object with global scope
> > End Sub

>
> Ray, how do create it with global scope? I typically do something very
> similar, except I pass in the variable that I want to hold the connection
> object (because I couldn't figure out how to create the object with global
> scope without creating it outside of the call).
>
>
> > OpenData
> > Set rs1 = adoObject.Execute(whatever)
> > ''code, code, code
> > Set rs2 = adoObject.Execute(whateverElse)
> > CloseData

>
> Is adoObject a variable name that you created, or some intrinsic object in
> the ADO world?
>
> Thanks,
> Peter Foti
>
>



 
Reply With Quote
 
dw
Guest
Posts: n/a
 
      12-16-2003
You can also use the Execute function to create global variables within Subs
and Functions:

Sub test()
Execute("cn = Null") ' this is a bug in VBS that allows variables
created this way to be global in scope. "cn" wasn't dimmed elsewhere.
Set cn = Server.CreateObject(....)
End Sub

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:(E-Mail Removed)...
> If you Dim the variable outside of the subroutine, it will have global
> scope. Example:
>
>
> Dim oADO
>
> Sub OpenData()
> Set oADO = Server.CreateObject("ADODB.Connection")
> oADO.Open ConnectionString
> End Sub
>
> Sub CloseData()
> oADO.Close
> Set oADO = Nothing
> End Sub
>
>
> Ray at work
>
>
>
>
> "Peter Foti" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> > news:uf4VsL$(E-Mail Removed)...
> > > I'll typically use on connection.
> > >
> > > Sub OpenData()
> > > ''create ado object with global scope
> > > End Sub

> >
> > Ray, how do create it with global scope? I typically do something very
> > similar, except I pass in the variable that I want to hold the

connection
> > object (because I couldn't figure out how to create the object with

global
> > scope without creating it outside of the call).
> >
> >
> > > OpenData
> > > Set rs1 = adoObject.Execute(whatever)
> > > ''code, code, code
> > > Set rs2 = adoObject.Execute(whateverElse)
> > > CloseData

> >
> > Is adoObject a variable name that you created, or some intrinsic object

in
> > the ADO world?
> >
> > Thanks,
> > Peter Foti
> >
> >

>
>



 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      12-16-2003
Hmm. Interesting.

Ray at work

"dw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You can also use the Execute function to create global variables within

Subs
> and Functions:
>
> Sub test()
> Execute("cn = Null") ' this is a bug in VBS that allows variables
> created this way to be global in scope. "cn" wasn't dimmed elsewhere.
> Set cn = Server.CreateObject(....)
> End Sub



 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      12-16-2003
It sounds to me like you don't use Option Explicit. Tisk tisk! :]

Ray at work

"Peter Foti" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

>
> Ok, that's essentially what I do. The only difference being that I pass

in
> the variable as a parameter to the function. I find it to be a helpful
> reminder to Dim the variable outside. In your case, if you forget to Dim
> oADO, your function will create a local copy (which could yield errors

when
> you try to use oADO), whereas mine explicitly requires it to be passed in.
> Otherwise, they are the same.
>
> Peter
>
>



 
Reply With Quote
 
dlbjr
Guest
Posts: n/a
 
      12-16-2003
Class Process
Private mConn

Private Sub Class_Initialize()
Set mConn = CreateObject("ADODB.Connection")
mConn.Open "CONNECTION_STRING"
GetData
End Sub

Private Sub_Terminate()
Set mConn = Nothing
End Sub

Private Sub GetData()
'Some emplimentation left out
rs.Open strSQL,mConn,adLockReadOnly,adCmdTable
rs.Open strSQL,mConn,adLockReadOnly,adCmdTable
End Sub
End Sub

-dlbjr

Discerning resolutions for the alms


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to close a TCP socket? (TCPSocket#close doesn't close it) IƱaki Baz Castillo Ruby 7 01-12-2010 01:32 PM
Hang on socket close after connection reset or connection timed out Helene Unterwieser Java 14 07-02-2007 11:27 PM
Several Recordsets...Close the Connection Each Time? Bob ASP .Net 3 02-22-2007 12:25 PM
Several Recordsets...Close the Connection Each Time? Bob ASP .Net 0 02-22-2007 05:30 AM
Open/Close Database Connection for each page Michael ASP .Net 2 12-30-2003 07:34 PM



Advertisments