Closing SQL connections in regular ASP

Question

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 = nothing

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)

Answer 1

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