Closing SQL connections in regular ASP

Anyone know what the best practice is for closing connections in Regular ASP, should I do it immediately after each sql query or just at the bottom of the page?

For instance, is this OK:

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 = n开发者_JS百科othing

or should I close the connection after each connection like this:

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

(If we close the connection after each query, will it use more or less resources, will it increase or decrease locks, etc)

---

The general rule of thumb is to re-use as much as possible. Closing and reopening the connection for each query will increase your overhead unnecessarily and also possibly create issues with the connection pooling (if your are running lots and lots of queries in a short space of time.)

Hope this helps. Dave

---

For every page I write, I open a single connection object, then use that same connection object for all my recordsets as the "ActiveConnection" property of the recordset, then close the connection object when I'm done working.

Example:

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

That way there's only one connection open for the entire page, and it's very efficient.