asp classic - Closing SQL connections in regular ASP

Question

任何人都知道在常规ASP中关闭连接的最佳做法是什么，我应该在每次sql查询后立即关闭，还是只在页面底部关闭？

例如，这可以吗：

sql = "SELECT COUNT(*) AS num FROM tblUSER"
set rstemp = connTemp.execute(sql)
theCount = rstemp("num")
sql = "SELECT COUNT(*) AS num2 FROM tblCUSTOMER"
set rstemp = connTemp.execute(sql)
theCount2 = rstemp("num2")
rstemp.close
set rstemp = nothing

或者我应该在每次连接后关闭连接，如下所示：

sql = "SELECT COUNT(*) AS num FROM tblUSER"
set rstemp = connTemp.execute(sql)
theCount = rstemp("num")
rstemp.close
set rstemp = nothing
sql = "SELECT COUNT(*) AS num2 FROM tblCUSTOMER"
set rstemp = connTemp.execute(sql)
theCount2 = rstemp("num2")
rstemp.close
set rstemp = nothing

（如果我们在每次查询后关闭连接，它会使用更多或更少的资源吗，它会增加或减少锁吗，等等）

Answer 1

经验法则是尽可能多地重复使用。关闭和重新打开每个查询的连接将不必要地增加您的开销，还可能导致连接池出现问题（如果您在短时间内运行了大量查询）

希望这能有所帮助。Dave

Answer 2

对于我编写的每一页，我都会打开一个连接对象，然后将该连接对象作为记录集的"ActiveConnection"属性用于所有记录集，然后在完成工作后关闭该连接对象。

示例：

Dim cn
Set cn = CreateObject("Adodb.Connection")
cn.Open MyDsnString
Dim rs1
Set rs1 = CreateObject("Adodb.Recordset")
rs1.ActiveConnection = cn
rs1.source = "some query"
rs1.Open
'... (stuff happens here) '
rs1.Close
Set rs1 = Nothing
'... (stuff happens here) '
Dim rs2
Set rs2 = CreateObject("Adodb.Recordset")
rs2.ActiveConnection = cn
rs2.source = "select something from sometable"
rs2.Open
'... (stuff happens here) '
rs2.Close
Set rs2 = Nothing
'... (stuff happens here) '
cn.Close
Set cn = Nothing

这样，整个页面只打开一个连接，而且非常高效。