Close connection after each call?

[dw]

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.

 

[Aaron Bertrand - MVP]

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

 

[dw]

Thanks, Aaron. Also thanks for the useful links

 

[Ray at]

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]

Thanks, Ray. That's a very smart way of doing ADO.

 

[Ray at]

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

 

[dw]

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]

Hmm. Interesting.

Ray at work

 

[Ray at]

It sounds to me like you don't use Option Explicit. Tisk tisk! :]

Ray at work

 

[dlbjr]

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