Closing SQL Connections in Regular ASP

Question

Does anyone know what is best suited for closing connections in Regular ASP, does it need to be done right after every sql request or only at the bottom of the page?

For example, this is normal:

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 as follows:

 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 request, whether it will use more or less resources, increase or decrease locks, etc.)

Answer 1

The general rule is to reuse as much as possible. Closing and reopening the connection for each request will lead to unnecessary unnecessary loads and, possibly, lead to problems with the connection pool (if you execute many and many requests in a short period of time.)

Hope this helps. Dave

Answer 2

For each page that I write, I open one connection object and then use the same connection object for all my recordsets as the "ActiveConnection" property of the recordset, and then close the connection object when I am done.

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 

Thus, only one connection is open to the entire page, and it is very effective.