Destroy Connection - Dont Destroy Connection?
-
I've seen conflicting opinions on whether or not to destroy an ADO connection object when you're finished with it. The argument to not destroy the connection if it is likely to be reused through connection pooling sounds reasonable, but historically I seem to recall most people favoring destroying the connection object regardless.
What do you think? I'd like to see if there's some consensus on this topic. We've got apps that are written both ways and I'd like to set a common practice for our SQL developers.
-
When I read the earlier discussion where Antares686 commented on the article
http://authors.aspalliance.com/stevesmith/articles/sprocs.asp
by saying:
Only close the connection and do not set to NOTHING if the site is going to be used frequently. The reason is when you set the object to nothing it is destroyed completely and connection pooling will not occur. If not then if called again IIS will find the existing connection in the pool and opening it is faster because of this. After a period of time the connection will naturally expire from the pool anyway so it is not a concern to cleanup, unless you are not going to use again for long periods of time (next couple of hours or beyond).
I was surprised, because I had always been told to close the connection, then set it to nothing. Now I don't know if I'm doing the right thing, even after reading http://msdn.microsoft.com/library/default.asp?URL=/library/techart/pooling2.htm and http://www.sql-server-performance.com/sk_connection_pooling_myths.asp.
In the Pooling Myths article, the author states:
Closing the connection is critical to pooling, but destroying the object by setting it to Nothing is only important to resources on your web server. That makes performing this step important, but for a reason separate from pooling.
Doesn't that conflict with Antares686 comment:
Only close the connection and do not set to NOTHING if the site is going to be used frequently. The reason is when you set the object to nothing it is destroyed completely and connection pooling will not occur.
although the Microsoft article defends this point of view in the following code examples:
' This will take advantage of resource pooling.
Dim i As Integer
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim c as new ADODB.Connection
c.Open "DSN=LocalServer;UID=sa;PWD=;OLE DB Services=-1"
For i = 1 To 100
Set r = New ADODB.Recordset
r.Open "SELECT * FROM Authors", c
Set r = Nothing
Next I
c.close
Set c = Nothing
' This will not take advantage of resource pooling.
Dim i As Integer
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
For i = 1 To 100
Set c = New ADODB.Connection
Set r = New ADODB.Recordset
c.Open "DSN=LocalServer;UID=sa;PWD=;OLE DB Services=-1"
r.Open "SELECT * FROM Authors", _
"DSN=LocalServer;UID=sa;PWD=;"
r.close
c.close
Set c = Nothing
Set r = Nothing
Next I
Anyway, my calls to my stored procedures are always structured in the following manner:
Sub stp_GetAllExecute(substrActiveConnection, Err)
dim cmdSubCommand
dim rsSubRecordSet
dim cnSubConnection
set cnSubConnection = Server.CreateObject(ADODB.Connection")
cnSubConnection.Open substrActiveConnection
set rsSubRecordSet = Server.CreateObject("ADODB.Recordset")
set cmdSubCommand = Server.CreateObject("ADODB.Command")
With cmdSubCommand
.CommandType = adcmdStoredProc
.ActiveConnection = cnSubConnection
set rsSubRecordSet = .Execute()
End With
rsSubRecordSet.Close
cnSubConnection.Close
set cmdSubCommand = nothing
set rsSubRecordSet = nothing
set cnSubConnection = nothing
End Sub
There's lots of database access in my site, but not by a lot of users (it's a data entry application). Someone will log on, move through the application accessing the database for some editing and then updating, and then exit the application. In short, there will be a lot of database access by a limited group of users for short periods.
So my question is, should I be using the statement set cnSubConnection = nothing after every connection to the database and still hope to have pooling enabled? If not, how should I set up the ADO to make this application perform as efficiently as it can going to the database as often as it does?
Thanks for those terrific article references, Antares686.
-
"Only close the connection and do not set to NOTHING if the site is going to be used frequently. The reason is when you set the object to nothing it is destroyed completely and connection pooling will not occur."
This is what I was tought in the past. I read those myself and see they are different and now saying pooling still occurrs when set to nothing. I forgot my link and would have returned to the comment on the other. Do as MS says and set to nothing.
Remember it is always about learning and even us who may have been doing it for a while can find the rules change peridocially.
-
My understanding is that setting any object to Nothing marks that object as eligible for the garbage collector to pick up. This process happens on the Web server and has nothing to do with connection pooling. The garbage collection process runs on a system defined basis. Not marking an unused object for garbage collection may potentially lead to your application becoming a memory hog. Those links are great and I think it may take more than one reading and the looking at many examples to get a good understanding of this process.
Francis
-
You should set any object you user to nothing after you're done with them. If not you may create a memory hungry app and start having random bugs/issues in your application that are hard to track down.
Setting your connection objects to nothing still lets you take advantage of connection pooling. For example try this simple script and watch the user connections counter for Sql Server: General Statistics (on the machine sql is installed on) in your performance manager:
For i = 0 To 100
Dim oConn, oRS
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.ConnectionString <Your ConnectionInfo>
oConn.Open
oRS.Open "some simple select statement", oConn
'you can do something here or just close recordset
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
Next
On my workstation connecting first my local instance of MSDE I only opened, and held, 2 connections while the script ran. Connecting to a development SQL server on another machine, still only 2 connections. So yes, setting your connection object to nothing you will still take advantage of connection pooling.
Eric
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply